From f5fa3605effd624b263ff3e5dbb8b3e5c8992dba Mon Sep 17 00:00:00 2001 From: John Naylor Date: Sat, 29 Apr 2023 14:23:50 +0700 Subject: [PATCH v7 2/2] Stop telling users to run VACUUM in a single-user mode Single-user mode is almost always the worst thing to reach for in a VACUUM emergency: * Restarting in single user mode requires a shutdown checkpoint * The user interface is completely different, and awful * The buffer cache is completely cold * The checkpointer, background writer and WAL writer are not running * Without checkpoints WAL segments can not be rotated and reused * Replication is not running, so after VACUUM is done and database is started in normal mode, there is a huge backlog to replicate * pg_stat_progress_vacuum is not available so there is no indication of when the command will complete * VACUUM VERBOSE doesn't work - there is no output from single-user mode vacuum, with or without VERBOSE If that weren't enough, it's also unsafe because the wraparound limits are not enforced. It is by no means impossible to corrupt the database by mistake, such as by a user running VACUUM FULL because it sounds better. As mentioned in commit XXXXXXXXX, the system is perfectly capable of accepting commands when reaching xidStopLimit. Most VACUUM operations will work normally, with one exception: A new XID is required when truncating the relation if wal_level is above "minimal". As of v14 the failsafe mechanism disables truncation some time before reaching xidStopLimit, so this is not an issue in practice. By remaining in multi-user mode, users still have read-only access to their database, they can use parallelism, they can use command line utilities like vacuumdb, and they can remotely access the database using normal clients. The only reason to restart in single-user mode is to DROP or TRUNCATE a table, when it is suspected that doing that would be faster than vacuuming. Also add an explicit note warning against using single-user mode. Backpatch to v14, which is the first version with the VACUUM failsafe. XXX We should consider v12-13 with "VACUUM (TRUNCATE off, INDEX_CLEANUP off);", but it's not yet convenient to get to xidStopLimit before v14. XXX We could consider v11 with careful instructions about redoing VACUUMs in single-user mode when truncation is necessary. Aleksander Alekseev, with some adjustments by me Reported as various times by (at least) Hannu Krosing, Robert Haas, and Andres Freund Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com Discussion: https://www.postgresql.org/message-id/CA%2BTgmob1QCMJrHwRBK8HZtGsr%2B6cJANRQw2mEgJ9e%3DD%2Bz7cOsw%40mail.gmail.com Discussion: https://www.postgresql.org/message-id/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com Discussion: https://www.postgresql.org/message-id/CA%2BTgmoYPfofQmRtUan%3DA3aWE9wFsJaOFr%2BW_ys2pPkNPr-2FZw%40mail.gmail.com --- doc/src/sgml/maintenance.sgml | 32 +++++++++++++++++++++-------- src/backend/access/transam/varsup.c | 4 ++-- 2 files changed, 25 insertions(+), 11 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 105a9900cb..ae9a35fb73 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -669,22 +669,36 @@ HINT: To prevent entering read-only mode, execute a database-wide VACUUM in tha ERROR: database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb" -HINT: Stop the postmaster and vacuum that database in single-user mode. +HINT: Execute a database-wide VACUUM in that database. 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 three-million-transaction safety margin exists to let the - administrator recover without data loss, by manually executing the - required VACUUM commands. However - the only way to do this is to stop the server and start the server in single-user - mode to execute VACUUM. See the - reference page for details about using - single-user mode. + The VACUUM command can still be run normally to recover. +