Sev1: PostgreSQL refuses writes to avoid transaction ID wraparound

#Symptoms

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

and finally:

ERROR:  database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "appdb"

(Exact text differs by version.) Reads still work; anything that writes fails.

#Why it happens

Transaction IDs are 32-bit. Old rows must be frozen by vacuum before the counter wraps. Freezing is blocked or too slow when:

• a long-running or idle-in-transaction session holds back the oldest visible XID,
• a replication slot (logical or physical) is abandoned,
• an orphaned prepared transaction exists,
• autovacuum is too slow, disabled or keeps getting cancelled on huge tables.

#Step 1: how close are we?

SELECT datname, age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 1) AS pct_to_wraparound
FROM pg_database ORDER BY xid_age DESC;

Oldest tables in the affected database:

SELECT c.oid::regclass AS table, age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
WHERE c.relkind IN ('r','m','t')
ORDER BY age(c.relfrozenxid) DESC LIMIT 20;

#Step 2: remove what's holding the horizon back

-- Long transactions
SELECT pid, usename, state, xact_start, now() - xact_start AS age, left(query, 80)
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL OR backend_xid IS NOT NULL
ORDER BY xact_start NULLS LAST LIMIT 20;

-- Replication slots
SELECT slot_name, slot_type, active, xmin, catalog_xmin, age(xmin) AS xmin_age, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots;

-- Prepared transactions
SELECT gid, prepared, owner, database, age(transaction) AS age FROM pg_prepared_xacts;

Fix:

SELECT pg_terminate_backend(<pid>);                 -- old sessions
SELECT pg_drop_replication_slot('old_cdc_slot');    -- abandoned slots (confirm with the CDC owner)
ROLLBACK PREPARED '<gid>';                          -- orphaned prepared transactions

On a standby with hot_standby_feedback = on, long queries on the standby also hold back the primary. Check pg_stat_activity there too.

Warning

Dropping a logical replication slot breaks the CDC pipeline using it (Debezium, DMS, logical subscribers). They'll need a re-snapshot. In this situation that is usually the right trade.

#Step 3: vacuum the oldest tables first

Connect to the affected database as a superuser (or rds_superuser / azure_pg_admin):

SET vacuum_cost_delay = 0;            -- run at full speed
VACUUM (FREEZE, VERBOSE) public.big_old_table;

Work down the list from Step 1, oldest first. A database-wide VACUUM also works but takes longer before you get the first relief. On PostgreSQL 14+, the failsafe also kicks in automatically at high XID age, skipping index cleanup to finish faster.

Modern PostgreSQL versions don't require single-user mode for this; a normal VACUUM by a superuser is enough. Don't use VACUUM FULL: it is slower and needs more space.

Check progress:

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total FROM pg_stat_progress_vacuum;

Once age(datfrozenxid) drops below the stop limit, writes are accepted again.

#Step 4: after recovery

• Re-run the age query; continue vacuuming until all databases are well below autovacuum_freeze_max_age.
• Check whether autovacuum is enabled and not overridden per table (reloptions).

#Prevent it next time