v3-0001-Add-pg_stat_recovery-system-view.patch

From f23f2662f41d8456a1450c98e4dd1b50ea43ed99 Mon Sep 17 00:00:00 2001
From: alterego655 <824662526@qq.com>
Date: Tue, 27 Jan 2026 12:11:26 +0800
Subject: [PATCH v3 1/3] Add pg_stat_recovery system view

Introduce pg_stat_recovery to expose WAL recovery state maintained by the
startup process, including:
- last replayed record boundaries and timeline
- current replay end pointer and timeline
- current WAL chunk start time
- promotion trigger state
- recovery pause state

Implement pg_stat_get_recovery() as a non-SRF, following the
pg_stat_get_wal_receiver() pattern.

Gate all output behind pg_read_all_stats. If the caller lacks that role,
or recovery is not in progress, return NULL immediately before taking
XLogRecoveryCtl->info_lck.
---
 doc/src/sgml/monitoring.sgml           | 150 +++++++++++++++++++++++++
 src/backend/access/transam/xlogfuncs.c |  98 ++++++++++++++++
 src/backend/catalog/system_views.sql   |  13 +++
 src/include/catalog/pg_proc.dat        |   7 ++
 src/test/regress/expected/rules.out    |  10 ++
 src/test/regress/expected/sysviews.out |   7 ++
 src/test/regress/sql/sysviews.sql      |   3 +
 7 files changed, 288 insertions(+)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index dcf6e6a2f48..3bace7d1430 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -338,6 +338,15 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
       </entry>
      </row>
 
+     <row>
+      <entry><structname>pg_stat_recovery</structname><indexterm><primary>pg_stat_recovery</primary></indexterm></entry>
+      <entry>Only one row, showing statistics about the recovery state of the
+       startup process.  This view returns no row when not in recovery.
+       See <link linkend="monitoring-pg-stat-recovery-view">
+       <structname>pg_stat_recovery</structname></link> for details.
+      </entry>
+     </row>
+
      <row>
       <entry><structname>pg_stat_recovery_prefetch</structname><indexterm><primary>pg_stat_recovery_prefetch</primary></indexterm></entry>
       <entry>Only one row, showing statistics about blocks prefetched during recovery.
@@ -1912,6 +1921,147 @@ description | Waiting for a newly initialized WAL file to reach durable storage
 
  </sect2>
 
+ <sect2 id="monitoring-pg-stat-recovery-view">
+  <title><structname>pg_stat_recovery</structname></title>
+
+  <indexterm>
+   <primary>pg_stat_recovery</primary>
+  </indexterm>
+
+  <para>
+   The <structname>pg_stat_recovery</structname> view will contain only
+   one row, showing statistics about the recovery state of the startup
+   process. This view returns no row when the server is not in recovery.
+  </para>
+
+  <para>
+   Some columns are restricted to members of the
+   <literal>pg_read_all_stats</literal> role; other users will see
+   <literal>NULL</literal> values for these columns.  The restricted columns
+   include LSN values, timeline IDs, and timestamps.  Basic operational status
+   columns (<structfield>promote_triggered</structfield>,
+   <structfield>pause_state</structfield>) are visible to all users.
+  </para>
+
+  <table id="pg-stat-recovery-view" xreflabel="pg_stat_recovery">
+   <title><structname>pg_stat_recovery</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>promote_triggered</structfield> <type>boolean</type>
+      </para>
+      <para>
+       True if a promotion has been triggered.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_replayed_read_lsn</structfield> <type>pg_lsn</type>
+      </para>
+      <para>
+       Start write-ahead log location of the last successfully replayed
+       WAL record.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_replayed_end_lsn</structfield> <type>pg_lsn</type>
+      </para>
+      <para>
+       End write-ahead log location of the last successfully replayed
+       WAL record.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>last_replayed_tli</structfield> <type>integer</type>
+      </para>
+      <para>
+       Timeline of the last successfully replayed WAL record.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>replay_end_lsn</structfield> <type>pg_lsn</type>
+      </para>
+      <para>
+       Write-ahead log location of the record currently being replayed
+       (end position plus one).  When no record is being actively replayed,
+       equals <structfield>last_replayed_end_lsn</structfield>.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>replay_end_tli</structfield> <type>integer</type>
+      </para>
+      <para>
+       Timeline of the WAL record currently being replayed.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>current_chunk_start_time</structfield> <type>timestamp with time zone</type>
+      </para>
+      <para>
+       Time when the startup process observed that replay had caught up
+       with the latest received WAL chunk.  Used in recovery-conflict
+       timing and replay/apply-lag diagnostics.  NULL if not yet
+       available.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pause_state</structfield> <type>text</type>
+      </para>
+      <para>
+       Recovery pause state. Possible values are:
+      </para>
+       <itemizedlist>
+        <listitem>
+         <para>
+          <literal>not paused</literal>: Recovery is proceeding normally.
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          <literal>pause requested</literal>: A pause has been requested
+          but recovery has not yet paused.
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          <literal>paused</literal>: Recovery is paused.
+         </para>
+        </listitem>
+       </itemizedlist>
+      </entry>
+     </row>
+
+    </tbody>
+   </tgroup>
+  </table>
+
+ </sect2>
+
  <sect2 id="monitoring-pg-stat-recovery-prefetch">
   <title><structname>pg_stat_recovery_prefetch</structname></title>
 
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 2efe4105efb..142289d8071 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -22,10 +22,13 @@
 #include "access/xlog_internal.h"
 #include "access/xlogbackup.h"
 #include "access/xlogrecovery.h"
+#include "catalog/pg_authid.h"
 #include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "miscadmin.h"
 #include "pgstat.h"
+#include "storage/spin.h"
+#include "utils/acl.h"
 #include "replication/walreceiver.h"
 #include "storage/fd.h"
 #include "storage/latch.h"
@@ -748,3 +751,98 @@ pg_promote(PG_FUNCTION_ARGS)
 						   wait_seconds)));
 	PG_RETURN_BOOL(false);
 }
+
+/*
+ * pg_stat_get_recovery - returns information about WAL recovery state
+ *
+ * Returns NULL when not in recovery or when the caller lacks
+ * pg_read_all_stats privileges; one row otherwise.
+ */
+Datum
+pg_stat_get_recovery(PG_FUNCTION_ARGS)
+{
+	TupleDesc	tupdesc;
+	Datum	   *values;
+	bool	   *nulls;
+
+	/* Local copies of shared state */
+	bool		promote_triggered;
+	XLogRecPtr	last_replayed_read_lsn;
+	XLogRecPtr	last_replayed_end_lsn;
+	TimeLineID	last_replayed_tli;
+	XLogRecPtr	replay_end_lsn;
+	TimeLineID	replay_end_tli;
+	TimestampTz current_chunk_start_time;
+	RecoveryPauseState pause_state;
+
+	if (!RecoveryInProgress())
+		PG_RETURN_NULL();
+
+	if (!has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_STATS))
+		PG_RETURN_NULL();
+
+	/* Take a lock to ensure value consistency */
+	SpinLockAcquire(&XLogRecoveryCtl->info_lck);
+	promote_triggered = XLogRecoveryCtl->SharedPromoteIsTriggered;
+	last_replayed_read_lsn = XLogRecoveryCtl->lastReplayedReadRecPtr;
+	last_replayed_end_lsn = XLogRecoveryCtl->lastReplayedEndRecPtr;
+	last_replayed_tli = XLogRecoveryCtl->lastReplayedTLI;
+	replay_end_lsn = XLogRecoveryCtl->replayEndRecPtr;
+	replay_end_tli = XLogRecoveryCtl->replayEndTLI;
+	current_chunk_start_time = XLogRecoveryCtl->currentChunkStartTime;
+	pause_state = XLogRecoveryCtl->recoveryPauseState;
+	SpinLockRelease(&XLogRecoveryCtl->info_lck);
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	values = palloc0_array(Datum, tupdesc->natts);
+	nulls = palloc0_array(bool, tupdesc->natts);
+
+	values[0] = BoolGetDatum(promote_triggered);
+
+	if (XLogRecPtrIsValid(last_replayed_read_lsn))
+		values[1] = LSNGetDatum(last_replayed_read_lsn);
+	else
+		nulls[1] = true;
+
+	if (XLogRecPtrIsValid(last_replayed_end_lsn))
+		values[2] = LSNGetDatum(last_replayed_end_lsn);
+	else
+		nulls[2] = true;
+
+	if (XLogRecPtrIsValid(last_replayed_end_lsn))
+		values[3] = Int32GetDatum(last_replayed_tli);
+	else
+		nulls[3] = true;
+
+	if (XLogRecPtrIsValid(replay_end_lsn))
+		values[4] = LSNGetDatum(replay_end_lsn);
+	else
+		nulls[4] = true;
+
+	if (XLogRecPtrIsValid(replay_end_lsn))
+		values[5] = Int32GetDatum(replay_end_tli);
+	else
+		nulls[5] = true;
+
+	if (current_chunk_start_time != 0)
+		values[6] = TimestampTzGetDatum(current_chunk_start_time);
+	else
+		nulls[6] = true;
+
+	switch (pause_state)
+	{
+		case RECOVERY_NOT_PAUSED:
+			values[7] = CStringGetTextDatum("not paused");
+			break;
+		case RECOVERY_PAUSE_REQUESTED:
+			values[7] = CStringGetTextDatum("pause requested");
+			break;
+		case RECOVERY_PAUSED:
+			values[7] = CStringGetTextDatum("paused");
+			break;
+	}
+
+	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
+}
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 1ea8f1faa9e..3aa77558bd6 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1005,6 +1005,19 @@ CREATE VIEW pg_stat_wal_receiver AS
     FROM pg_stat_get_wal_receiver() s
     WHERE s.pid IS NOT NULL;
 
