🚨 PostgreSQL Error Guide: 'database is not accepting commands to avoid wraparound data loss'

Overview

PostgreSQL uses 32-bit transaction IDs (XIDs), which wrap around after roughly 2 billion transactions. To stop old, still-visible rows from suddenly appearing to be in the “future” (which would silently corrupt visibility), VACUUM “freezes” old rows and advances each database’s datfrozenxid. If freezing falls too far behind and a database’s oldest unfrozen XID approaches the 2-billion limit, PostgreSQL refuses all new transactions to protect your data.

You will see this when you try to run any query:

ERROR:  database is not accepting commands to avoid wraparound data loss in database "production"
HINT:  Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

Before the hard stop, you get escalating warnings in the server log as the age of the oldest XID climbs past autovacuum_freeze_max_age and approaches the safety limit:

WARNING:  database "production" must be vacuumed within 10342111 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.

It occurs when freezing cannot keep up: autovacuum is disabled or too slow, something is holding back the oldest XID (a long transaction, an abandoned prepared transaction, or a replication slot), or large tables were simply never vacuumed. The condition is database-wide, so a single neglected table can freeze the entire cluster.

Symptoms

• Every write (and eventually every read) fails with “not accepting commands to avoid wraparound data loss”.
• The server log fills with “must be vacuumed within N transactions” warnings hours or days before the shutdown.
• age(datfrozenxid) for one or more databases is close to autovacuum_freeze_max_age (default 200 million) and climbing toward 2 billion.
• Autovacuum appears to run but never finishes on a few large tables.

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

  datname  |  xid_age   | xids_remaining
-----------+------------+----------------
 production | 2138492011 |        8991637
 template1  |  103221544 |     2044262104
 postgres   |   98442190 |     2049041458
(3 rows)

SELECT pid, state, age(backend_xmin) AS xmin_age, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

  pid  |        state        | xmin_age  |              query
-------+---------------------+-----------+---------------------------------
 41122 | idle in transaction | 198223110 | BEGIN; SELECT * FROM orders ...
 41980 | active              |     12044 | SELECT count(*) FROM sessions
(2 rows)

Common Root Causes

1. Autovacuum is disabled or not freezing fast enough

If autovacuum was turned off (often “temporarily” during a migration) or throttled too hard, the anti-wraparound freeze never catches up.

SHOW autovacuum;
SELECT name, setting FROM pg_settings
WHERE name IN ('autovacuum', 'autovacuum_vacuum_cost_delay',
               'autovacuum_vacuum_cost_limit', 'autovacuum_max_workers');

 autovacuum
------------
 off

            name              | setting
------------------------------+---------
 autovacuum                   | off
 autovacuum_max_workers       | 3
 autovacuum_vacuum_cost_delay | 20
 autovacuum_vacuum_cost_limit | 200

With autovacuum = off, nothing advances datfrozenxid automatically. Even when on, a high cost_delay with a low cost_limit can make freezing too slow for a write-heavy database.

2. A long-running transaction is pinning xmin

VACUUM cannot freeze rows newer than the oldest snapshot still in use. One transaction left open for hours holds xmin back and stalls freezing cluster-wide.

SELECT pid, age(backend_xmin) AS xmin_age, state,
       now() - xact_start AS xact_runtime, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 3;

  pid  | xmin_age  |        state        | xact_runtime |             query
-------+-----------+---------------------+--------------+-------------------------------
 41122 | 198223110 | idle in transaction | 06:14:22     | BEGIN; SELECT * FROM orders...

An idle in transaction session running for over six hours is holding xmin ~198 million transactions back. Terminate it (SELECT pg_terminate_backend(41122);) and freezing can resume.

3. Abandoned prepared (two-phase) transactions

Prepared transactions from a crashed coordinator never commit or roll back, and they hold xmin indefinitely — a classic silent wraparound cause because they survive reconnects and restarts.

SELECT gid, prepared, owner, database,
       age(transaction::text::xid) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;

        gid         |           prepared            |  owner  | database  |  xid_age
--------------------+-------------------------------+---------+-----------+-----------
 txn_7f3a_orders    | 2026-06-10 02:14:55.110233+00 | appuser | production | 187442910
(1 row)

Resolve with ROLLBACK PREPARED 'txn_7f3a_orders'; (or COMMIT PREPARED if appropriate) once you confirm the originating transaction manager is gone.

4. Replication slots or stale standbys pinning xmin

