Update the documentation on recovering from (M)XID exhaustion.

robertmhaas · robertmhaas · commit a70bce43fbce · 2023-10-16T12:57:39.000-04:00
The old documentation encourages entering single-user mode for no reason, which is a bad plan in most cases. Instead, discourage users from doing that, and explain the limited cases in which it may be desirable. The old documentation claims that running VACUUM as anyone but the superuser can't possibly work, which is not really true, because it might be that some other user has enough permissions to VACUUM all the tables that matter. Weaken the language just a bit. The old documentation claims that you can't run any commands when near XID exhaustion, which is false because you can still run commands that don't require an XID, like a SELECT without a locking clause. The old documentation doesn't clearly explain that it's a good idea to get rid of prepared transactons, long-running transactions, and replication slots that are preventing (M)XID horizon advancement. Spell out the steps to do that. Also, discourage the use of VACUUM FULL and VACUUM FREEZE in this type of scenario. Back-patch to v14. Much of this is good advice on all supported versions, but before 60f1f09 the chances of VACUUM failing in multi-user mode were much higher. Alexander Alekseev, John Naylor, Robert Haas, reviewed at various times by Peter Geoghegan, Hannu Krosing, and Andres Freund. Discussion: http://postgr.es/m/CA+TgmoYtsUDrzaHcmjFhLzTk1VEv29mO_u-MT+XWHrBJ_4nD8A@mail.gmail.com
diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
@@ -660,29 +660,79 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
-    hint; but note that the <command>VACUUM</command> must be performed by a
-    superuser, else it will fail to process system catalogs and thus not
-    be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    hint; but note that the <command>VACUUM</command> should be performed by a
+    superuser, else it will fail to process system catalogs, which prevent it from
+    being able to advance the database's <structfield>datfrozenxid</structfield>.)
+    If these warnings are ignored, the system will refuse to assign new XIDs once
+    there are fewer than three million transactions left until wraparound:
 
 <programlisting>
 ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
-    The three-million-transaction safety margin exists to let the
-    administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    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 <command>VACUUM</command> command can still be run normally.
+    Contrary to what the hint states, 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:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Resolve old prepared transactions. You can find these by checking
+       <link linkend="view-pg-prepared-xacts">pg_prepared_xacts</link> for rows where
+       <literal>age(transactionid)</literal> is large. Such transactions should be
+       committed or rolled back.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>End long-running open transactions. You can find these by checking
+       <link linkend="monitoring-pg-stat-activity-view">pg_stat_activity</link> for rows where
+       <literal>age(backend_xid)</literal> or <literal>age(backend_xmin)</literal> is
+       large. Such transactions should be committed or rolled back, or the session
+       can be terminated using <literal>pg_terminate_backend</literal>.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Drop any old replication slots. Use
+       <link linkend="monitoring-pg-stat-replication-view">pg_stat_replication</link> to
+       find slots where <literal>age(xmin)</literal> or <literal>age(catalog_xmin)</literal>
+       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.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Execute <command>VACUUM</command> in the target database. A database-wide
+       <literal>VACUUM</literal> is simplest; to reduce the time required, it as also possible
+       to issue manual <command>VACUUM</command> commands on the tables where
+       <structfield>relminxid</structfield> is oldest. Do not use <literal>VACUUM FULL</literal>
+       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 <literal>VACUUM FREEZE</literal> either, because it will do
+       more than the minimum amount of work required to restore normal operation.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Once normal operation is restored, ensure that autovacuum is properly configured
+       in the target database in order to avoid future problems.</simpara>
+     </listitem>
+    </orderedlist>
    </para>
 
+   <note>
+    <para>
+     In earlier versions, it was sometimes necessary to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. In typical scenarios, this
+     is no longer necessary, and should be avoided whenever possible, since it involves taking
+     the system down. It is also riskier, since it disables transaction ID wraparound safeguards
+     that are designed to prevent data loss.  The only reason to use single-user mode in this
+     scenario is if you wish to <command>TRUNCATE</command> or <command>DROP</command> unneeded
+     tables to avoid needing to <command>VACUUM</command> them.  The three-million-transaction
+     safety margin exists to let the administrator do this. See the
+     <xref linkend="app-postgres"/> reference page for details about using single-user mode.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
@@ -747,6 +797,38 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
      have the oldest multixact-age.  Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled.
     </para>
+
+    <para>
+     Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
+     system will begin to emit warning messages when the database's oldest MXIDs reach forty
+     million transactions from the wraparound point.  And, just as an the XID case, if these
+     warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
+     than three million left until wraparound.
+    </para>
+
+    <para>
+     Normal operation when MXIDs are exhausted can be restored in much the same way as
+     when XIDs are exhausted. Follow the same steps in the previous section, but with the
+     following differences:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Running transactions and prepared transactions can be ignored if there
+       is no chance that they might appear in a multixact.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>MXID information is not directly visible in system views such as
+       <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
+       way of determining which transactions are causing MXID wraparound problems.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>XID exhaustion will block all write transactions, but MXID exhaustion will
+       only block a subset of write transactions, specifically those that involve
+       row locks that require an MXID.</simpara>
+     </listitem>
+    </orderedlist>
+   </para>
+
    </sect3>
   </sect2>
 
