How to fix database isn't accepting commands to avoid wraparound data loss in PostgreSQL

PostgreSQLADVANCEDCRITICALUpdated Dec 22, 2025· 148 views

PostgreSQL has stopped accepting write commands to prevent transaction ID wraparound from corrupting data. This emergency shutdown occurs when the database approaches the critical 2-billion-transaction threshold and autovacuum has failed to freeze old transaction IDs.

Quick fix

Fixes database isn't accepting commands to avoid wraparound data loss

SELECT pid, usename, state, backend_xmin, backend_xid,
       now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
  1. Immediately identify and terminate blocking transactions
  2. Check the severity of wraparound age
  3. Run VACUUM as a superuser to freeze transaction IDs
  4. Verify vacuum progress with pg_stat_progress_vacuum
postgresql — error
SELECT pid, usename, state, backend_xmin, backend_xid,
       now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
database isn't accepting commands to avoid wraparound data loss

What this error means

This error indicates that PostgreSQL has entered a protective read-only state to prevent catastrophic data loss. PostgreSQL uses 32-bit transaction IDs (XIDs) which wrap around after approximately 4.3 billion transactions. Due to how MVCC (Multi-Version Concurrency Control) works, if transaction IDs wrap around without proper freezing, rows that were inserted long ago will appear to be from the future, making them invisible to queries and effectively corrupting data. When PostgreSQL detects that the database is dangerously close to this wraparound point (within ~3 million unfrozen transactions), it stops accepting any commands that would assign new transaction IDs. This includes INSERT, UPDATE, DELETE, and most other DML operations. The database remains in this state until you manually vacuum all tables to freeze old transaction IDs. This is a last-resort safety mechanism - the only worse outcome would be silent data corruption. The error message is PostgreSQL saying "I'm shutting down writes to save your data from invisible corruption."

Typical symptoms

  • ERROR: database isn't accepting commands to avoid wraparound data loss in database "dbname"
  • All write operations (INSERT, UPDATE, DELETE) fail with the wraparound error
  • Read operations (SELECT) continue to work
  • New connections can be made, but write commands are rejected
  • Application receives connection/transaction errors from write attempts
  • Database appears frozen - data cannot be modified
  • Logs show CRITICAL or FATAL warnings about approaching transaction ID wraparound

Common causes

  1. Autovacuum disabled or not running frequently enough to freeze old transaction IDs
  2. Long-running transactions blocking autovacuum from advancing datfrozenxid
  3. Uncommitted connections holding locks that prevent autovacuum from completing
  4. Database experiencing very high transaction rate that exceeds autovacuum capacity
  5. Autovacuum aggressively throttled by cost parameters, unable to keep up with transaction rate
  6. Very large tables where VACUUM takes longer than the interval between transactions
  7. Connections with open transactions preventing freezing of newer transaction IDs
  8. autovacuum_freeze_max_age set too high or disabled entirely

How to fix "database isn't accepting commands to avoid wraparound data loss"

1Immediately identify and terminate blocking transactions

The first thing to do is find what's preventing autovacuum. Connect as a superuser and check for old transactions:

sql
SELECT pid, usename, state, backend_xmin, backend_xid,
       now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

These transactions are preventing autovacuum from advancing the frozen transaction ID. Gracefully terminate them:

sql
-- For blocking transactions, try gentle termination first
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE pid != pg_backend_pid()
  AND backend_xmin IS NOT NULL
  AND now() - xact_start > interval '1 hour';

-- If that doesn't work, force termination
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid != pg_backend_pid()
  AND backend_xmin IS NOT NULL
  AND now() - xact_start > interval '1 hour';

Focus on terminating idle transactions that are doing nothing but holding a transaction ID snapshot.

2Check the severity of wraparound age

Determine how close you are to the critical 2-billion transaction wraparound point:

sql
SELECT datname, age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS txids_remaining,
       CASE
         WHEN 2147483647 - age(datfrozenxid) < 1000000 THEN 'CRITICAL'
         WHEN 2147483647 - age(datfrozenxid) < 10000000 THEN 'DANGER'
         ELSE 'MONITOR'
       END AS status
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

If "txids_remaining" is less than 1 million, you are in critical state and write operations are blocked. You need to vacuum immediately.

Also check individual tables:

sql
SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY age DESC
LIMIT 10;
3Run VACUUM as a superuser to freeze transaction IDs

Once blocking transactions are cleared, run VACUUM on all databases. The standard approach is:

sql
-- Connect to each database and run this (as superuser)
VACUUM ANALYZE;

If you have very large tables and want faster completion, you can optimize:

sql
-- PostgreSQL 12+ with optimized freezing
VACUUM (INDEX_CLEANUP OFF, TRUNCATE OFF) large_table_name;

-- PostgreSQL 11 and earlier - just use plain VACUUM
VACUUM;

For an entire cluster, use the vacuumdb utility from the command line:

