v1-0004-Add-wal_source-column-to-pg_stat_recovery.patch

From bc93177ca524c1a2e0e67b2697143fc1ffe326d5 Mon Sep 17 00:00:00 2001
From: alterego655 <824662526@qq.com>
Date: Tue, 27 Jan 2026 14:02:52 +0800
Subject: [PATCH v1 4/4] 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    | 21 ++++++++++++-
 src/backend/access/transam/xlogrecovery.c |  6 ++++
 src/backend/catalog/system_views.sql      |  3 +-
 src/include/access/xlogrecovery.h         |  8 +++++
 src/include/catalog/pg_proc.dat           |  6 ++--
 src/test/regress/expected/rules.out       |  5 ++--
 7 files changed, 78 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index ac2cb309976..09e341639e9 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -2062,6 +2062,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_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 330bf00f6d9..1117a46a48e 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -765,7 +765,7 @@ pg_promote(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_recovery(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_RECOVERY_COLS 9
+#define PG_STAT_GET_RECOVERY_COLS 10
 	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
 	Datum		values[PG_STAT_GET_RECOVERY_COLS];
 	bool		nulls[PG_STAT_GET_RECOVERY_COLS];
@@ -781,6 +781,7 @@ pg_stat_get_recovery(PG_FUNCTION_ARGS)
 	TimestampTz recovery_last_xact_time;
 	TimestampTz current_chunk_start_time;
 	RecoveryPauseState pause_state;
+	XLogSource	wal_source;
 
 	InitMaterializedSRF(fcinfo, 0);
 
@@ -805,6 +806,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_source = XLogRecoveryCtl->lastReadSource;
 	SpinLockRelease(&XLogRecoveryCtl->info_lck);
 
 	/* Initialize nulls array */
@@ -887,6 +889,23 @@ pg_stat_get_recovery(PG_FUNCTION_ARGS)
 			break;
 	}
 
+	/* wal_source - always visible */
+	switch (wal_source)
+	{
+		case XLOG_FROM_ANY:
+			nulls[9] = true;	/* not yet determined */
+			break;
+		case XLOG_FROM_ARCHIVE:
+			values[9] = CStringGetTextDatum("archive");
+			break;
+		case XLOG_FROM_PG_WAL:
+			values[9] = CStringGetTextDatum("pg_wal");
+			break;
+		case XLOG_FROM_STREAM:
+			values[9] = CStringGetTextDatum("stream");
+			break;
+	}
+
 	tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
 
 	return (Datum) 0;
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 9fdf143d18f..af86eece08c 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);
 }
@@ -4246,6 +4247,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 a88cac599c4..36182cb5057 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -994,7 +994,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_source
     FROM pg_stat_get_recovery() s;
 
 CREATE VIEW pg_stat_recovery_prefetch AS
diff --git a/src/include/access/xlogrecovery.h b/src/include/access/xlogrecovery.h
index 514595f0ee6..f18922271a1 100644
--- a/src/include/access/xlogrecovery.h
+++ b/src/include/access/xlogrecovery.h
@@ -133,6 +133,14 @@ typedef struct XLogRecoveryCtlData
 	RecoveryPauseState recoveryPauseState;
 	ConditionVariable recoveryNotPausedCV;
 
+	/*
+	 * Source from which the startup process most recently read WAL data.
+	 * Updated when the startup process successfully reads WAL from a source.
+	 * Note: this reflects the read source, not the original receipt source;
+	 * streamed WAL read from local files will show XLOG_FROM_PG_WAL.
+	 */
+	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 3b1569d8a3f..d86de7901da 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5701,9 +5701,9 @@
   proname => 'pg_stat_get_recovery', prorows => '1', proretset => 't',
   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_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 7f3499291b5..81f11329b6f 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_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_source);
 pg_stat_recovery_prefetch| SELECT stats_reset,
     prefetch,
     hit,
-- 
2.51.0