Add pg_stat_recovery system view

From: Xuneng Zhou <xunengzhou(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>
Subject: Add pg_stat_recovery system view
Date: 2026-01-27 07:23:50
Message-ID: CABPTF7W+Nody-+P9y4PNk37-QWuLpfUrEonHuEhrX+Vx9Kq+Kw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Hackers,

This patch series introduces pg_stat_recovery, a new view that exposes
the startup process’s internal recovery state at the SQL level. It
follows the pattern of pg_stat_wal_receiver, but focuses on WAL replay
and recovery rather than WAL reception.

The view provides visibility into replay progress, recovery timing,
and operational status—information that was previously scattered
across separate function calls or not exposed at all.

As Michael suggested [1][2], this view is intentionally independent of
walreceiver state, since the startup process can consume WAL from
multiple sources (archive, pg_wal, or streaming), not just streaming
replication.

The patch series:
0001: Refactor: move XLogRecoveryCtlData struct to xlogrecovery.h
Move the XLogRecoveryCtlData struct definition from xlogrecovery.c to
xlogrecovery.h,

0002: Add pg_stat_recovery system view
Introduces the core view with columns

0003: Refactor: move XLogSource enum to xlogrecovery.h
Preparatory refactoring to make XLogSource visible externally.

0004: Add wal_source column to pg_stat_recovery
Adds wal_source column showing where WAL was last read from:
'archive', 'pg_wal', or 'stream'.

Example usage on a standby:

SELECT promote_triggered, pause_state, wal_source,
pg_size_pretty(pg_wal_lsn_diff(replay_end_lsn, last_replayed_end_lsn))
FROM pg_stat_recovery;

promote_triggered | pause_state | wal_source | pg_size_pretty
-------------------+-------------+------------+----------------
f | not paused | stream | 0 bytes

Feedbacks welcome.

[1] https://www.postgresql.org/message-id/aW13GJn_RfTJIFCa@paquier.xyz
[2] https://www.postgresql.org/message-id/aW68b79-9U3WPZiz@paquier.xyz

--
Best,
Xuneng

Attachment Content-Type Size
v1-0002-Add-pg_stat_recovery-system-view.patch application/octet-stream 17.0 KB
v1-0003-Refactor-move-XLogSource-enum-to-xlogrecovery.h.patch application/octet-stream 2.4 KB
v1-0004-Add-wal_source-column-to-pg_stat_recovery.patch application/octet-stream 8.7 KB
v1-0001-Refactor-move-XLogRecoveryCtlData-struct-to-xlogr.patch application/octet-stream 6.4 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey Borodin 2026-01-27 07:26:01 Re: Fix gistkillitems & add regression test to microvacuum
Previous Message ocean_li_996 2026-01-27 07:18:58 Re: [BUG] Incorrect historic snapshot may be serialized to disk during fast-forwarding