From: Michael Paquier <michael@paquier.xyz>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add system view pg_stat_recovery
Date: Fri, 06 Mar 2026 03:40:42 +0000
Message-ID: <E1vyM3O-002cMz-2q@gemulon.postgresql.org> (raw)
Add system view pg_stat_recovery
This commit introduces pg_stat_recovery, that exposes at SQL level the
state of recovery as tracked by XLogRecoveryCtlData in shared memory,
maintained by the startup process. This new view includes the following
fields, that are useful for monitoring purposes on a standby, once it
has reached a consistent state (making the execution of the SQL function
possible):
- Last-successfully replayed WAL record LSN boundaries and its timeline.
- Currently replaying WAL record end LSN and its timeline.
- Current WAL chunk start time.
- Promotion trigger state.
- Timestamp of latest processed commit/abort.
- Recovery pause state.
Some of this data can already be recovered from different system
functions, but not all of it. See pg_get_wal_replay_pause_state or
pg_last_xact_replay_timestamp. This new view offers a stronger
consistency guarantee, by grabbing the recovery state for all fields
through one spinlock acquisition.
The system view relies on a new function, called pg_stat_get_recovery().
Querying this data requires the pg_read_all_stats privilege. The view
returns no rows if the node is not in recovery.
This feature originates from a suggestion I have made while discussion
the addition of a CONNECTING state to the WAL receiver's shared memory
state, because we lacked access to some of the state data. The author
has taken the time to implement it, so thanks for that.
Bump catalog version.
Author: Xuneng Zhou <xunengzhou@gmail.com>
Discussion: https://postgr.es/m/CABPTF7W+Nody-+P9y4PNk37-QWuLpfUrEonHuEhrX+Vx9Kq+Kw@mail.gmail.com
Discussion: https://postgr.es/m/aW13GJn_RfTJIFCa@paquier.xyz
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/01d485b142e4c89aef79da9fd60f203c1bd4458b
Modified Files
--------------
doc/src/sgml/monitoring.sgml | 151 +++++++++++++++++++++++++++++++++
src/backend/access/transam/xlogfuncs.c | 94 ++++++++++++++++++++
src/backend/catalog/system_views.sql | 14 +++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 7 ++
src/test/recovery/t/001_stream_rep.pl | 5 ++
src/test/regress/expected/rules.out | 11 +++
src/test/regress/expected/sysviews.out | 7 ++
src/test/regress/sql/sysviews.sql | 3 +
9 files changed, 293 insertions(+), 1 deletion(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: michael@paquier.xyz, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Add system view pg_stat_recovery
In-Reply-To: <E1vyM3O-002cMz-2q@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox