© Laurenz Albe 2025
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 I recently realized that even many PostgreSQL power users have a wrong idea of how to fix the problem. So I decided to write about it in some more detail.
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:
autovacuum_freeze_max_age transactions (200 million by default), PostgreSQL will launch an anti-wraparound autovacuum workervacuum_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 canOnly 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:
hot_standby_feedback enabledVACUUM failThe 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
VACUUMcommand 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)orage(backend_xmin)is large. Such transactions should be committed or rolled back, or the session can be terminated usingpg_terminate_backend.- Drop any old replication slots. Use pg_stat_replication to find slots where
age(xmin)orage(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
VACUUMin the target database. A database-wideVACUUMis simplest; to reduce the time required, it is also possible to issue manualVACUUMcommands on the tables whererelminxidis oldest. Do not useVACUUM FULLin 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 useVACUUM FREEZEeither, 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.
The documentation I quoted above takes care to point out the frequent fallacies:
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.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 (see the previous point).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.
If you don't know how to analyze the history of the PostgreSQL code, I recommend that you read my article about the true power of open source.
Commit 60b2444cc3 added the safety margin of three million transaction IDs in 2005 with a message like this:
|
1 2 |
ERROR: database is shut down to avoid wraparound data loss in database "..." HINT: Stop the postmaster and use a standalone backend to VACUUM in "...". |
You see the first mention of single-user mode in the form “standalone backend”. Since the server didn't actually shut down, commit 8ad3965a11, also in 2005, amended the message to read
|
1 2 |
ERROR: database is not accepting queries to avoid wraparound data loss in database "..." HINT: Stop the postmaster and use a standalone backend to VACUUM database "...". |
Note that back then, the advice to use single-user mode was actually correct, since VACUUM used to consume a transaction ID back then. In 2009, commit 8d4f2ecd41 extended the hint to
|
1 2 3 |
ERROR: database is not accepting queries to avoid wraparound data loss in database "..." HINT: Stop the postmaster and use a standalone backend to VACUUM database "...". You might also need to commit or roll back old prepared transactions. |
In 2013, commit 7dfd5cd21c changed the wording to “single-user mode”:
|
1 2 3 |
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. |
And in 2017, commit 2958a672b1 added stale replication slots as a possible cause:
|
1 2 3 |
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. |
This was the state of affairs until PostgreSQL v17. By then, the message had long been inaccurate, ever since commit 295e63983d introduced “lazy transaction ID allocation” in 2007. In October 2023, commit 2406c4e34c finally changed the error message to its current form:
|
1 2 3 |
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!
We reviewed the correct way to handle “database is not accepting commands” (a plan VACUUM) and explored various inferior responses to the situation. This gave me the opportunity to dig into the history of the PostgreSQL source code again!
You are currently viewing a placeholder content from Turnstile. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
My colleague Christophe reminded me that, before v12, it wasn't possible to use VACUUM with TRUNCATE disabled. However, if VACUUM wants to truncate a table, it uses a real XID. In certain cases, therefore, you had to restart in single-user mode in versions older than v12.
Great blog post anyway, thanks!