+CREATE VIEW pg_stat_recovery AS
+    SELECT
+            s.promote_triggered,
+            s.last_replayed_read_lsn,
+            s.last_replayed_end_lsn,
+            s.last_replayed_tli,
+            s.replay_end_lsn,
+            s.replay_end_tli,
+            s.current_chunk_start_time,
+            s.pause_state
+    FROM pg_stat_get_recovery() s
+    WHERE s.promote_triggered IS NOT NULL;
+
 CREATE VIEW pg_stat_recovery_prefetch AS
     SELECT
             s.stats_reset,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index dac40992cbc..514ec8dc5ad 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5698,6 +5698,13 @@
   proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{pid,status,receive_start_lsn,receive_start_tli,written_lsn,flushed_lsn,received_tli,last_msg_send_time,last_msg_receipt_time,latest_end_lsn,latest_end_time,slot_name,sender_host,sender_port,conninfo}',
   prosrc => 'pg_stat_get_wal_receiver' },
+{ oid => '9949', descr => 'statistics: information about WAL recovery',
+  proname => 'pg_stat_get_recovery', proisstrict => 'f', provolatile => 's',
+  proparallel => 'r', prorettype => 'record', proargtypes => '',
+  proallargtypes => '{bool,pg_lsn,pg_lsn,int4,pg_lsn,int4,timestamptz,text}',
+  proargmodes => '{o,o,o,o,o,o,o,o}',
+  proargnames => '{promote_triggered,last_replayed_read_lsn,last_replayed_end_lsn,last_replayed_tli,replay_end_lsn,replay_end_tli,current_chunk_start_time,pause_state}',
+  prosrc => 'pg_stat_get_recovery' },
 { oid => '6169', descr => 'statistics: information about replication slot',
   proname => 'pg_stat_get_replication_slot', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => 'text',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 78a37d9fc8f..1150dc0ebf2 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2127,6 +2127,16 @@ pg_stat_progress_vacuum| SELECT s.pid,
         END AS started_by
    FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
      LEFT JOIN pg_database d ON ((s.datid = d.oid)));
+pg_stat_recovery| SELECT promote_triggered,
+    last_replayed_read_lsn,
+    last_replayed_end_lsn,
+    last_replayed_tli,
+    replay_end_lsn,
+    replay_end_tli,
+    current_chunk_start_time,
+    pause_state
+   FROM pg_stat_get_recovery() s(promote_triggered, last_replayed_read_lsn, last_replayed_end_lsn, last_replayed_tli, replay_end_lsn, replay_end_tli, current_chunk_start_time, pause_state)
+  WHERE (s.promote_triggered IS NOT NULL);
 pg_stat_recovery_prefetch| SELECT stats_reset,
     prefetch,
     hit,
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 3dd63fd88ed..132b56a5864 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -143,6 +143,13 @@ select count(*) = 0 as ok from pg_stat_wal_receiver;
  t
 (1 row)
 
+-- We expect no recovery state in this test (running on primary)
+select count(*) = 0 as ok from pg_stat_recovery;
+ ok 
+----
+ t
+(1 row)
+
 -- This is to record the prevailing planner enable_foo settings during
 -- a regression test run.
 select name, setting from pg_settings where name like 'enable%';
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 004f9a70e00..507e400ad4a 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -76,6 +76,9 @@ select count(*) = 1 as ok from pg_stat_wal;
 -- We expect no walreceiver running in this test
 select count(*) = 0 as ok from pg_stat_wal_receiver;
 
+-- We expect no recovery state in this test (running on primary)
+select count(*) = 0 as ok from pg_stat_recovery;
+
 -- This is to record the prevailing planner enable_foo settings during
 -- a regression test run.
 select name, setting from pg_settings where name like 'enable%';
-- 
2.51.0