A logical or physical replication slot with hot_standby_feedback reports the standby’s oldest needed XID back to the primary, which then refuses to freeze past it. A disconnected-but-not-dropped slot pins xmin forever.

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;

   slot_name    | slot_type | active | xmin_age  | catalog_xmin_age
----------------+-----------+--------+-----------+------------------
 standby_dc2    | physical  | f      | 201882334 |
 cdc_debezium   | logical   | t      |           |        14223110
(2 rows)

standby_dc2 is inactive but still pins xmin ~201 million back. If the standby is gone, SELECT pg_drop_replication_slot('standby_dc2'); releases it.

5. autovacuum_freeze_max_age tuned too high (or tables not reached)

A very high autovacuum_freeze_max_age delays the forced anti-wraparound vacuum, leaving little margin. Combined with many large tables and few workers, autovacuum may never reach the worst offenders in time.

SHOW autovacuum_freeze_max_age;
SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind IN ('r','m','t')
ORDER BY age(relfrozenxid) DESC
LIMIT 5;

 autovacuum_freeze_max_age
---------------------------
 1500000000

      relname       |  xid_age   |  size
--------------------+------------+--------
 events_archive     | 2110448221 | 412 GB
 audit_log          | 1984220110 | 188 GB
 orders             |  221004411 |  44 GB
(3 rows)

A 1.5-billion freeze_max_age leaves only ~0.6 billion of headroom; a 412 GB table that has never been frozen can blow through that before autovacuum finishes.

6. Large tables that were never vacuumed

Append-only or archive tables receive no UPDATEs/DELETEs, so the dead-tuple thresholds that trigger normal autovacuum never fire. Only the anti-wraparound trigger touches them — too late if it lands at the limit.

SELECT relname,
       age(relfrozenxid) AS xid_age,
       last_autovacuum, last_vacuum, n_dead_tup
FROM pg_stat_user_tables t
JOIN pg_class c ON c.relname = t.relname
ORDER BY age(c.relfrozenxid) DESC
LIMIT 5;

   relname      |  xid_age   | last_autovacuum | last_vacuum | n_dead_tup
----------------+------------+-----------------+-------------+------------
 events_archive | 2110448221 |                 |             |          0
 audit_log      | 1984220110 |                 |             |          0

last_autovacuum/last_vacuum are NULL and n_dead_tup is 0 — autovacuum never had a dead-tuple reason to run. These tables need an explicit VACUUM FREEZE.

Diagnostic Workflow

Step 1: Measure XID age per database and find the headroom

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

The database with the smallest xids_remaining is the one at risk. Below a few million, treat it as an emergency.

Step 2: Identify what is holding back xmin

-- Open/idle transactions
SELECT pid, age(backend_xmin) AS xmin_age, state, now() - xact_start AS runtime
FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC LIMIT 5;

-- Prepared transactions
SELECT gid, prepared, database FROM pg_prepared_xacts;

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

Anything with a large age here caps how far freezing can advance — clear it before vacuuming, or the vacuum will not lower datfrozenxid.

Step 3: Find the specific tables driving the age

SELECT c.relname,
       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 10;

The top tables here are where freezing must happen. Target them directly rather than vacuuming the whole database blindly.

Step 4: Clear blockers, then freeze the worst tables

-- Release the blockers found in Step 2:
SELECT pg_terminate_backend(<PID>);          -- long idle-in-transaction
ROLLBACK PREPARED '<gid>';                     -- abandoned prepared xact
SELECT pg_drop_replication_slot('<slot>');     -- stale slot

-- Then freeze the highest-age tables (run with verbose to watch progress):
VACUUM (FREEZE, VERBOSE) events_archive;
VACUUM (FREEZE, VERBOSE) audit_log;

If the database is still accepting commands, online VACUUM FREEZE is enough. Re-check Step 1 — datfrozenxid age should drop sharply.

Step 5: If the cluster has already shut down, use single-user mode

When PostgreSQL is fully refusing commands, stop the postmaster and vacuum offline:

sudo systemctl stop postgresql
sudo -u postgres postgres --single -D /var/lib/postgresql/data production

At the backend> prompt:

backend> VACUUM (FREEZE, VERBOSE);
backend> <Ctrl-D to exit>

Then restart normally:

sudo systemctl start postgresql

Example Root Cause Analysis

A monitoring page fires: writes on production are failing with “not accepting commands to avoid wraparound data loss”. The database is still partially responsive, so we start with the age query.

