Postgres XID wraparound / autovacuum crisis

Symptom

'database is not accepting commands to avoid wraparound data loss'; autovacuum cannot keep up, datfrozenxid is aging.

1. Understand the urgency

PostgreSQL transaction IDs are 32-bit; as they approach exhaustion, the server STOPS WRITES to prevent data loss. This is a critical, time-sensitive situation. The good news: it is solvable; it takes discipline, not panic.

2. Measure how close you are

Extract the age of the oldest unfrozen XID for each database; proximity to autovacuum_freeze_max_age (default 200M) shows the danger.

PostgreSQL

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

3. Find the oldest tables

The problem usually comes from a few large/frequently written tables; focus on the oldest relfrozenxid values.

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. Remove what is blocking VACUUM

Long open transactions, abandoned replication slots and prepared transactions block cleanup (freeze). Find and resolve them — otherwise VACUUM makes no progress no matter how long it runs.

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. Run an aggressive FREEZE

Once the blockers are gone, force freezing starting with the oldest tables. Even if the server has stopped assigning new transaction IDs, do not switch to single-user mode (postgres --single): current PostgreSQL documentation states it is no longer needed and should be avoided. Connecting as a superuser in normal mode and running VACUUM is sufficient; in PG14+, VACUUM's failsafe mechanism kicks in as wraparound approaches.

PostgreSQL

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

6. Permanent: strengthen and monitor autovacuum

Increase autovacuum_vacuum_cost_limit, lower autovacuum_naptime, and set table-specific storage parameters on heavily written tables. Set up an alert on age(datfrozenxid); never go through this crisis again.