v9-0003-Rough-draft-of-complete-steps-to-recover-from-M-X.patch

From 0e9d6ea72216b196d37de4629736c0cf34e7cd5c Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Sat, 13 May 2023 11:03:40 +0700
Subject: [PATCH v9 3/3] Rough draft of complete steps to recover from (M)XID
 generation failure

TODO: squash with previous
---
 doc/src/sgml/maintenance.sgml | 61 ++++++++++++++++++++++++++++++++++-
 1 file changed, 60 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 45d6cd1815..fee88cb647 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -675,7 +675,25 @@ 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 <command>VACUUM</command> command can still be run normally to recover.
+    The <command>VACUUM</command> command can still be run normally.
+    To restore normal operation:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Commit or rollback any prepared transactions.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Terminate any sessions that might have open transactions.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Drop any old replication slots.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Ensure autovacuum is running, and execute a database-wide <command>VACUUM</command>.
+        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.</simpara>
+     </listitem>
+    </orderedlist>
    </para>
 
    <note>
@@ -761,6 +779,47 @@ HINT:  Execute a database-wide VACUUM in that database.
      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 like this when the database's
+    oldest MXIDs reach forty million transactions from the wraparound point:
+
+<programlisting>
+WARNING:  database "mydb" must be vacuumed within 39985967 transactions
+HINT:  To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.
+</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 refuse to generate new
+    MXIDs once there are fewer than three million left until wraparound:
+
+<programlisting>
+ERROR:  database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database "mydb"
+HINT:  Execute a database-wide VACUUM in that database.
+</programlisting>
+   </para>
+
+   <para>
+    To restore normal operation:
+    <orderedlist>
+     <listitem>
+      <simpara>Commit or rollback each prepared transaction that might appear in a multixact.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Resolve each transaction that might appear in a multixact.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Ensure autovacuum is running, and execute a database-wide <command>VACUUM</command>.
+        To reduce the time required, it as also possible to issue manual <command>VACUUM</command>
+        commands on the tables where <structfield>relminmxid</structfield> is oldest.</simpara>
+     </listitem>
+    </orderedlist>
+   </para>
+
    </sect3>
   </sect2>
 
-- 
2.39.2