Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfdfb141

Browse files
committed
Update the documentation on recovering from (M)XID exhaustion.
The old documentation encourages entering single-user mode for noreason, which is a bad plan in most cases. Instead, discourage usersfrom doing that, and explain the limited cases in which it may bedesirable.The old documentation claims that running VACUUM as anyone but thesuperuser can't possibly work, which is not really true, because itmight be that some other user has enough permissions to VACUUM allthe tables that matter. Weaken the language just a bit.The old documentation claims that you can't run any commandswhen near XID exhaustion, which is false because you can stillrun commands that don't require an XID, like a SELECT without alocking clause.The old documentation doesn't clearly explain that it's a good ideato get rid of prepared transactons, long-running transactions, andreplication 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 inthis type of scenario.Back-patch to v14. Much of this is good advice on all supportedversions, but before60f1f09the chances of VACUUM failing in multi-user mode were much higher.Alexander Alekseev, John Naylor, Robert Haas, reviewed at varioustimes by Peter Geoghegan, Hannu Krosing, and Andres Freund.Discussion:http://postgr.es/m/CA+TgmoYtsUDrzaHcmjFhLzTk1VEv29mO_u-MT+XWHrBJ_4nD8A@mail.gmail.com
1 parent5e39884 commitfdfb141

File tree

1 file changed

+97
-15
lines changed

1 file changed

+97
-15
lines changed

‎doc/src/sgml/maintenance.sgml‎

Lines changed: 97 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -659,29 +659,79 @@ HINT: To avoid a database shutdown, execute a database-wide VACUUM in that data
659659
</programlisting>
660660

661661
(A manual <command>VACUUM</command> should fix the problem, as suggested by the
662-
hint; but note that the <command>VACUUM</command> must be performed by a
663-
superuser, else it will fail to process system catalogs and thus not
664-
be able to advance the database's <structfield>datfrozenxid</structfield>.)
665-
If these warnings are
666-
ignored, the system will shut down and refuse to start any new
667-
transactions once there are fewer than three million transactions left
668-
until wraparound:
662+
hint; but note that the <command>VACUUM</command> should be performed by a
663+
superuser, else it will fail to process system catalogs, which prevent it from
664+
being able to advance the database's <structfield>datfrozenxid</structfield>.)
665+
If these warnings are ignored, the system will refuse to assign new XIDs once
666+
there are fewer than three million transactions left until wraparound:
669667

670668
<programlisting>
671669
ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb"
672670
HINT: Stop the postmaster and vacuum that database in single-user mode.
673671
</programlisting>
674672

675-
The three-million-transaction safety margin exists to let the
676-
administrator recover without data loss, by manually executing the
677-
required <command>VACUUM</command> commands. However, since the system will not
678-
execute commands once it has gone into the safety shutdown mode,
679-
the only way to do this is to stop the server and start the server in single-user
680-
mode to execute <command>VACUUM</command>. The shutdown mode is not enforced
681-
in single-user mode. See the <xref linkend="app-postgres"/> reference
682-
page for details about using single-user mode.
673+
In this condition any transactions already in progress can continue,
674+
but only read-only transactions can be started. Operations that
675+
modify database records or truncate relations will fail.
676+
The <command>VACUUM</command> command can still be run normally.
677+
Contrary to what the hint states, it is not necessary or desirable to stop the
678+
postmaster or enter single user-mode in order to restore normal operation.
679+
Instead, follow these steps:
680+
681+
<orderedlist>
682+
<listitem>
683+
<simpara>Resolve old prepared transactions. You can find these by checking
684+
<link linkend="view-pg-prepared-xacts">pg_prepared_xacts</link> for rows where
685+
<literal>age(transactionid)</literal> is large. Such transactions should be
686+
committed or rolled back.</simpara>
687+
</listitem>
688+
<listitem>
689+
<simpara>End long-running open transactions. You can find these by checking
690+
<link linkend="monitoring-pg-stat-activity-view">pg_stat_activity</link> for rows where
691+
<literal>age(backend_xid)</literal> or <literal>age(backend_xmin)</literal> is
692+
large. Such transactions should be committed or rolled back, or the session
693+
can be terminated using <literal>pg_terminate_backend</literal>.</simpara>
694+
</listitem>
695+
<listitem>
696+
<simpara>Drop any old replication slots. Use
697+
<link linkend="monitoring-pg-stat-replication-view">pg_stat_replication</link> to
698+
find slots where <literal>age(xmin)</literal> or <literal>age(catalog_xmin)</literal>
699+
is large. In many cases, such slots were created for replication to servers that no
700+
longer exist, or that have been down for a long time. If you drop a slot for a server
701+
that still exists and might still try to connect to that slot, that replica may
702+
need to be rebuilt.</simpara>
703+
</listitem>
704+
<listitem>
705+
<simpara>Execute <command>VACUUM</command> in the target database. A database-wide
706+
<literal>VACUUM</literal> is simplest; to reduce the time required, it as also possible
707+
to issue manual <command>VACUUM</command> commands on the tables where
708+
<structfield>relminxid</structfield> is oldest. Do not use <literal>VACUUM FULL</literal>
709+
in this scenario, because it requires an XID and will therefore fail, except in super-user
710+
mode, where it will instead consume an XID and thus increase the risk of transaction ID
711+
wraparound. Do not use <literal>VACUUM FREEZE</literal> either, because it will do
712+
more than the minimum amount of work required to restore normal operation.</simpara>
713+
</listitem>
714+
<listitem>
715+
<simpara>Once normal operation is restored, ensure that autovacuum is properly configured
716+
in the target database in order to avoid future problems.</simpara>
717+
</listitem>
718+
</orderedlist>
683719
</para>
684720

721+
<note>
722+
<para>
723+
In earlier versions, it was sometimes necessary to stop the postmaster and
724+
<command>VACUUM</command> the database in a single-user mode. In typical scenarios, this
725+
is no longer necessary, and should be avoided whenever possible, since it involves taking
726+
the system down. It is also riskier, since it disables transaction ID wraparound safeguards
727+
that are designed to prevent data loss. The only reason to use single-user mode in this
728+
scenario is if you wish to <command>TRUNCATE</command> or <command>DROP</command> unneeded
729+
tables to avoid needing to <command>VACUUM</command> them. The three-million-transaction
730+
safety margin exists to let the administrator do this. See the
731+
<xref linkend="app-postgres"/> reference page for details about using single-user mode.
732+
</para>
733+
</note>
734+
685735
<sect3 id="vacuum-for-multixact-wraparound">
686736
<title>Multixacts and Wraparound</title>
687737

@@ -746,6 +796,38 @@ HINT: Stop the postmaster and vacuum that database in single-user mode.
746796
have the oldest multixact-age. Both of these kinds of aggressive
747797
scans will occur even if autovacuum is nominally disabled.
748798
</para>
799+
800+
<para>
801+
Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
802+
system will begin to emit warning messages when the database's oldest MXIDs reach forty
803+
million transactions from the wraparound point. And, just as an the XID case, if these
804+
warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
805+
than three million left until wraparound.
806+
</para>
807+
808+
<para>
809+
Normal operation when MXIDs are exhausted can be restored in much the same way as
810+
when XIDs are exhausted. Follow the same steps in the previous section, but with the
811+
following differences:
812+
813+
<orderedlist>
814+
<listitem>
815+
<simpara>Running transactions and prepared transactions can be ignored if there
816+
is no chance that they might appear in a multixact.</simpara>
817+
</listitem>
818+
<listitem>
819+
<simpara>MXID information is not directly visible in system views such as
820+
<literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
821+
way of determining which transactions are causing MXID wraparound problems.</simpara>
822+
</listitem>
823+
<listitem>
824+
<simpara>XID exhaustion will block all write transactions, but MXID exhaustion will
825+
only block a subset of write transactions, specifically those that involve
826+
row locks that require an MXID.</simpara>
827+
</listitem>
828+
</orderedlist>
829+
</para>
830+
749831
</sect3>
750832
</sect2>
751833

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp