Postgres の XID 周回 / autovacuum の危機

症状

'database is not accepting commands to avoid wraparound data loss'。autovacuum が追いつかず、datfrozenxid が古くなっている。

1. 緊急度を把握する

PostgreSQL のトランザクション ID は 32 ビットである。枯渇が近づくと、サーバーはデータ損失を防ぐため書き込みを停止する。これは重大で時間的制約のある状況である。良い知らせ: 解決可能である。必要なのはパニックではなく規律である。

2. どれだけ近づいているかを測定する

各データベースの最も古い未凍結 XID の経過年齢を抽出する。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 をブロックしているものを取り除く

長時間開いたトランザクション、放棄されたレプリケーションスロット、準備済みトランザクションはクリーンアップ (凍結) をブロックする。それらを見つけて解消する — さもなければ 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 を実行する

ブロッカーがなくなったら、最も古いテーブルから凍結を強制する。サーバーが新しいトランザクション ID の割り当てを停止していても、シングルユーザーモード (postgres --single) には切り替えないこと: 現在の PostgreSQL ドキュメントでは、もはや不要であり避けるべきとされている。通常モードでスーパーユーザーとして接続し VACUUM を実行すれば十分である。PG14+ では周回が近づくと VACUUM の failsafe 機構が作動する。

PostgreSQL

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

6. 恒久対策: autovacuum を強化して監視する

autovacuum_vacuum_cost_limit を上げ、autovacuum_naptime を下げ、書き込みの多いテーブルにはテーブル固有のストレージパラメータを設定する。age(datfrozenxid) にアラートを設定し、この危機を二度と経験しないようにする。