PostgreSQL: database is not accepting commands to avoid wraparound data loss

Writes fail abruptly. SELECT still works, but INSERT, UPDATE, DELETE, and DDL return an error like:

ERROR: database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "..."

This is PostgreSQL’s emergency brake, not a crash. The engine has stopped issuing new transaction IDs to prevent tuple visibility corruption. Modern PostgreSQL lets you recover without restarting or entering single-user mode. Remove whatever is blocking VACUUM progress, then freeze old rows.

What this means

PostgreSQL assigns every transaction a 32-bit unsigned transaction ID (XID). The counter cycles after roughly 2.1 billion transactions. If the counter wrapped to zero, old and new transactions would become indistinguishable, corrupting row visibility. VACUUM marks old tuples with a frozen XID, permanently unlinking them from the live counter.

When the oldest unfrozen XID in a database approaches the limit, PostgreSQL escalates through several defenses before shutting down writes:

1. Anti-wraparound autovacuum triggers at autovacuum_freeze_max_age (default 200 million transactions).
2. Emergency autovacuum triggers at vacuum_failsafe_age (default 1.6 billion), bypassing cost delays and index cleanup.
3. Log warnings fire when roughly 40 million XIDs remain.
4. When approximately 3 million XIDs remain, the database rejects any command that would consume a new XID.

Read-only queries that do not assign XIDs continue to work, but writes and DDL are blocked. In PostgreSQL 17 and later, the error message explicitly states “that assign new XIDs,” and the hint tells you to execute a database-wide VACUUM. Older versions suggested single-user mode, but that advice is outdated: modern PostgreSQL does not require it, and using single-user mode in current versions removes the safety guard and allows further XID consumption.

flowchart TD
    A[Normal operation] --> B[XID age grows]
    B --> C{Autovacuum freezes tuples?}
    C -->|Yes| A
    C -->|No| D[Anti-wraparound vacuum at 200M]
    D --> E{Progress?}
    E -->|Yes| A
    E -->|No| F[Emergency vacuum at 1.6B]
    F --> G{Progress?}
    G -->|Yes| A
    G -->|No| H[Warnings at 40M remaining]
    H --> I[Shutdown at 3M remaining]
    I --> J[Database rejects new XIDs]

Common causes

Quick checks

Run these safe, read-only queries to assess scope.

# Database XID age
psql -c "SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;"

# Per-table frozen XID age
psql -c "SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' ORDER BY 2 DESC LIMIT 10;"

# Sessions holding back vacuum
psql -c "SELECT pid, state, age(backend_xmin), query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;"

# Prepared transactions
psql -c "SELECT gid, prepared, age(transaction) FROM pg_prepared_xacts;"

# Replication slots
psql -c "SELECT slot_name, active, age(xmin), age(catalog_xmin) FROM pg_replication_slots;"

# Autovacuum recency on active tables
psql -c "SELECT schemaname, relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

How to diagnose it

1. Confirm the affected database. The error names a specific database. Run age(datfrozenxid) for every database to see which one is closest to the limit.
2. Find the oldest table. Query pg_class for age(relfrozenxid). The table with the oldest value is often the root cause, especially if it is an insert-only or archive table that default autovacuum ignores.
3. Identify blockers. Check pg_stat_activity for sessions with an old backend_xmin or backend_xid. Check pg_prepared_xacts for forgotten two-phase commits. Check pg_replication_slots for old catalog_xmin from inactive slots.
4. Review PostgreSQL logs. Look for earlier warnings such as “database must be vacuumed within N transactions” or autovacuum errors that explain why freeze scans failed.
5. Determine if autovacuum is running but blocked, or not running at all. If last_autovacuum is recent but XID age still grows, the worker is likely blocked by a long transaction or replication slot. If last_autovacuum is stale or NULL, the table may need per-table tuning.

Metrics and signals to monitor

Fixes

Clear blockers before vacuuming

A plain VACUUM will not help if something is pinning the oldest XID. Resolve these first:

• Prepared transactions. Commit or rollback any old prepared transactions. Replace 'gid' with the actual global identifier from pg_prepared_xacts.

  ROLLBACK PREPARED 'gid';
  

• Long-running sessions. Identify sessions holding old backend_xmin, confirm the query is safe to kill, then terminate the specific blocker. This is disruptive.

  -- Identify the oldest blocker
  SELECT pid, age(backend_xmin), query
  FROM pg_stat_activity
  WHERE backend_xmin IS NOT NULL
  ORDER BY age(backend_xmin) DESC;
  
  -- Terminate the specific PID
  SELECT pg_terminate_backend(pid);
  

• Stale replication slots. If a slot belongs to a decommissioned consumer, drop it. Dropping a slot forces the consumer to reinitialize if it ever comes back.

  SELECT pg_drop_replication_slot('slot_name');
  

Run the right VACUUM

Once blockers are removed, run a database-wide VACUUM in the affected database:

-- Run in the affected database
VACUUM;

If wraparound protection triggers mid-vacuum, run:

VACUUM (TRUNCATE OFF);

Do not run VACUUM FULL. It requires an XID and will fail under wraparound protection. Do not run VACUUM (FREEZE) unless you have a specific reason; it performs more aggressive freezing than necessary and extends I/O pressure when you need the shortest possible recovery.

Monitor progress with:

SELECT * FROM pg_stat_progress_vacuum;

If the database is refusing connections entirely and you cannot run VACUUM normally, emergency restart is a last resort. This risks corruption and should only be used when normal recovery is impossible.

Prevention

• Alert on age(datfrozenxid) at 500 million (early warning) and 1 billion (urgent intervention required).
• Tune per-table autovacuum for high-churn and insert-only tables. Default thresholds often never trigger on large archive tables.
• Set idle_in_transaction_session_timeout to a reasonable value (for example, 2 minutes) to prevent abandoned transactions from blocking vacuum indefinitely.
• Monitor replication slots. Alert on inactive slots and slot lag. Drop slots when consumers are decommissioned.
• Review pg_prepared_xacts during maintenance windows. Prepared transactions should never sit open for days.

How Netdata helps

• Netdata tracks transaction ID age across databases, letting you spot wraparound pressure weeks before the safety shutdown.
• It correlates autovacuum worker activity with table bloat and long-running transaction counts, so you can distinguish between vacuum that is running and vacuum that is blocked.
• It tracks replication slot health and connection state distributions, helping you find the root blocker without manual querying during an incident.

Related guides

• How PostgreSQL actually works in production: a mental model for operators
• PostgreSQL ALTER TABLE blocked: zero-downtime DDL patterns
• PostgreSQL autovacuum blocked by long-running transaction: detection and fix
• PostgreSQL autovacuum not running: detection, causes, and fixes
• PostgreSQL autovacuum tuning: per-table thresholds for high-churn workloads
• PostgreSQL blocking queries: finding the root blocker in a lock cascade
• PostgreSQL connection exhaustion: detection, diagnosis, and prevention
• PostgreSQL connection refused: pg_hba, listen_addresses, and TCP diagnosis
• PostgreSQL dead tuples piling up: why autovacuum can’t keep up
• PostgreSQL deadlock detected: how to diagnose and prevent deadlocks
• PostgreSQL frozen XID monitoring: catching wraparound 6 months early
• PostgreSQL idle in transaction: detecting and killing zombie sessions