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
Steps to reproduce use case:
1. Log in as a postgres user and setup env variables (PGDATA, PGLOG, PGPORT
etc.), also
mkdir $PGDATA/../archive_dir
2. Create data directory
initdb -k
3. Modify postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /home/postgres/data/pgpro/archive_dir/%f && cp
%p /home/postgres/data/pgpro/archive_dir/%f'
4. Start server
pg_ctl start -l $PGLOG/logfile
5. Create example database and run psql
createdb db_pitr_01
psql -d db_pitr_1
6. Create table (just to modify database)
CREATE TABLE t8x1_float4 AS SELECT random()::float4 as a1 FROM
generate_series(1, 8);
7. Switch to a new WAL file
SELECT pg_switch_wal();
8. Make a backup
pg_basebackup -Ft -X none -D - | gzip >
/home/postgres/data/pgpro/db_file_backup.tar.gz
9. Update table
INSERT INTO t8x1_float4 values (1.);
INSERT INTO t8x1_float4 values (2.);
INSERT INTO t8x1_float4 values (3.);
10. Switch to a new WAL file
SELECT pg_switch_wal();
11. Stop database and remove data directory
pg_ctl stop
rm -r /home/postgres/data/pgpro/data-debug/*
12. Restore data directory
tar xvfz db_file_backup.tar.gz -C /home/postgres/data/pgpro/data-debug/
13. Update the following parameters in postgrespro.conf
recovery_target_time = '2024-03-26 10:26:30 GMT'
restore_command = 'cp /home/postgres/data/pgpro/archive_dir/%f %p'
14.
touch recovery.signal
15.
pg_ctl start -l $PGLOG/logfile
16. Start database and see it is in Point In Time Recovery mode
psql -d db_pitr_01
db_pitr_01=# SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
db_pitr_01=# SELECT pg_is_standby_requested();
pg_is_standby_requested
-------------------------
f
(1 row)
Now we can differ recovering server in Point In Time Recovery mode from standby
mode.
pg_is_standby_requested() returns true for a replica.
From 563431ffb53d0b598f33d3378b7ba40338020ca6 Mon Sep 17 00:00:00 2001
From: Mikhail Litsarev <m.litsa...@postgrespro.ru>
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 <m.litsa...@postgrespro.ru>
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