SELECT datname, age(datfrozenxid) AS xid_age,
       2147483648 - age(datfrozenxid) AS xids_remaining
FROM pg_database ORDER BY xid_age DESC LIMIT 1;

  datname  |  xid_age   | xids_remaining
-----------+------------+----------------
 production | 2146880201 |         603447

Only ~600k XIDs of headroom. Autovacuum is on, so something must be pinning xmin. Checking slots and activity:

SELECT slot_name, active, age(xmin) AS xmin_age FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST LIMIT 1;

  slot_name  | active | xmin_age
-------------+--------+-----------
 standby_dc2 | f      | 2104882210

The standby_dc2 physical slot is inactive but pins xmin ~2.1 billion transactions back — the standby was decommissioned weeks ago but the slot was never dropped. Every freeze attempt was capped by this slot, so datfrozenxid could never advance.

Fix: drop the stale slot, then freeze the worst tables:

SELECT pg_drop_replication_slot('standby_dc2');
VACUUM (FREEZE, VERBOSE) events_archive;
VACUUM (FREEZE, VERBOSE) audit_log;

After the freeze, the age query confirms recovery:

  datname  | xid_age  | xids_remaining
-----------+----------+----------------
 production | 41002233 |     2106481415

production resumes accepting commands. The lasting fix is a check that alerts on inactive replication slots so a forgotten one can never pin xmin again.

Prevention Best Practices

• Never leave autovacuum off in production. If you must disable it for a bulk load, re-enable it immediately and run VACUUM FREEZE on the affected tables.
• Alert on age(datfrozenxid) per database (and age(relfrozenxid) per table). Page well before the warning threshold — somewhere around 1 billion is a comfortable margin.
• Monitor for xmin blockers: long idle in transaction sessions, rows in pg_prepared_xacts, and inactive replication slots. Any of these can silently halt freezing.
• Schedule explicit VACUUM FREEZE on append-only/archive tables that normal autovacuum never reaches.
• Tune autovacuum_freeze_max_age, autovacuum_max_workers, and autovacuum_vacuum_cost_limit so freezing can keep pace with your write volume; do not raise freeze_max_age toward the limit just to silence vacuums.
• For fast triage when a wraparound warning fires, the free incident assistant can turn the age and pg_stat_activity output into a likely blocker and next step.

Quick Command Reference

-- XID age and remaining headroom per database
SELECT datname, age(datfrozenxid) AS xid_age,
       2147483648 - age(datfrozenxid) AS xids_remaining
FROM pg_database ORDER BY xid_age DESC;

-- Per-table freeze age (find the worst offenders)
SELECT relname, age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class WHERE relkind IN ('r','m','t')
ORDER BY age(relfrozenxid) DESC LIMIT 10;

-- xmin blockers: transactions, prepared xacts, slots
SELECT pid, age(backend_xmin) AS xmin_age, state FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;
SELECT gid, prepared, database FROM pg_prepared_xacts;
SELECT slot_name, active, age(xmin) AS xmin_age FROM pg_replication_slots;

-- Clear blockers
-- SELECT pg_terminate_backend(<PID>);
-- ROLLBACK PREPARED '<gid>';
-- SELECT pg_drop_replication_slot('<slot>');

-- Freeze the high-age tables
VACUUM (FREEZE, VERBOSE) <table>;

-- Single-user recovery if the cluster has shut down (shell):
-- sudo systemctl stop postgresql
-- sudo -u postgres postgres --single -D /var/lib/postgresql/data <dbname>
--   backend> VACUUM (FREEZE, VERBOSE);
-- sudo systemctl start postgresql

Conclusion

The wraparound shutdown means a database’s oldest unfrozen XID got dangerously close to the 2-billion limit because freezing fell behind. The usual root causes:

1. Autovacuum disabled, or throttled too aggressively to keep up with write volume.
2. A long-running or idle in transaction session pinning xmin.
3. An abandoned prepared (two-phase) transaction holding the oldest XID.
4. A stale or inactive replication slot pinning xmin/catalog_xmin.
5. autovacuum_freeze_max_age set too high, leaving too little freezing headroom.
6. Large append-only tables that normal autovacuum never vacuums.

Always clear the xmin blocker first — vacuuming will not lower datfrozenxid while a transaction or slot holds it back. Then freeze the highest-age tables, online if you can or in single-user mode if the cluster has already stopped. For more PostgreSQL troubleshooting, see the Postgres guides.