VACUUM을 돌렸는데 age(datfrozenxid)가 안 줄어든다. 왜인가?

VACUUM은 실행 중인 가장 오래된 트랜잭션의 xmin보다 새로운 행만 얼릴 수 있다. 오래 열린 트랜잭션, 방치된 prepared transaction, 밀린 복제 슬롯이 옛 XID를 붙잡고 있으면 VACUUM을 돌려도 나이가 전진하지 않는다. pg_stat_activity의 backend_xmin, pg_prepared_xacts, pg_replication_slots를 먼저 정리하고 다시 VACUUM한다.

복구할 때 VACUUM FREEZE와 그냥 VACUUM 중 무엇을 써야 하나?

서비스를 빨리 되살리는 것이 목적이면 FREEZE 없는 평범한 VACUUM이 낫다. 공식 문서도 복구 시 일반 VACUUM을 권한다. FREEZE는 얼릴 수 있는 XID를 최대한 얼려 더 오래 걸리므로, 나이를 바닥까지 리셋하고 싶은 유지보수 작업에 맞는다. VACUUM FULL은 새 XID를 요구해 차단 상태에서 실패할 수 있으니 이 상황에서는 쓰지 않는다.

차단 에러가 떠도 SELECT는 되는 이유는?

PostgreSQL은 신규 XID 발급을 막는 것이지 읽기를 막는 것이 아니다. 새 XID를 요구하는 INSERT, UPDATE, DELETE만 실패하고, XID를 새로 소비하지 않는 읽기 조회는 정상 동작한다. 그래서 진단 쿼리는 차단 상태에서도 그대로 돌릴 수 있다.

RDS PostgreSQL에서는 단일 사용자 모드를 쓸 수 있나?

쓸 수 없다. RDS와 Aurora는 OS 접근이 없어 postgres --single 같은 단일 사용자 모드를 실행할 방법이 없다. 대신 마스터 사용자로 접속해 방해 트랜잭션과 복제 슬롯을 정리한 뒤 데이터베이스 전체 VACUUM을 돌리면 된다. 차단 상태에서도 접속과 읽기, VACUUM은 가능하다.

wraparound를 미리 알아채려면 무엇을 봐야 하나?

온프레미스는 SELECT datname, age(datfrozenxid) FROM pg_database 값을 주기적으로 보면 된다. 기본 차단선은 age 기준 약 21억이므로 그 훨씬 전에 알림을 건다. RDS와 Aurora는 CloudWatch의 MaximumUsedTransactionIDs 지표에 예를 들어 10억 이상 경보를 걸어두면 차단 사고 전에 대응할 수 있다.

PostgreSQL XID wraparound로 DB가 멈췄을 때: 'database is not accepting commands' 진단과 복구

새벽에 애플리케이션이 통째로 쓰기를 못 하고 로그에 database is not accepting commands가 찍히면 대부분 여기서 처음 XID wraparound라는 단어를 검색하게 된다. RDS PostgreSQL을 몇 년째 재기동 없이 잘 돌리던 인스턴스일수록 이 상황을 늦게 만난다. 오래 안정적으로 돌던 DB가 어느 날 갑자기 읽기만 되고 쓰기가 전부 막히는 형태라, 커넥션 풀부터 의심하다 시간을 버리기 쉽다.

결론부터 말하면, 이 에러는 트랜잭션 ID(XID)가 소진 직전이라 PostgreSQL이 데이터 손상을 막으려고 스스로 신규 XID 발급을 거부한 상태다. 복구는 정해져 있다. XID를 붙잡고 있는 방해 요소(오래된 트랜잭션, prepared transaction, 복제 슬롯)를 먼저 걷어낸 다음 문제 데이터베이스에 VACUUM을 돌려 datfrozenxid를 앞으로 밀면 다시 쓰기가 열린다. 단일 사용자 모드는 요즘 환경에선 필요 없는 경우가 많고, RDS에서는 애초에 쓸 수도 없다.

1. XID와 wraparound가 무엇인지부터

PostgreSQL은 모든 트랜잭션에 32비트 트랜잭션 ID를 부여한다. 각 행에는 자신을 만든 트랜잭션의 XID가 xmin으로 기록되고, 어떤 행이 지금 트랜잭션에 보이는지는 이 XID 대소 비교로 판단한다. 그런데 32비트는 약 42억 개뿐이라 끝까지 쓰면 0으로 되돌아온다. 이걸 wraparound라고 부른다.

되돌아오면 과거에 만들어진 행의 xmin이 갑자기 '미래'로 보여 데이터가 통째로 사라진 것처럼 되는 사고가 난다. 이를 막는 장치가 freezing이다. 충분히 오래된 행의 XID를 '항상 과거'라는 특수 표시로 얼려서(freeze) 비교 대상에서 빼는 작업이고, 이 일을 하는 것이 VACUUM이다.

