PostgreSQL Tutorial: Dealing with 'database is not accepting commands' error

September 25, 2025

Summary: in this tutorial, you will learn how to troubleshooting the error “database is not accepting commands …” in PostgreSQL.

Table of Contents

If you ever get the error message “database is not accepting commands”, you are dangerously close to transaction ID wraparound. Most PostgreSQL users understand the principle behind transaction ID wraparound, but many PostgreSQL users have a wrong idea of how to fix the problem.

How do you end up with “database is not accepting commands”?

If you end up with this error, your application will have down time while you manually repair the problem. In this state, you can still run queries, but you cannot perform any more data modifications. Few people ever get that far, because PostgreSQL has several lines of defense before it has to take this last, invasive measure:

if a table contains live rows older than autovacuum_freeze_max_age transactions (200 million by default), PostgreSQL will launch an anti-wraparound autovacuum worker
if a table contains live rows older than vacuum_failsafe_age transactions (1.6 billion by default), PostgreSQL will launch an emergency anti-wraparound autovacuum worker that skips the index cleanup step and runs as fast as it can
40 million transactions before transaction ID wraparound, you will get warnings in the log

Only if none of these safeties can prevent the problem will PostgreSQL stop data modifications.

There are a few ways to prevent PostgreSQL from fixing the problem by itself:

keep a database transaction open forever
keep a prepared transaction around without committing it or rolling it back
keep an orphaned replication slot with the standby server having hot_standby_feedback enabled
have data corruption that makes VACUUM fail

What is the proper measure against “database is not accepting commands”?

The documentation describes how to fix the problem:

In this condition, any transactions already in progress can continue, but only read-only transactions can be started. Operations that modify database records or truncate relations will fail. The VACUUM command can still be run normally. Note that, contrary to what was sometimes recommended in earlier releases, it is not necessary or desirable to stop the postmaster or enter single-user mode in order to restore normal operation. Instead, follow these steps:

Resolve old prepared transactions. You can find these by checking pg_prepared_xacts for rows where age(transactionid) is large. Such transactions should be committed or rolled back.
End long-running open transactions. You can find these by checking pg_stat_activity for rows where age(backend_xid) or age(backend_xmin) is large. Such transactions should be committed or rolled back, or the session can be terminated using pg_terminate_backend.
Drop any old replication slots. Use pg_stat_replication to find slots where age(xmin) or age(catalog_xmin) is large. In many cases, such slots were created for replication to servers that no longer exist, or that have been down for a long time. If you drop a slot for a server that still exists and might still try to connect to that slot, that replica may need to be rebuilt.
Execute VACUUM in the target database. A database-wide VACUUM is simplest; to reduce the time required, it is also possible to issue manual VACUUM commands on the tables where pg_class.relminxid is oldest. Do not use VACUUM FULL in this scenario, because it requires an XID and will therefore fail, except in super-user mode, where it will instead consume an XID and thus increase the risk of transaction ID wraparound. Do not use VACUUM FREEZE either, because it will do more than the minimum amount of work required to restore normal operation.
Once normal operation is restored, ensure that autovacuum is properly configured in the target database in order to avoid future problems.

Inferior measures that tempt users

The documentation I quoted above takes care to point out the frequent fallacies:

Many people think that you need to shut down PostgreSQL and start it in single-user mode to run VACUUM. But that is not necessary. On the contrary: starting the server in single-user mode will complicate recovery and increase the down time. Moreover, single-user mode disarms the safety that prevents you from using any more transaction IDs, and consuming transaction IDs will bring you closer to data corruption by transaction ID wraparound.
Many people think of VACUUM (FULL) as “a better VACUUM”, so they are tempted to use it in this dire situation. But VACUUM (FULL) does much more work than a plain VACUUM, and you would end up with a much longer down time. Besides, as the documentation mentions, it will force you to use the single-user mode.
Using VACUUM (FREEZE) would fix the problem, but it freezes all rows of the table. That is more work than necessary and will lead to a longer down time.

So, why do people believe that you need single-user mode to recover from “database is not accepting commands”? To answer that, we have to dig into the history of PostgreSQL.

Commit log in PostgreSQL project

In PostgreSQL, there is such an ERROR message before the following commit:

ERROR:  database is not accepting commands to avoid wraparound data loss in database "..."
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.

Reword messages about impending (M)XID exhaustion.

First, we shouldn’t recommend switching to single-user mode, because that’s terrible advice. Especially on newer versions where VACUUM will enter emergency mode when nearing (M)XID exhaustion, it’s perfectly fine to just VACUUM in multi-user mode. Doing it that way is less disruptive and avoids disabling the safeguards that prevent actual wraparound, so recommend that instead.

Second, be more precise about what is going to happen (when we’re nearing the limits) or what is happening (when we actually hit them). The database doesn’t shut down, nor does it refuse all commands. It refuses commands that assign whichever of XIDs and MXIDs are nearly exhausted.

No back-patch. The existing hint that advises going to single-user mode is sufficiently awful advice that removing it or changing it might be justifiable even though we normally avoid changing user-facing messages in back-branches, but we felt that it was better to be more conservative and limit this fix to master only. Aside from the usual risk of breaking translations, people might be used to the existing message, or even have monitoring scripts that look for it.

Discussion: http://postgr.es/m/CA+TgmoZBg95FiR9wVQPAXpGPRkacSt2okVge+PKPPFppN7sfnQ@mail.gmail.com

This commit in PostgreSQL v17 changed the error message to its current form:

ERROR:  database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "..."
HINT:  Execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.

So it is hardly surprising that many PostgreSQL old-timers still believe that you need single-user mode to recover from an impending transaction ID wraparound!