bash
# Vacuum all databases with multiple workers
vacuumdb --all --jobs 4 --verbose

# Or target specific database
vacuumdb --dbname yourdb --jobs 4 --verbose

DO NOT use VACUUM FULL - it requires transaction IDs and will make the problem worse in a wraparound situation.

4Verify vacuum progress with pg_stat_progress_vacuum

On PostgreSQL 13+, you can monitor vacuum progress in real-time:

sql
SELECT p.pid, p.datname, p.relid::regclass AS table_name,
       p.phase, p.heap_blks_total, p.heap_blks_scanned,
       round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS percent_complete,
       now() - a.xact_start AS duration
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON p.pid = a.pid
ORDER BY p.pid;

On PostgreSQL 12 and earlier, monitor with pg_stat_activity:

sql
SELECT pid, datname, duration, query
FROM (
  SELECT pid, datname, now() - xact_start AS duration, query
  FROM pg_stat_activity
  WHERE query LIKE '%VACUUM%'
) v
ORDER BY duration DESC;

The vacuum may take a very long time on large databases. Do not interrupt it.

5Confirm database accepts write commands again

Once VACUUM completes, test that write operations work:

sql
-- Simple test insert
BEGIN;
  INSERT INTO test_write_check (value) VALUES ('test');
ROLLBACK;  -- Don't keep the test data

If this succeeds, your database is out of wraparound protection mode and accepting writes normally. Verify the transaction ID age dropped significantly:

sql
SELECT datname, age(datfrozenxid) AS xid_age,
       2147483647 - age(datfrozenxid) AS txids_remaining
FROM pg_database
WHERE datname = current_database();

The xid_age should now be in the thousands or low millions, depending on your transaction rate.

Advanced notes

Why Wraparound is Catastrophic: PostgreSQL's MVCC model stores visibility information in row headers as transaction IDs. The visibility rules are: if a row's xmin (insert transaction) is older than the current transaction, the row is visible; if xmin is in the future, the row is hidden. Transaction IDs only make sense within a ~2.1 billion transaction window. Beyond that, the modular arithmetic breaks and "future" becomes "past" - making old rows invisible.

Freezing Mechanism: VACUUM FREEZE sets special hint bits (HEAP_XMIN_FROZEN) on rows to mark them as permanently visible without checking transaction IDs. This removes those rows from wraparound considerations. The database's datfrozenxid advances to the XID of the oldest unfrozen row.

Read-Only Mode Threshold: PostgreSQL enters this protective state when fewer than 3 million transaction IDs remain before wraparound (at approximately 2,147,480,000 XIDs out of 2^31). At 1 million XIDs remaining, even superuser connections are restricted.

Failsafe Autovacuum: PostgreSQL 14+ includes a failsafe VACUUM mechanism that runs with extraordinary priority to prevent wraparound shutdown, but this still causes performance impact. Avoiding this situation through proactive monitoring is essential.

Recovery Without VACUUM: If VACUUM takes too long on a very large database and you can afford data loss, the faster option is to restore from a backup. A restored backup will have a much lower transaction ID age.

Per-Table Freezing: If one table is causing the problem, you can vacuum it individually in parallel:

sql
ALTER TABLE problem_table SET (autovacuum_freeze_min_age = 0);
VACUUM FREEZE problem_table;

Monitoring Integration: Set up database monitoring to alert when age(datfrozenxid) exceeds 1.5 billion (roughly 70% of the way to wraparound). Use PostgreSQL's pg_database view or managed service monitoring (RDS CloudWatch, Azure Database alerts, Google Cloud SQL recommender) to catch issues before they require emergency action.

Connection Pooling: If using PgBouncer or connection poolers, ensure you can still connect to the database as a superuser for maintenance. Connection poolers should not be the only connection mechanism during a wraparound incident.

Frequently asked questions

What does "database isn't accepting commands to avoid wraparound data loss" mean?
This error indicates that PostgreSQL has entered a protective read-only state to prevent catastrophic data loss. 3 billion transactions. Due to how MVCC (Multi-Version Concurrency Control) works, if transaction IDs wrap around without proper freezing, rows that were inserted long ago will appear to be from the future, making them invisible to queries and effectively corrupting data.
How do I fix "database isn't accepting commands to avoid wraparound data loss"?
PostgreSQL has stopped accepting write commands to prevent transaction ID wraparound from corrupting data. This emergency shutdown occurs when the database approaches the critical 2-billion-transaction threshold and autovacuum has failed to freeze old transaction IDs.
What causes "database isn't accepting commands to avoid wraparound data loss"?
Autovacuum disabled or not running frequently enough to freeze old transaction IDs Long-running transactions blocking autovacuum from advancing datfrozenxid Uncommitted connections holding locks that prevent autovacuum from completing
Is the "database isn't accepting commands to avoid wraparound data loss" error serious?
Yes. This is a critical-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading