v1-0001-Fix-cascading-standby-reconnect-failure-after-arc.patch

From eda1bba6a9a64474c27c780e2b810a21b275448b Mon Sep 17 00:00:00 2001
From: alterego655 <824662526@qq.com>
Date: Thu, 29 Jan 2026 20:14:04 +0800
Subject: [PATCH v1] Fix cascading standby reconnect failure after archive
 fallback

A cascading standby could fail to reconnect to its upstream standby
with "requested starting point ... is ahead of the WAL flush position"
after falling back to archive recovery. This occurred when the cascade
restored WAL from archive that advanced its read position (RecPtr)
beyond what the upstream could serve.

Fix by tracking the last confirmed flush position from streaming
(lastStreamedFlush) and clamping the streaming start request to that
position when it exceeds RecPtr on the same timeline. For timeline
switches, fall back to the timeline switchpoint as a safe boundary.

The fix ensures the cascade never requests WAL beyond what was
previously confirmed available via streaming, while preserving normal
startup behavior when no prior streaming has occurred.
---
 src/backend/access/transam/xlogrecovery.c | 29 +++++++
 src/test/recovery/t/001_stream_rep.pl     | 93 +++++++++++++++++++++++
 2 files changed, 122 insertions(+)

diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 117d8d8bb6b..a843c5e9c96 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -264,6 +264,10 @@ static XLogSource XLogReceiptSource = XLOG_FROM_ANY;
 static XLogRecPtr flushedUpto = 0;
 static TimeLineID receiveTLI = 0;
 
+/* Last WAL flush position observed from walreceiver, with its timeline */
+static XLogRecPtr lastStreamedFlush = InvalidXLogRecPtr;
+static TimeLineID lastStreamedFlushTLI = 0;
+
 /*
  * Copy of minRecoveryPoint and backupEndPoint from the control file.
  *
@@ -3908,6 +3912,26 @@ WaitForWALToBecomeAvailable(XLogRecPtr RecPtr, bool randAccess,
 							 */
 							tli = tliOfPointInHistory(tliRecPtr, expectedTLEs);
 
+							/*
+							 * Clamp the streaming start position to the last
+							 * walreceiver flush on the same timeline to avoid
+							 * requesting WAL ahead of the upstream.
+							 */
+							if (lastStreamedFlushTLI == tli &&
+								XLogRecPtrIsValid(lastStreamedFlush) &&
+								lastStreamedFlush < ptr)
+								ptr = lastStreamedFlush;
+							else if (lastStreamedFlushTLI != tli)
+							{
+								TimeLineID	nextTLI;
+								XLogRecPtr	switchpoint;
+
+								switchpoint = tliSwitchPoint(tli, expectedTLEs,
+															 &nextTLI);
+								if (XLogRecPtrIsValid(switchpoint))
+									ptr = Min(ptr, switchpoint);
+							}
+
 							if (curFileTLI > 0 && tli < curFileTLI)
 								elog(ERROR, "according to history file, WAL location %X/%08X belongs to timeline %u, but previous recovered WAL file came from timeline %u",
 									 LSN_FORMAT_ARGS(tliRecPtr),
@@ -3952,6 +3976,11 @@ WaitForWALToBecomeAvailable(XLogRecPtr RecPtr, bool randAccess,
 						if (RecPtr < flushedUpto && receiveTLI == curFileTLI)
 						{
 							havedata = true;
+							if (XLogRecPtrIsValid(flushedUpto))
+							{
+								lastStreamedFlush = flushedUpto;
+								lastStreamedFlushTLI = receiveTLI;
+							}
 							if (latestChunkStart <= RecPtr)
 							{
 								XLogReceiptTime = GetCurrentTimestamp();
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index e9ac67813c7..b40ffc927f8 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -642,4 +642,97 @@ ok( pump_until(
 	'base backup cleanly canceled');
 $sigchld_bb->finish();
 
+# Test cascading standby reconnection after archive fallback.
+#
+# This verifies that a cascading standby can reconnect to its upstream after
+# falling back to archive recovery, even when local recovery position has
+# advanced beyond the upstream's flush position.  This can happen when the
+# cascading standby has direct access to the archive and the upstream standby
+# is archive-only (no streaming from primary).
+note "testing cascading standby reconnection with archive fallback";
+
+# Create a new primary with archiving for this test.
+my $node_primary2 = PostgreSQL::Test::Cluster->new('primary_arch');
+$node_primary2->init(has_archiving => 1, allows_streaming => 1);
+$node_primary2->append_conf('postgresql.conf', "archive_timeout = 1s");
+$node_primary2->start;
+
+# Create initial data.
+$node_primary2->safe_psql('postgres',
+	"CREATE TABLE cascade_test AS SELECT generate_series(1,100) AS a");
+
+# Take backup while streaming is still enabled.
+my $backup_name2 = 'backup_arch';
+$node_primary2->backup($backup_name2);
+
+# Disable streaming on primary so middle is archive-only.
+$node_primary2->safe_psql('postgres', "ALTER SYSTEM SET max_wal_senders = 0");
+$node_primary2->reload;
+
+# Create middle standby with archive restore only.
+my $node_standby_mid = PostgreSQL::Test::Cluster->new('middle');
+$node_standby_mid->init_from_backup($node_primary2, $backup_name2,
+	has_restoring => 1);
+$node_standby_mid->append_conf('postgresql.conf', "max_wal_senders = 10");
+$node_standby_mid->append_conf('postgresql.conf', "hot_standby = on");
+$node_standby_mid->start;
+
+# Take backup of middle standby for cascading standby.
+$node_standby_mid->backup($backup_name2, backup_options => ['-Xnone']);
+
+# Create cascading standby with streaming to middle AND archive access.
+# Archive source: primary archive. Streaming source: middle standby.
+my $node_standby_cascade = PostgreSQL::Test::Cluster->new('cascade');
+$node_standby_cascade->init_from_backup($node_standby_mid, $backup_name2,
+	has_streaming => 1);
+# Enable archive restore from primary's archive (shared archive path).
+$node_standby_cascade->enable_restoring($node_primary2);
+$node_standby_cascade->start;
+
+# Wait for cascading standby to establish streaming connection.
+$node_standby_cascade->poll_query_until('postgres',
+	"SELECT EXISTS(SELECT 1 FROM pg_stat_wal_receiver)")
+  or die "Timed out waiting for cascade to connect";
+
+# Start continuous load on primary to keep archive ahead of middle.
+my $writer = $node_primary2->background_psql('postgres', on_error_stop => 0);
+$writer->query_until(
+	qr/wal_writer_ready/,
+	q(\echo wal_writer_ready
+INSERT INTO cascade_test VALUES (DEFAULT);
+\watch 0.1
+\q
+));
+
+# Let continuous load generate WAL and archive ahead of middle.
+sleep 2;
+
+# Restart upstream standby to force cascade reconnect.
+# The bug triggers when cascade tries to reconnect and requests WAL
+# ahead of upstream's flush position. We just need to check the log
+# for the error.
+note "testing upstream restart reconnection";
+my $cascade_log_offset = -s $node_standby_cascade->logfile;
+$node_standby_mid->restart;
+
+# Wait for cascade to attempt reconnection (deterministic vs sleep).
+# Either it succeeds ("started streaming") or hits the bug ("ahead of the WAL flush").
+$node_standby_cascade->wait_for_log(
+	qr/started streaming WAL from primary|requested starting point .* is ahead of the WAL flush position/,
+	$cascade_log_offset);
+
+# Stop continuous load.
+$writer->{run}->kill_kill;
+
+# The key assertion: no "ahead of WAL flush" error should appear.
+my $log_contents =
+  PostgreSQL::Test::Utils::slurp_file($node_standby_cascade->logfile,
+	$cascade_log_offset);
+my $has_error =
+  ($log_contents =~
+	  /requested starting point .* is ahead of the WAL flush position/);
+
+ok(!$has_error,
+	"no ahead-of-flush error after upstream restart (cascade reconnection)");
+
 done_testing();
-- 
2.51.0