Postgres XID wraparound / autovacuum 위기

증상

'database is not accepting commands to avoid wraparound data loss'. autovacuum이 따라가지 못하고 datfrozenxid가 노화되고 있다.

1. 긴급성을 이해한다

PostgreSQL 트랜잭션 ID는 32비트이며, 소진에 가까워지면 서버는 데이터 손실을 막기 위해 쓰기를 중지한다. 이는 시간에 민감한 심각한 상황이다. 좋은 소식: 해결 가능하며, 필요한 것은 당황이 아니라 규율이다.

2. 얼마나 근접했는지 측정한다

각 데이터베이스의 가장 오래된 미동결 XID의 age를 추출한다. autovacuum_freeze_max_age(기본값 200M)에 대한 근접도가 위험도를 보여 준다.

PostgreSQL

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

3. 가장 오래된 테이블을 찾는다

문제는 대개 몇몇 크거나 쓰기가 잦은 테이블에서 비롯된다. 가장 오래된 relfrozenxid 값에 집중한다.

PostgreSQL

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind IN ('r','m','t')
ORDER BY xid_age DESC LIMIT 20;

4. VACUUM을 막는 요소를 제거한다

장시간 열린 트랜잭션, 방치된 replication slot, prepared transaction이 정리(freeze)를 막는다. 이를 찾아 해결한다 — 그렇지 않으면 VACUUM을 아무리 오래 실행해도 진행되지 않는다.

PostgreSQL

SELECT pid, now()-xact_start AS age, state FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY age DESC;
SELECT slot_name, active FROM pg_replication_slots WHERE NOT active;
SELECT * FROM pg_prepared_xacts;

5. 공격적인 FREEZE를 실행한다

차단 요소가 사라지면 가장 오래된 테이블부터 freeze를 강제한다. 서버가 새 트랜잭션 ID 할당을 중지했더라도 single-user 모드(postgres --single)로 전환하지 않는다: 현재 PostgreSQL 문서는 이것이 더 이상 필요 없으며 피해야 한다고 명시한다. 일반 모드에서 superuser로 연결해 VACUUM을 실행하면 충분하다. PG14+에서는 wraparound가 가까워지면 VACUUM의 failsafe 메커니즘이 작동한다.

PostgreSQL

VACUUM (FREEZE, VERBOSE) oldest_table;
-- Entire database: vacuumdb --all --freeze --jobs=4

6. 영구 조치: autovacuum을 강화하고 모니터링한다

autovacuum_vacuum_cost_limit을 높이고 autovacuum_naptime을 낮추며, 쓰기가 많은 테이블에 테이블별 storage parameter를 설정한다. age(datfrozenxid)에 알림을 설정하여 이 위기를 다시 겪지 않도록 한다.