relfrozenxid는 테이블에서 아직 얼리지 않은 가장 오래된 XID를 가리키는 값이고, datfrozenxid는 데이터베이스 안 모든 테이블의 relfrozenxid 중 최솟값이다. VACUUM이 행을 얼릴수록 이 값이 현재 XID 쪽으로 전진하면서 wraparound까지 남은 여유가 늘어난다. 반대로 VACUUM이 제때 안 돌면 이 값이 뒤처지고, 여유가 바닥나면 PostgreSQL이 쓰기를 막는다.

2. 에러가 뜨기 전 경고와 실제 차단 메시지

PostgreSQL은 갑자기 멈추지 않는다. wraparound까지 4천만 트랜잭션쯤 남으면 매 트랜잭션마다 경고를 찍는다.

WARNING:  database "mydb" must be vacuumed within 39985967 transactions
HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.

이 경고를 무시하고 계속 트랜잭션을 소비해 남은 여유가 약 3백만 트랜잭션 밑으로 떨어지면, 신규 XID 발급을 아예 거부한다. 이때 나오는 것이 그 유명한 차단 메시지다.

ERROR:  database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "mydb"
HINT:  Execute a database-wide VACUUM in that database.

이 상태에서 SELECT 같은 읽기는 되지만 INSERT/UPDATE/DELETE는 전부 이 에러로 실패한다. 그래서 겉으로는 애플리케이션이 반쯤 살아 있는 것처럼 보인다.

3. age()로 얼마나 급한지 진단

age()는 인자로 준 XID에서 현재 XID까지 몇 트랜잭션 떨어졌는지 돌려준다. 데이터베이스 단위로 가장 늙은 값부터 본다.

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

   datname  | xid_age
------------+-----------
 mydb       | 2145869213
 template0  |  205831922
 postgres   |  198500431

기본 임계값이 몇 개 있다. 신규 XID 차단은 wraparound 약 3백만 전, 즉 age 기준 대략 21억(2^31 부근)에서 걸린다. 위 예시의 mydb는 이미 그 근처라 차단 상태로 봐야 한다.

어느 테이블이 발목을 잡는지는 테이블별로 나이를 뽑아 확인한다. TOAST 테이블까지 함께 봐야 정확하다.

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY age DESC
LIMIT 20;

가장 위에 뜨는 테이블 몇 개가 datfrozenxid를 끌어내리는 주범이다. 급할 때는 전체 대신 이 테이블부터 VACUUM해도 datfrozenxid가 전진한다.

4. VACUUM 전에 XID를 붙잡은 것부터 풀어야 한다

여기서 처음 걸리는 함정이 있다. 차단 에러를 보고 바로 VACUUM을 돌렸는데 age가 꿈쩍도 안 하는 경우다. VACUUM은 아직 실행 중인 가장 오래된 트랜잭션의 xmin보다 새로운 행은 얼리지 못한다. 오래된 트랜잭션이나 복제 슬롯이 옛 XID를 붙잡고 있으면 아무리 VACUUM을 돌려도 datfrozenxid가 전진하지 않는다. 그래서 순서가 중요하다. 방해 요소를 먼저 제거하고 VACUUM해야 한다.

세 군데를 점검한다. 먼저 오래 열려 있는 트랜잭션이다.

SELECT pid, state, age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age, query
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY greatest(age(backend_xid), age(backend_xmin)) DESC;

xmin_age가 유난히 큰 세션이 있으면 그 세션이 원인이다. 애플리케이션의 트랜잭션 미종료(커밋/롤백 누락)나 방치된 psql이 흔하다. 확인 후 SELECT pg_terminate_backend(<pid>);로 정리한다.

다음은 prepared transaction(2단계 커밋을 걸어두고 방치된 것)이다.

SELECT gid, database, prepared, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY xid_age DESC;

결과가 있으면 ROLLBACK PREPARED '<gid>';로 정리한다. 마지막은 복제 슬롯이다. 복제 슬롯은 스탠바이가 아직 안 받아간 WAL을 지키려고 XID도 함께 붙잡는데, 죽은 스탠바이나 폐기한 논리 복제가 슬롯만 남겨두면 이게 wraparound의 조용한 원인이 된다.

SELECT slot_name, slot_type, active,
       age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC;

안 쓰는 슬롯이면 SELECT pg_drop_replication_slot('<slot_name>');로 지운다. 쓰는 슬롯이라면 스탠바이를 살려 밀린 WAL을 소비시켜야 한다.

5. VACUUM으로 복구: 평범한 VACUUM이 먼저다

방해 요소를 걷어냈으면 문제 데이터베이스에 VACUUM을 돌린다. 서비스를 빨리 되살리는 것이 목적이라면 FREEZE 없는 평범한 데이터베이스 전체 VACUUM이 정답이다. 공식 문서도 복구 상황에서는 VACUUM FREEZE나 VACUUM FULL보다 일반 VACUUM을 권한다. FREEZE는 필요 이상으로 많이 얼려 더 오래 걸리고, FULL은 새 XID를 요구해 차단 상태에서 오히려 실패할 수 있기 때문이다.

psql -h mydb.xxxx.ap-northeast-2.rds.amazonaws.com -U postgres -d mydb

mydb=> VACUUM (VERBOSE);

급하면 전체 대신 3번에서 찾은 가장 늙은 테이블만 먼저 처리해도 datfrozenxid가 전진해 차단이 풀린다.

