Hi Fujii-san,
Thanks for reviewing!
On 29.10.26 03:41, Fujii Masao wrote:
> + if (!LocalHotStandbyActive && !LocalPromoteIsTriggered)
>
> Do we need to use !CheckForStandbyTrigger() instead of
> !LocalPromoteIsTriggered?
> This would also handle the case where a promotion request has arrived but
> LocalPromoteIsTriggered has not yet been set.
Right, I missed that case. Changed to CheckForStandbyTrigger() as suggested.
> + errdetail("The standby snapshots stayed overflowed and the
> transactions responsible did not finish, so hot standby never
> started."),
> + errhint("Set \"recovery_target_action\" to \"promote\", or choose a
> recovery target at which hot standby can be enabled.")));
>
> Are overflowed standby snapshots the only cause of this situation?
> If not, mentioning them in the detail message could be misleading.
Good point. It can also happen, for example, in single-user mode,
where hot standby is never enabled. So I removed the errdetail; the
message and hint hold for all cases.
> We should clarify that this applies only when recovery has already reached
> a consistent state? If the recovery target is reached before that,
> the preceding check raises FATAL regardless of recovery_target_action.
> As currently worded, this could be read as covering that case too.
>
> For example, how about the following description?
>
> Likewise, if recovery has reached a consistent state but hot standby
> has not yet become active when the recovery target is reached,
> a setting of pause will act the same as shutdown. This can happen,
> for example, when the replayed standby snapshots remain overflowed.
Yes, that makes it clearer. I have adjusted the documentation as proposed.
> +# to pause and shut down instead of promoting. The server stops on its own,
> so
> +# we drive it with pg_ctl directly (a regular ->start() would error out
> because
> +# the server never becomes ready to accept connections).
> +run_log(
> + [
> + 'pg_ctl',
> + '--pgdata' => $node_standby->data_dir,
> + '--log' => $node_standby->logfile,
> + 'start',
> + ]);
>
> How about using $node_standby->start(fail_ok => 1) instead of invoking pg_ctl
> directly? This would allow the expected startup failure while tracking
> the postmaster PID, so teardown_node() could stop the server
> if it unexpectedly promotes. If we do this, the comment needs to be updated
> accordingly.
Unfortunately, I couldn't get it to work with
$node_standby->start(fail_ok => 1). When the server shuts down at the
recovery target, pg_ctl start reports "server shut down because of
recovery target settings" and exits with 0, so Cluster::start() takes
the success path, finds no postmaster.pid and BAIL_OUTs.
You're right that if a standby unexpectedly promotes, it would keep
running because Cluster.pm doesn't track its PID. So I kept starting it
with pg_ctl directly, updated the comment, and now I also stop it with
pg_ctl at the end of the test.
> +my $until_lsn =
> + $node_primary->safe_psql('postgres', 'SELECT pg_current_wal_lsn()');
>
> We can simplify this to "my $until_lsn = $node_primary->lsn('write');".
Good point, changed as suggested.
Version 6 of the patch with all of these changes is attached.
Thanks again for the careful review.
Best regards
Jan
--
Jan Nidzwetzki
PlanetScale Postgres Core Team
From 1392567e20aaa0c30c18d5d0706f8c2aa6728747 Mon Sep 17 00:00:00 2001
From: Jan Nidzwetzki <[email protected]>
Date: Mon, 15 Jun 2026 13:45:05 +0200
Subject: [PATCH v6] Shut down instead of promoting when recovery cannot pause
When recovery_target_action is set to 'pause' and recovery reaches the
target, recoveryPausesHere() only actually pauses if hot standby is
active. If hot standby never became active, recovery silently fell
through and promoted, ignoring the configured action.
One way this happens is when standbyState stays STANDBY_SNAPSHOT_PENDING.
We enter that state from an overflowed standby snapshot and leave it only
once a non-overflowed snapshot arrives, or the oldest running xid advances
past the pending snapshot's xmin. Neither happens when a long-lived
transaction keeps the snapshots overflowed. Enabling hot standby from such
a snapshot is not a safe alternative: the visibility of the missing
subtransactions cannot be determined.
Fix this by shutting down when the target is reached but hot standby is
not active, unless a promotion has been requested, in which case we honor
it. This mirrors how 'pause' is downgraded to 'shutdown' when
hot_standby is turned off. The user can then choose a different recovery
target or action.
Document the new behavior and add a TAP test that drives an
archive-recovery standby into this case and checks that it shuts down
rather than pausing or promoting.
---
doc/src/sgml/config.sgml | 5 +
src/backend/access/transam/xlogrecovery.c | 27 ++++
src/test/recovery/meson.build | 1 +
.../recovery/t/061_recovery_pause_subxid.pl | 123 ++++++++++++++++++
4 files changed, 156 insertions(+)
create mode 100644 src/test/recovery/t/061_recovery_pause_subxid.pl
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index f7db2995c3e..1d8653325fc 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4662,6 +4662,11 @@ restore_command = 'copy "C:\\server\\archivedir\\%f"
"%p"' # Windows
This setting has no effect if no recovery target is set.
If <xref linkend="guc-hot-standby"/> is not enabled, a setting of
<literal>pause</literal> will act the same as
<literal>shutdown</literal>.
+ Likewise, if recovery has reached a consistent state but hot standby
+ has not yet become active when the recovery target is reached,
+ a setting of <literal>pause</literal> will act the same as
+ <literal>shutdown</literal>. This can happen, for example, when the
+ replayed standby snapshots remain overflowed.
If the recovery target is reached while a promotion is ongoing,
a setting of <literal>pause</literal> will act the same as
<literal>promote</literal>.
diff --git a/src/backend/access/transam/xlogrecovery.c
b/src/backend/access/transam/xlogrecovery.c
index 52ac6d58423..211c4afaf8b 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -1855,6 +1855,33 @@ PerformWalRecovery(void)
proc_exit(3);
case RECOVERY_TARGET_ACTION_PAUSE:
+
+ /*
+ * If hot standby is inactive,
recoveryPausesHere() does
+ * nothing, so we would fall through
and promote, silently
+ * ignoring the requested action.
+ *
+ * Hot standby stays inactive while
standbyState is
+ * STANDBY_SNAPSHOT_PENDING. We enter
that state from an
+ * overflowed standby snapshot and
leave it only once a
+ * non-overflowed snapshot arrives, or
the oldest running
+ * xid advances past the pending
snapshot's xmin (see
+ * ProcArrayApplyRecoveryInfo()). So,
instead of falling
+ * through and silently promoting, we
shut down and let
+ * the user choose a different recovery
target or action.
+ *
+ * If a promotion has already been
triggered, let it
+ * proceed (the fall-through below
promotes) rather than
+ * shutting down.
+ */
+ if (!LocalHotStandbyActive &&
!CheckForStandbyTrigger())
+ {
+ ereport(LOG,
+
(errmsg("recovery cannot pause at the recovery target because hot standby is
not active"),
+ errhint("Set
\"recovery_target_action\" to \"promote\", or choose a recovery target at which
hot standby can be enabled.")));
+ proc_exit(3);
+ }
+
SetRecoveryPause(true);
recoveryPausesHere(true);
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index b6ac9dd07a7..c31505c99ab 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -69,6 +69,7 @@ tests += {
't/058_shutdown_crash_restart.pl',
't/059_remote_apply_status_interval.pl',
't/060_multixact_crashed_updater.pl',
+ 't/061_recovery_pause_subxid.pl',
],
},
}
diff --git a/src/test/recovery/t/061_recovery_pause_subxid.pl
b/src/test/recovery/t/061_recovery_pause_subxid.pl
new file mode 100644
index 00000000000..14f496dcbcb
--- /dev/null
+++ b/src/test/recovery/t/061_recovery_pause_subxid.pl
@@ -0,0 +1,123 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+#
+# Verify that a standby reaching a recovery target with
+# 'recovery_target_action = pause' shuts down, rather than silently
+# promoting, when hot standby never became active because the standby
+# snapshot was still incomplete (STANDBY_SNAPSHOT_PENDING).
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize primary node.
+my $node_primary = PostgreSQL::Test::Cluster->new('primary');
+$node_primary->init(allows_streaming => 1, has_archiving => 1);
+$node_primary->start;
+
+# Create a table to use by the subtransactions.
+$node_primary->safe_psql('postgres', "CREATE TABLE subxid_test (id int);");
+
+# Open a transaction with more than PGPROC_MAX_CACHED_SUBXIDS (64)
+# subtransactions and keep it open to force a snapshot overflow. The INSERT
+# (not the SAVEPOINT) is what assigns each subtransaction its XID.
+my $bg = $node_primary->background_psql('postgres');
+$bg->query_safe('BEGIN');
+for my $i (1 .. 70)
+{
+ $bg->query_safe("SAVEPOINT s$i");
+ $bg->query_safe("INSERT INTO subxid_test VALUES ($i)");
+}
+
+# Take a base backup.
+my $backup_name = 'my_backup';
+$node_primary->backup($backup_name);
+
+# Perform a checkpoint and record the LSN as recovery target.
+$node_primary->safe_psql('postgres', 'CHECKPOINT');
+my $until_lsn = $node_primary->lsn('write');
+
+# Force a segment switch so the WAL segment containing the recovery target is
+# archived and reachable through the standby's restore_command.
+$node_primary->safe_psql('postgres', 'SELECT pg_switch_wal()');
+
+# Create an archive-recovery standby that is configured to pause once it
+# reaches the target.
+my $node_standby = PostgreSQL::Test::Cluster->new('standby');
+$node_standby->init_from_backup($node_primary, $backup_name,
+ has_restoring => 1);
+$node_standby->append_conf('postgresql.conf',
+ "recovery_target_lsn = '$until_lsn'");
+$node_standby->append_conf('postgresql.conf',
+ 'recovery_target_action = pause');
+# Raise the log level so we can observe that the standby kept waiting for a
+# non-overflowed snapshot.
+$node_standby->append_conf('postgresql.conf', 'log_min_messages = debug1');
+
+# The standby reaches the recovery target but, because every snapshot it saw
+# was overflowed, it could never enable hot standby. It must therefore refuse
+# to pause and shut down instead of promoting. The server stops on its own, so
+# we drive it with pg_ctl directly (->start() would bail out because pg_ctl
+# reports success but leaves no postmaster.pid behind).
+run_log(
+ [
+ 'pg_ctl',
+ '--pgdata' => $node_standby->data_dir,
+ '--log' => $node_standby->logfile,
+ 'start',
+ ]);
+
+# Wait for the standby to resolve the recovery target. It must shut down; a
+# regressed fix would instead promote and open the server for connections.
+$node_standby->wait_for_log(
+ qr/database system is shut down|database system is ready to accept/);
+
+my $logfile = slurp_file($node_standby->logfile());
+
+# The standby actually exercised the overflowed-snapshot path...
+like(
+ $logfile,
+ qr/recovery snapshot waiting for non-overflowed snapshot/,
+ 'standby kept waiting for a non-overflowed snapshot');
+
+# ... and never reached STANDBY_SNAPSHOT_READY.
+unlike(
+ $logfile,
+ qr/recovery snapshots are now enabled/,
+ 'standby never reached a ready (non-overflowed) snapshot');
+
+# It must have shut down for the documented reason ...
+like(
+ $logfile,
+ qr/recovery cannot pause at the recovery target because hot standby is
not active/,
+ 'standby refused to pause without hot standby');
+
+# ... by taking the clean shutdown-at-recovery-target path (not a crash) ...
+like(
+ $logfile,
+ qr/shutdown at recovery target/,
+ 'standby shut down cleanly at the recovery target');
+
+# ... and never opened up for connections (neither as a hot standby nor by
+# promoting), which is what would have allowed unsafe queries.
+unlike(
+ $logfile,
+ qr/database system is ready to accept/,
+ 'standby never accepted connections');
+
+# Close the held-open transaction on the primary.
+$bg->quit;
+
+# The standby was started outside of Cluster.pm, so stop it the same way in
+# case it unexpectedly kept running.
+run_log(
+ [
+ 'pg_ctl',
+ '--pgdata' => $node_standby->data_dir,
+ '--mode' => 'immediate',
+ 'stop',
+ ]);
+$node_primary->teardown_node;
+
+done_testing();
--
2.47.3
