PostgreSQL의 “cannot execute CREATE TABLE in a read-only transaction” 오류로 인해 많은 팀의 마이그레이션이 중단되고 있으며, 이로 인해 스키마가 절반만 적용되거나 마이그레이션 이력 테이블이 잠기는 문제가 발생하고 있습니다. 이 오류는 보통 쓰기 작업이 프라이머리(primary)가 아닌 레플리카(replica)에 전달될 때 발생하며, Flyway, Liquibase, Django ORM 등 스키마 변경에 의존하는 모든 도구의 작동을 멈추게 합니다.
오류가 발생하는 이유
트랜잭션이 읽기 전용(read-only)으로 표시되면 PostgreSQL은 모든 쓰기, 스키마 변경 및 시퀀스 업데이트를 비활성화합니다. 마이그레이션이 이러한 상태에 빠지는 가장 흔한 원인은 다음과 같습니다:
- 커넥션 풀러(예: PgBouncer)가 실수로 마이그레이션 연결을 읽기 전용 레플리카로 보내는 경우.
default_transaction_read_only파라미터가 기본적으로 on으로 설정된 역할(Role).- 읽기 전용과 쓰기 전용 URL을 별도로 제공하는 클라우드 엔드포인트. (AWS RDS나 Aurora에서 흔히 그렇듯) 읽기 전용 URL을 사용하면 쓰기 작업이 레플리카로 라우팅됩니다.
이러한 조건 중 하나라도 해당되면, 마이그레이션 프로세스가 테이블을 생성하거나 컬럼을 추가하고 시퀀스를 업데이트하다가 갑자기 막히게 되어, 데이터베이스가 마이그레이션이 부분적으로만 완료된 상태로 남게 됩니다.
트랜잭션 내 즉각적인 해결 방법
이미 오류가 발생했다면, 세션의 나머지 부분에 영향을 주지 않고 현재 트랜잭션에 대해서만 읽기 전용 플래그를 무시하도록 설정할 수 있습니다. 이는 커넥션 풀이 동일한 세션을 다른 작업에 재사용할 때 매우 중요합니다.
BEGIN;
SET LOCAL default_transaction_read_only = off;
SET TRANSACTION READ WRITE;
CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
COMMIT;
SET LOCAL은 트랜잭션이 지속되는 동안에만 설정을 변경합니다. 일반 SET을 사용하면 세션 전체에 변경 사항이 유지되어, 읽기 전용 기본값이 반드시 필요한 다른 작업들을 망가뜨릴 수 있습니다.
예방 조치
1. 프라이머리 노드인지 확인하기
마이그레이션 실행 전 다음과 같이 간단한 확인 절차를 추가하세요:
SELECT CASE WHEN pg_is_in_recovery() THEN 'REPLICA' ELSE 'PRIMARY' END;
결과가 REPLICA라면 마이그레이션을 중단하십시오. pg_is_in_recovery() 함수는 스탠바이(standby) 서버에서 true를 반환하므로, 읽기 전용 복제본에 쓰기를 시도하지 않도록 보장합니다.
2. 전용 마이그레이션 역할(Role) 사용하기
기본 트랜잭션 모드가 쓰기 가능으로 설정된 역할을 생성하고, 필요한 권한만 부여하십시오:
- 대상 데이터베이스에 대한
CREATE권한. - 마이그레이션 도구가 세션을 열 수 있도록 하는
CONNECT권한.
일반 사용자용으로 가끔 설정되는 default_transaction_read_only = on 속성을 이 역할에 부여하지 않도록 주의하십시오.
3. 인프라를 라이터(Writer) 엔드포인트로 지정하기
IaC 스크립트(Terraform, CloudFormation 등)에서 마이그레이션 러너가 리더(reader) 엔드포인트가 아닌 클러스터의 라이터(writer) 엔드포인트를 사용하도록 구성하십시오. 라이터 엔드포인트는 프라이머리 노드로 연결되지만, 리더 엔드포인트는 쓰기를 거부하는 레플리카로 연결됩니다.
4. CI/CD 게이트 추가하기
파이프라인(GitHub Actions, GitLab CI 등)에 pg_is_in_recovery() 쿼리를 실행하는 쉘(shell) 단계를 삽입하십시오. 결과가 true를 반환하면, 배포를 조기에 중단하기 위해 0이 아닌 상태 코드로 작업을 종료하십시오.
if psql $DATABASE_URL -c "SELECT pg_is_in_recovery()" | grep -q t; then
echo "Connected to replica – aborting migration"
exit 1
fi
5. 마이그레이션 도구의 재시도 설정 조정하기
Flyway와 같은 도구는 종종 자동으로 연결을 재시도합니다. Flyway 9 이상 버전의 경우 flyway.connectRetries=0으로 설정하십시오. 이렇게 하면 도구가 레플리카에 반복적으로 접속하는 것을 방지할 수 있으며, 이는 복제 지연(replication lag)을 증가시키고 리소스를 낭비하는 것을 막아줍니다.
향후 주의 깊게 살펴볼 사항
- 복제 지연(Replication lag) 지표: 지연 시간이 증가한다면 마이그레이션이 실수로 레플리카를 대상으로 하여 쓰기 시도가 대기열에 쌓이고 있음을 나타낼 수 있습니다.
- 커넥션 풀러 라우팅 규칙: 풀러 설정이 마이그레이션 트래픽을 프라이머리 호스트로 명시적으로 라우팅하는지 확인하십시오.
- 업그레이드 후 역할 기본값: 데이터베이스 업그레이드 시 역할 파라미터가 초기화될 수 있습니다. 메이저 버전 변경 후에는
default_transaction_read_only설정을 다시 점검하십시오.
결론적으로, 읽기 전용 트랜잭션 오류는 PostgreSQL의 버그인 경우가 거의 없습니다. 이는 트래픽이 잘못된 노드로 전송되거나 역할이 잘못 구성되었을 때 나타나는 증상입니다. 노드 역할을 확인하고, 전용 마이그레이션 계정을 사용하며, CI/CD 파이프라인을 강화함으로써 마이그레이션을 원활하게 유지하고 후속 개발을 방해하는 스키마 부분 적용 문제를 방지할 수 있습니다.