mydb=> VACUUM (VERBOSE) big_log_table;
INFO:  aggressively vacuuming "public.big_log_table"
...
INFO:  table "big_log_table": found 0 removable, 128374 nonremovable row versions
new relfrozenxid: 2412008551, which is 2145869213 older than before

new relfrozenxid 줄이 나오면 그 테이블의 나이가 리셋된 것이다. VACUUM이 끝나면 3번의 age(datfrozenxid) 쿼리를 다시 돌려 값이 크게 떨어졌는지 확인한다. 임계값 아래로 내려가면 쓰기가 다시 열린다.

FREEZE는 언제 쓰나. VACUUM (FREEZE)는 vacuum_freeze_min_age=0, vacuum_freeze_table_age=0과 같아 얼릴 수 있는 XID를 최대한 얼린다. 당장의 복구보다는, 한 번 크게 데인 뒤 나이를 바닥까지 리셋해 다음 anti-wraparound autovacuum이 한동안 안 뜨게 만들고 싶을 때 유지보수 창에서 돌리는 용도다. 지금 차단이 걸린 상황에서 첫 수는 평범한 VACUUM이다.

6. RDS와 Aurora에서 달라지는 점

온프레미스 문서를 보면 최후의 수단으로 단일 사용자 모드(postgres --single)가 나오는데, RDS와 Aurora PostgreSQL에서는 OS 접근이 없어 이 방법을 못 쓴다. 대신 마스터 사용자로 붙어 위의 트랜잭션 정리와 VACUUM을 그대로 하면 된다. RDS도 차단 상태에서 읽기는 되므로 진단 쿼리는 문제없이 돈다.

RDS는 wraparound를 미리 감지하라고 CloudWatch에 MaximumUsedTransactionIDs 지표를 준다. 이 값이 곧 데이터베이스의 최대 XID 나이다. 기본 차단선인 약 21억에 닿기 한참 전, 대략 10억 근처에서 경보를 걸어두면 차단 사고 전에 손을 쓸 수 있다.

# CloudWatch 지표 이름
AWS/RDS  MaximumUsedTransactionIDs  (DBInstanceIdentifier 차원)
# 경보 임계값 예: 1,000,000,000 이상 지속 시 알림

Aurora PostgreSQL은 스토리지 구조가 달라도 XID는 동일한 32비트라 wraparound 개념과 대응이 같다. 지표와 VACUUM 절차를 그대로 적용한다.

7. 재발 방지: autovacuum을 이해하고 놔두기

wraparound는 대부분 autovacuum이 제 일을 못 한 결과다. 관련 기본값을 알면 왜 막혔는지 보인다. autovacuum_freeze_max_age는 기본 2억이고, 테이블 나이가 이 값을 넘으면 autovacuum을 꺼놨더라도 PostgreSQL이 강제로 anti-wraparound autovacuum을 돌린다. vacuum_freeze_min_age는 기본 5천만으로 이보다 오래된 XID를 얼림 대상으로 삼고, vacuum_freeze_table_age는 기본 1억5천만으로 이 나이를 넘으면 전체 페이지를 훑는 aggressive vacuum으로 전환한다.

여기서 강제 anti-wraparound autovacuum이 도는데도 나이가 안 줄면, 원인은 십중팔구 4번의 방해 요소다. 오래된 트랜잭션이나 복제 슬롯이 남아 있으면 autovacuum도 얼리지 못한다. 그래서 재발 방지의 절반은 방치된 슬롯과 미종료 트랜잭션을 안 만드는 것이다.

나머지 절반은 autovacuum을 끄거나 너무 느리게 조여놓지 않는 것이다. 대량 쓰기가 있는 인스턴스라면 autovacuum이 부하로 뒤처지지 않게 autovacuum_vacuum_cost_limit을 올려 처리 속도를 높이는 편이 현실적이다. autovacuum 자체를 끄는 설정은 피한다. 끈다고 anti-wraparound autovacuum까지 사라지는 것은 아니지만, 평상시 정리가 안 돼 결국 강제 vacuum이 몰아서 도는 상황을 만든다.

마지막으로 vacuum_failsafe_age(기본 16억)라는 안전장치가 있다. 나이가 이 선을 넘으면 VACUUM이 인덱스 정리 같은 부수 작업을 건너뛰고 오직 wraparound 회피에만 집중한다. 이 장치가 발동했다는 건 이미 위험선 깊숙이 들어왔다는 신호이므로, 로그에서 failsafe 문구를 보면 그 데이터베이스는 즉시 점검 대상이다.

8. 정리

차단 에러를 만나면 순서는 하나다. age(datfrozenxid)로 급한 정도를 보고, pg_stat_activity와 pg_prepared_xacts, pg_replication_slots에서 XID를 붙잡은 것을 먼저 풀고, 그다음 평범한 데이터베이스 전체 VACUUM으로 datfrozenxid를 전진시켜 쓰기를 되살린다. RDS라면 MaximumUsedTransactionIDs 경보를 걸어 애초에 이 에러를 안 보는 것이 가장 싸게 먹힌다.