Subject: SQL function which allows to distinguish a server being in point in time recovery mode and an ordinary replica

From: [email-masked]
Date: Tue, 26 Mar 2024 17:28:01 +0300

Hi,

At present time, an existing pg_is_in_recovery() method is not enough
to distinguish a server being in point in time recovery (PITR) mode and 
an ordinary replica
because it returns true in both cases.

That is why pg_is_standby_requested() function introduced in attached 
patch might help.
It reports whether a standby.signal file was found in the data directory 
at startup process.
Instructions for reproducing the possible use case are also attached.

Hope it will be usefull.

Respectfully,

Mikhail Litsarev
Postgres Professional: https://postgrespro.com

From 563431ffb53d0b598f33d3378b7ba40338020ca6 Mon Sep 17 00:00:00 2001
From: Mikhail Litsarev <[email-masked]>
Date: Tue, 20 Feb 2024 20:05:37 +0300
Subject: [PATCH 1/2] Introduce pg_is_standby_requested().

Introduce pg_is_standby_requested() function to distinguish
a replica from a regular instance in point in time recovery mode.
---
 src/backend/access/transam/xlogfuncs.c    | 14 ++++++++++
 src/backend/access/transam/xlogrecovery.c | 33 +++++++++++++++++++++++
 src/include/access/xlogrecovery.h         |  1 +
 src/include/catalog/pg_proc.dat           |  4 +++
 4 files changed, 52 insertions(+)

diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 45452d937c7..3170c4ef343 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -753,3 +753,17 @@ pg_promote(PG_FUNCTION_ARGS)
 						   wait_seconds)));
 	PG_RETURN_BOOL(false);
 }
+
+/*
+ * Returns bool with a current value of StandbyModeIsRequested flag
+ * to distinguish a replica from a regular instance in a
+ * Point In Time Recovery (PITR) mode.
+ *
+ * Returns	true	if standby.signal file is found at startup process
+ * 			false	otherwise
+ */
+Datum
+pg_is_standby_requested(PG_FUNCTION_ARGS)
+{
+	PG_RETURN_BOOL(StandbyModeIsRequested());
+}
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 6318878356a..cbca4c5055d 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -321,6 +321,12 @@ typedef struct XLogRecoveryCtlData
 	 */
 	bool		SharedPromoteIsTriggered;
 
+	/*
+	 * SharedStandbyModeRequested indicates if we're in a standby mode at
+	 * start, while recovery mode is on. Protected by info_lck.
+	 */
+	bool		SharedStandbyModeRequested;
+
 	/*
 	 * recoveryWakeupLatch is used to wake up the startup process to continue
 	 * WAL replay, if it is waiting for WAL to arrive or promotion to be
@@ -1070,7 +1076,16 @@ readRecoverySignalFile(void)
 		ArchiveRecoveryRequested = true;
 	}
 	else
+	{
+		/*
+		 * There is no need to use Spinlock here because only the startup
+		 * process modifies the SharedStandbyModeRequested variable here and
+		 * no other processes are reading it at that time.
+		 */
+		XLogRecoveryCtl->SharedStandbyModeRequested = StandbyModeRequested;
 		return;
+	}
+	XLogRecoveryCtl->SharedStandbyModeRequested = StandbyModeRequested;
 
 	/*
 	 * We don't support standby mode in standalone backends; that requires
@@ -4555,6 +4570,24 @@ HotStandbyActiveInReplay(void)
 	return LocalHotStandbyActive;
 }
 
+
+/*
+ * It reports wether a standby.signal file is in the data directory
+ * at startup.
+ *
+ * This works in any process that's connected to shared memory.
+ */
+bool
+StandbyModeIsRequested(void)
+{
+	/*
+	 * Spinlock is not needed here because SharedStandbyModeRequested variable
+	 * can only be read after startup process is done and no other actions (in
+	 * functions, in processes) change it then.
+	 */
+	return XLogRecoveryCtl->SharedStandbyModeRequested;
+}
+
 /*
  * Get latest redo apply position.
  *
diff --git a/src/include/access/xlogrecovery.h b/src/include/access/xlogrecovery.h
index 66affd2eac3..f85a2af6a23 100644
--- a/src/include/access/xlogrecovery.h
+++ b/src/include/access/xlogrecovery.h
@@ -145,6 +145,7 @@ extern TimestampTz GetLatestXTime(void);
 extern TimestampTz GetCurrentChunkReplayStartTime(void);
 extern XLogRecPtr GetCurrentReplayRecPtr(TimeLineID *replayEndTLI);
 
+extern bool StandbyModeIsRequested(void);
 extern bool PromoteIsTriggered(void);
 extern bool CheckPromoteSignal(void);
 extern void WakeupRecovery(void);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index bb775ce7264..a2ac4160d8c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6494,6 +6494,10 @@
 { oid => '3810', descr => 'true if server is in recovery',
   proname => 'pg_is_in_recovery', provolatile => 'v', prorettype => 'bool',
   proargtypes => '', prosrc => 'pg_is_in_recovery' },
+{ oid => '8439',
+  descr => 'Is StandbyMode requested at startup while being in recovery mode',
+  proname => 'pg_is_standby_requested', provolatile => 'v', prorettype => 'bool',
+  proargtypes => '', prosrc => 'pg_is_standby_requested' },
 
 { oid => '3820', descr => 'current wal flush location',
   proname => 'pg_last_wal_receive_lsn', provolatile => 'v',
-- 
2.34.1

From 3d944423ed1c0f2a73e4a38a61ac545f0450a70d Mon Sep 17 00:00:00 2001
From: Mikhail Litsarev <[email-masked]>
Date: Tue, 27 Feb 2024 14:08:42 +0300
Subject: [PATCH 2/2] Test pg_is_standby_requested() in different use-cases.

Add tiny tests in src/test/recovery/t/004_timeline_switch.pl
They validate:
 - master is not a replica
 - standby_1 is a replica
 - promoted replica in master mode
 - standby_2 remains a replica after switch to promoted master
---
 src/test/recovery/t/004_timeline_switch.pl | 23 ++++++++++++++++++++++
 1 file changed, 23 insertions(+)

diff --git a/src/test/recovery/t/004_timeline_switch.pl b/src/test/recovery/t/004_timeline_switch.pl
index edaef918454..e3f320ab9ab 100644
--- a/src/test/recovery/t/004_timeline_switch.pl
+++ b/src/test/recovery/t/004_timeline_switch.pl
@@ -18,6 +18,11 @@ my $node_primary = PostgreSQL::Test::Cluster->new('primary');
 $node_primary->init(allows_streaming => 1);
 $node_primary->start;
 
+# Validate pg_is_standby_requested() for master
+my $ret_mode_primary = $node_primary->safe_psql('postgres',
+	'SELECT pg_is_standby_requested()');
+is($ret_mode_primary, 'f', "master is not a replica");
+
 # Take backup
 my $backup_name = 'my_backup';
 $node_primary->backup($backup_name);
@@ -32,6 +37,11 @@ $node_standby_2->init_from_backup($node_primary, $backup_name,
 	has_streaming => 1);
 $node_standby_2->start;
 
+# Validate pg_is_standby_requested() for replica node
+my $ret_mode_standby_1 = $node_standby_1->safe_psql('postgres',
+	'SELECT pg_is_standby_requested()');
+is($ret_mode_standby_1, 't', "node_standby_1 is a replica");
+
 # Create some content on primary
 $node_primary->safe_psql('postgres',
 	"CREATE TABLE tab_int AS SELECT generate_series(1,1000) AS a");
@@ -50,6 +60,14 @@ $node_standby_1->psql(
 	stdout => \$psql_out);
 is($psql_out, 't', "promotion of standby with pg_promote");
 
+# Validate pg_is_standby_requested() for master promoted from standby node.
+# pg_is_standby_requested() returns true because standby.signal file
+# was found while being a replica.
+# Use it with pg_is_in_recovery() (returns false), for such use-cases.
+my $ret_mode_1 = $node_standby_1->safe_psql('postgres',
+	'SELECT pg_is_standby_requested()');
+is($ret_mode_1, 't', "node_standby_1 becomes a master");
+
 # Switch standby 2 to replay from standby 1
 my $connstr_1 = $node_standby_1->connstr;
 $node_standby_2->append_conf(
@@ -58,6 +76,11 @@ primary_conninfo='$connstr_1'
 ));
 $node_standby_2->restart;
 
+# Validate pg_is_standby_requested() for second replica after restart
+my $ret_mode_standby_2 = $node_standby_2->safe_psql('postgres',
+	'SELECT pg_is_standby_requested()');
+is($ret_mode_standby_2, 't', "node_standby_2 remains a replica");
+
 # Insert some data in standby 1 and check its presence in standby 2
 # to ensure that the timeline switch has been done.
 $node_standby_1->safe_psql('postgres',
-- 
2.34.1

---

From: "Tristan Partin" <[email-masked]>
Date: Mon, 15 Apr 2024 16:06:03 -0500

> Instructions for reproducing the possible use case are also attached.

Hey Mikhail,

Saw your patch for the first time today. Looks like your patch is messed 
up? You seem to have more of the diff at the bottom which seems to add 
a test. Want to send a v2 with a properly formatted patch?

Example command:

git format-patch -v2 -M HEAD^

-- 
Tristan Partin
Neon (https://neon.tech)