Postgres Won't Accept Writes: The Transaction ID Wraparound Runbook

The page comes in at 01:40. Every write is failing, every read is fine. You SSH into the database box expecting a disk full or an OOM kill and find neither: plenty of space, idle CPU, Postgres up and answering SELECT 1 instantly. Then you try an INSERT by hand and Postgres tells you, politely, that it has stopped.

The database did not crash. It shut the door on itself, on purpose, to avoid destroying your data. Transaction ID wraparound is the one Postgres failure mode where the server is working exactly as designed and you still have a total outage on your hands.

Almost everyone who hits this reaches straight for single-user mode, because a decade of blog posts and the error's own HINT told them to. On Postgres 14 and newer that is usually the wrong first move, and the official documentation now says so explicitly. Here is the runbook I actually use.

The exact error: database is not accepting commands to avoid wraparound data loss

Depending on your major version you will see one of these two, and they mean the same thing:

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

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.

And in the log for days or weeks beforehand, the warning nobody actioned:

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

The thresholds and the wording changed across versions, which is exactly why searching for the message gives you contradictory advice:

Version	Warning starts at	Writes refused at	Official advice
13 and earlier	~11 million XIDs remaining	fewer than 1 million remaining	Stop the postmaster, vacuum in single-user mode
14 to 16	~40 million XIDs remaining	fewer than 3 million remaining	Docs describe the online procedure; the HINT still says single-user mode
17 and newer	~40 million XIDs remaining	fewer than 3 million remaining	Execute a database-wide VACUUM; single-user mode explicitly discouraged

Note the important detail hiding in that table: on 14 and later you have roughly 3 million transactions of headroom in the "stopped" state, and reads and VACUUM still work. You are not locked out. You have a narrow but real window to fix this online.

Why Postgres stops: what a transaction ID actually is

Every row version in a Postgres heap carries the transaction ID that created it and the one that deleted it. Those IDs are 32-bit and they wrap. Postgres compares them modulo 2^32, which means that from any given transaction's point of view, roughly two billion IDs are "in the past" and two billion are "in the future". If an XID ever ages past that two billion boundary while still stamped on a live row, that row would suddenly appear to be from the future, and it would vanish. Not error out. Vanish.

The defence is freezing. VACUUM marks sufficiently old row versions as frozen, meaning "visible to everyone, forever, ignore my XID". Each table records the oldest unfrozen XID it still contains in pg_class.relfrozenxid, and each database records the minimum across all its relations in pg_database.datfrozenxid. The age of that value is your countdown.

Postgres tries hard to keep you away from the edge. Once a table's relfrozenxid reaches autovacuum_freeze_max_age (default 200 million), an anti-wraparound autovacuum is forced on that table even if you have autovacuum switched off entirely. At vacuum_failsafe_age (default 1.6 billion) vacuum drops its cost delays and skips index cleanup so it can sprint. That is a lot of guard rail to blow through.

The part everyone misses: autovacuum was probably running the whole time

This is the piece the content farms skip, and it is the entire root cause in most incidents I have seen.

VACUUM can only freeze row versions older than the oldest snapshot anything in the cluster might still need. If one session opened a transaction on Tuesday and went to lunch, the freeze horizon is stuck on Tuesday. Autovacuum will keep waking up, keep scanning the table, keep writing to the log that it vacuumed successfully, and relfrozenxid will not move a single tick. Your monitoring shows autovacuum healthy. Your dead tuple counts look fine. And the countdown keeps running.

Three things pin that horizon, and you must check all three:

The fix, step by step

Step 1: Measure how much runway you have

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

Expect a row per database. Anything above 200,000,000 means anti-wraparound autovacuum should already be working on that database. Above 1,500,000,000 you are in incident territory. At roughly 2,144,000,000 you are where you are now.

Then find the actual culprit relations. It is almost never the whole database, it is one or two tables (and on a partitioned table, often a single neglected partition, because every partition is its own relation with its own relfrozenxid and the database takes the minimum):

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

Step 2: Find what is pinning the horizon

Run all three. Do not stop at the first one that looks innocent.

-- Open transactions holding an old snapshot
SELECT pid, datname, usename, state,
       age(backend_xid)  AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration,
       left(query, 80) AS 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;

-- Prepared transactions left hanging by a two-phase commit
SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

-- Replication slots, including ones for replicas that no longer exist
SELECT slot_name, slot_type, active,
       age(xmin) AS xmin_age,
       age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST;

A logical replication slot for a subscriber that has been down for a month is the classic silent killer here, because it holds catalog_xmin indefinitely and nothing in your app-level monitoring will ever mention it. An idle in transaction session from a connection pool that forgot to roll back is the second most common.

Step 3: Release the holder

-- An idle-in-transaction session (get the pid from step 2)
SELECT pg_terminate_backend(12345);

-- An orphaned prepared transaction
ROLLBACK PREPARED 'the_gid_from_pg_prepared_xacts';

-- A slot for a replica that is never coming back
SELECT pg_drop_replication_slot('stale_subscriber_slot');

Dropping a slot for a replica that is still alive means that replica has to be rebuilt, so confirm before you drop. If a standby is doing this to you and you want to keep it, the setting to look at is hot_standby_feedback on the standby.

Step 4: Vacuum the right things, the right way

Once the horizon is free, vacuum. vacuumdb can target only the old relations and run several in parallel, which matters when the alternative is a single-threaded scan of a terabyte:

vacuumdb --dbname=mydb --jobs=4 --min-xid-age=500000000 --echo

Each job is a connection, so keep --jobs below your spare connection and I/O budget. --echo prints each VACUUM statement as it runs so you can see progress in a scrollback.

Two commands you must not reach for:

• VACUUM FULL rewrites the table and needs an XID of its own. In this state it will fail, and in single-user mode it will succeed at consuming one more XID you cannot spare.
• VACUUM FREEZE does far more work than restoring service requires. Plain VACUUM already advances relfrozenxid.

Step 5: Single-user mode, only if you truly cannot connect

If the cluster will not let you in at all, then and only then:

sudo systemctl stop postgresql@16-main
sudo -u postgres /usr/lib/postgresql/16/bin/postgres --single -D /var/lib/postgresql/16/main mydb

At the backend> prompt type VACUUM; and press Enter, then exit with Ctrl+D when it returns. Adjust the version numbers and data directory to your install. Do not interrupt it: on a large table this can run for hours, and killing it halfway leaves you with the same outage and less time. Run it inside tmux or screen, especially over a flaky connection, which is a lesson I learned the hard way on a Nairobi link at 3am.

Verification: proving you are actually out of it

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

The number for the affected database should have dropped by hundreds of millions, typically to somewhere near vacuum_freeze_min_age plus recent activity rather than to zero. Zero is not the target; "well below 200 million" is.

Then prove writes work, with a real write rather than a SELECT:

CREATE TEMP TABLE wraparound_check (ok boolean);
INSERT INTO wraparound_check VALUES (true);
SELECT * FROM wraparound_check;

Finally, tail the log and confirm the must be vacuumed within warnings have stopped appearing. If they have not, something is still pinning the horizon and you skipped part of step 2.

What people get wrong

"Just restart Postgres." A restart clears open transactions, which occasionally looks like it worked, and does nothing at all for prepared transactions or replication slots, which survive restarts by design. Meanwhile you have spent your calm diagnostic window on a reboot and now everything is reconnecting at once.

"Go straight to single-user mode." This is the advice in the error's own HINT on older versions, and the docs on 17 and newer now say plainly that it should be avoided where possible: it takes the whole system down, and it disables the very wraparound safeguards that are protecting your data. Use it as a fallback, not an opening move.

"Raise autovacuum_freeze_max_age so it stops warning." The threshold is not the problem. Raising it removes the alarm and moves you closer to the cliff.

"Cancel that autovacuum, it is hammering the disk." Repeatedly cancelling anti-wraparound autovacuum on a big table, or letting a nightly DDL job cancel it every night, is one of the most reliable ways to end up reading this article. Anti-wraparound autovacuum does not back off politely for a reason.

"We monitor bloat, so we would have seen it." Bloat and XID age are different numbers with different causes. A table can have almost no dead tuples and a relfrozenxid age of 1.9 billion. Alert on age(datfrozenxid) directly: warn at 500 million, page at 1 billion. That one alert is the whole prevention story, and it is the same discipline as watching database growth before it becomes an archiving emergency.

When it is still broken

1. The error mentions MultiXactIds, not XIDs. Multixacts have their own separate counter and their own wraparound. Check age(relminmxid) in pg_class and use vacuumdb --min-mxid-age. Heavy use of SELECT ... FOR SHARE and foreign keys under concurrency is what drives it.
2. The disk is full. VACUUM writes WAL and dirties pages; it cannot run on a full filesystem. Free space first, and if the space is being eaten by WAL that cannot be recycled, that is another sign of a stuck replication slot.
3. VACUUM is running but crawling. Set SET vacuum_cost_delay = 0; in your session before a manual VACUUM so cost-based throttling is out of the way, and watch progress in pg_stat_progress_vacuum.
4. It comes back a week later. Then you never found the real holder. Put a recurring check on the three queries in step 2 and on idle in transaction sessions older than a few minutes; most application frameworks will happily leak one forever.

The honest summary: this is not a rare exotic failure, it is a monitoring failure. The database warned you tens of millions of transactions in advance, in the log, in English. If you take one thing from this, make it the age(datfrozenxid) alert, and treat a slow database as a separate problem with its own diagnostics, the way I do for a slow full-table scan on InnoDB.

Reference: PostgreSQL routine vacuuming documentation and the vacuum configuration parameters.