v4-0002-Add-wal_source-column-to-pg_stat_recovery.patch

From 2760c4e2b37a53a6d78410fd0661021d3ff1a871 Mon Sep 17 00:00:00 2001
From: alterego655 <824662526@qq.com>
Date: Tue, 27 Jan 2026 14:02:52 +0800
Subject: [PATCH v4 2/2] Add wal_source column to pg_stat_recovery

Extend pg_stat_recovery with a wal_source column that shows where the
startup process most recently read WAL data from: 'archive', 'pg_wal',
or 'stream'.

This helps diagnose recovery behavior:
- Detecting streaming vs archive fallback transitions
- Monitoring initial standby catch-up progress
- Troubleshooting replication lag sources

The column reflects the current read source, not the original delivery
mechanism. Streamed WAL that is subsequently read from local files
shows 'pg_wal'. NULL if no WAL has been read yet.
---
 doc/src/sgml/monitoring.sgml              | 36 +++++++++++++++++++++++
 src/backend/access/transam/xlogfuncs.c    | 18 ++++++++++++
 src/backend/access/transam/xlogrecovery.c |  6 ++++
 src/backend/catalog/system_views.sql      |  3 +-
 src/include/access/xlogrecovery.h         | 10 +++++++
 src/include/catalog/pg_proc.dat           |  6 ++--
 src/test/regress/expected/rules.out       |  5 ++--
 7 files changed, 78 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b3d53550688..08132f3970a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2057,6 +2057,42 @@ description | Waiting for a newly initialized WAL file to reach durable storage
       </entry>
      </row>
 
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wal_read_source</structfield> <type>text</type>
+      </para>
+      <para>
+       Source from which the startup process most recently read WAL data.
+       Possible values are:
+      </para>
+       <itemizedlist>
+        <listitem>
+         <para>
+          <literal>archive</literal>: WAL restored using
+          <varname>restore_command</varname>.
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          <literal>pg_wal</literal>: WAL read from local
+          <filename>pg_wal</filename> directory.
+         </para>
+        </listitem>
+        <listitem>
+         <para>
+          <literal>stream</literal>: WAL actively being streamed from the
+          upstream server.
+         </para>
+        </listitem>
+       </itemizedlist>
+      <para>
+       NULL if no WAL has been read yet.  Note that this reflects the
+       current read source, not the original delivery mechanism; streamed
+       WAL that is subsequently read from local files will show
+       <literal>pg_wal</literal>.
+      </para></entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 80007783067..76e9fb00df3 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -789,6 +789,7 @@ pg_stat_get_recovery(PG_FUNCTION_ARGS)
 	TimestampTz recovery_last_xact_time;
 	TimestampTz current_chunk_start_time;
 	RecoveryPauseState pause_state;
+	XLogSource	wal_read_source;
 
 	if (!RecoveryInProgress())
 		PG_RETURN_NULL();
@@ -807,6 +808,7 @@ pg_stat_get_recovery(PG_FUNCTION_ARGS)
 	recovery_last_xact_time = XLogRecoveryCtl->recoveryLastXTime;
 	current_chunk_start_time = XLogRecoveryCtl->currentChunkStartTime;
 	pause_state = XLogRecoveryCtl->recoveryPauseState;
+	wal_read_source = XLogRecoveryCtl->lastReadSource;
 	SpinLockRelease(&XLogRecoveryCtl->info_lck);
 
 	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
@@ -854,5 +856,21 @@ pg_stat_get_recovery(PG_FUNCTION_ARGS)
 
 	values[8] = CStringGetTextDatum(GetRecoveryPauseStateString(pause_state));
 
+	switch (wal_read_source)
+	{
+		case XLOG_FROM_ANY:
+			nulls[8] = true;
+			break;
+		case XLOG_FROM_ARCHIVE:
+			values[8] = CStringGetTextDatum("archive");
+			break;
+		case XLOG_FROM_PG_WAL:
+			values[8] = CStringGetTextDatum("pg_wal");
+			break;
+		case XLOG_FROM_STREAM:
+			values[8] = CStringGetTextDatum("stream");
+			break;
+	}
+
 	PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls)));
 }
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 11915ab3de0..54b2979db8b 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -397,6 +397,7 @@ XLogRecoveryShmemInit(void)
 	memset(XLogRecoveryCtl, 0, sizeof(XLogRecoveryCtlData));
 
 	SpinLockInit(&XLogRecoveryCtl->info_lck);
+	XLogRecoveryCtl->lastReadSource = XLOG_FROM_ANY;
 	InitSharedLatch(&XLogRecoveryCtl->recoveryWakeupLatch);
 	ConditionVariableInit(&XLogRecoveryCtl->recoveryNotPausedCV);
 }
@@ -4249,6 +4250,11 @@ XLogFileRead(XLogSegNo segno, TimeLineID tli,
 		if (source != XLOG_FROM_STREAM)
 			XLogReceiptTime = GetCurrentTimestamp();
 
+		/* Update shared memory for external visibility */
+		SpinLockAcquire(&XLogRecoveryCtl->info_lck);
+		XLogRecoveryCtl->lastReadSource = source;
+		SpinLockRelease(&XLogRecoveryCtl->info_lck);
+
 		return fd;
 	}
 	if (errno != ENOENT || !notfoundOk) /* unexpected failure? */
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 2eda7d80d02..6f52d54b1fa 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1008,7 +1008,8 @@ CREATE VIEW pg_stat_recovery AS
             s.replay_end_tli,
             s.recovery_last_xact_time,
             s.current_chunk_start_time,
-            s.pause_state
+            s.pause_state,
+            s.wal_read_source
     FROM pg_stat_get_recovery() s
     WHERE s.promote_triggered IS NOT NULL;
 
diff --git a/src/include/access/xlogrecovery.h b/src/include/access/xlogrecovery.h
index ba714a78280..04cffc33510 100644
--- a/src/include/access/xlogrecovery.h
+++ b/src/include/access/xlogrecovery.h
@@ -132,6 +132,16 @@ typedef struct XLogRecoveryCtlData
 	RecoveryPauseState recoveryPauseState;
 	ConditionVariable recoveryNotPausedCV;
 
+	/*
+	 * Source from which the startup process most recently read WAL data:
+	 *   XLOG_FROM_ARCHIVE - retrieved via archive recovery
+	 *   XLOG_FROM_PG_WAL  - read from pg_wal outside active streaming
+	 *   XLOG_FROM_STREAM  - opened during active walreceiver streaming
+	 *                       (I/O still reads a local pg_wal file)
+	 *   XLOG_FROM_ANY     - initial/unknown before first successful read
+	 */
+	XLogSource	lastReadSource;
+
 	slock_t		info_lck;		/* locks shared variables shown above */
 } XLogRecoveryCtlData;
 
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..ffad581e616 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5704,9 +5704,9 @@
 { 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,timestamptz,text}',
-  proargmodes => '{o,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,recovery_last_xact_time,current_chunk_start_time,pause_state}',
+  proallargtypes => '{bool,pg_lsn,pg_lsn,int4,pg_lsn,int4,timestamptz,timestamptz,text,text}',
+  proargmodes => '{o,o,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,recovery_last_xact_time,current_chunk_start_time,pause_state,wal_read_source}',
   prosrc => 'pg_stat_get_recovery' },
 { oid => '6169', descr => 'statistics: information about replication slot',
   proname => 'pg_stat_get_replication_slot', provolatile => 's',
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index deb6e2ad6a9..e15fd98ed9f 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2135,8 +2135,9 @@ pg_stat_recovery| SELECT promote_triggered,
     replay_end_tli,
     recovery_last_xact_time,
     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, recovery_last_xact_time, current_chunk_start_time, pause_state)
+    pause_state,
+    wal_read_source
+   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, recovery_last_xact_time, current_chunk_start_time, pause_state, wal_read_source)
   WHERE (promote_triggered IS NOT NULL);
 pg_stat_recovery_prefetch| SELECT stats_reset,
     prefetch,
-- 
2.51.0