pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedImprove pg_stat_recovery documentation
3+ messages / 2 participants
[nested] [flat]
* Improve pg_stat_recovery documentation
@ 2026-07-17 14:10 Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Fujii Masao @ 2026-07-17 14:10 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Hi,
While trying the new pg_stat_recovery view in v19, I found that it can return
no rows even while recovery is in progress. This happened because the user
querying the view lacked the pg_read_all_stats privilege.
That behavior seems fine to me, but it isn't documented. So I'd like to propose
the attached patch, which documents that behavior and also makes a few other
documentation improvements:
- Mention the view in high-availability.sgml as a way to monitor
recovery state and replay progress, alongside the existing recovery
information functions.
- Clarify that the view returns at most one row, not exactly one row,
and no rows to users who lack the pg_read_all_stats privilege.
- Correct the description of last_replayed_end_lsn to clarify that it
is the end LSN of the last replayed record plus one.
- Document that replay_end_tli equals last_replayed_tli when no WAL
record is currently being replayed.
- Clarify that current_chunk_start_time is NULL until streaming WAL
has been received.
Thought?
Regards,
--
Fujii Masao
Attachments:
[application/octet-stream] v1-0001-doc-Improve-pg_stat_recovery-documentation.patch (7.2K, ../../CAHGQGwGRavm18HqnQn_f68QB96qk6arhjET1V93OJH09Mgojkg@mail.gmail.com/2-v1-0001-doc-Improve-pg_stat_recovery-documentation.patch)
download | inline diff:
From 531f499508670b4690aa19b9ced12969e7a8f3f4 Mon Sep 17 00:00:00 2001
From: Fujii Masao <fujii@postgresql.org>
Date: Fri, 17 Jul 2026 21:58:16 +0900
Subject: [PATCH v1] doc: Improve pg_stat_recovery documentation
Improve the documentation for pg_stat_recovery in several ways:
- Mention the view in high-availability.sgml as a way to monitor
recovery state and replay progress, alongside the existing recovery
information functions.
- Clarify that the view returns at most one row, not exactly one row,
and no rows to users who lack the pg_read_all_stats privilege.
- Correct the description of last_replayed_end_lsn to clarify that it
is the end LSN of the last replayed record plus one.
- Document that replay_end_tli equals last_replayed_tli when no WAL
record is currently being replayed.
- Clarify that current_chunk_start_time is NULL until streaming WAL
has been received.
---
doc/src/sgml/high-availability.sgml | 13 ++++++++----
doc/src/sgml/monitoring.sgml | 33 +++++++++++++++++------------
src/include/access/xlogrecovery.h | 4 ++--
3 files changed, 30 insertions(+), 20 deletions(-)
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml
index 6d9636bd125..fd338ab1540 100644
--- a/doc/src/sgml/high-availability.sgml
+++ b/doc/src/sgml/high-availability.sgml
@@ -920,7 +920,10 @@ primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
<structname>pg_stat_wal_receiver</structname></link> view. A large
difference between <function>pg_last_wal_replay_lsn</function> and the
view's <literal>flushed_lsn</literal> indicates that WAL is being
- received faster than it can be replayed.
+ received faster than it can be replayed. Recovery state and replay
+ progress can also be monitored via the
+ <link linkend="monitoring-pg-stat-recovery-view">
+ <structname>pg_stat_recovery</structname></link> view.
</para>
</sect3>
</sect2>
@@ -1801,9 +1804,11 @@ postgres=# WAIT FOR LSN '0/306EE20';
(In server versions before 14, the <varname>in_hot_standby</varname>
parameter did not exist; a workable substitute method for older servers
is <command>SHOW transaction_read_only</command>.) In addition, a set of
- functions (<xref linkend="functions-recovery-info-table"/>) allow users to
- access information about the standby server. These allow you to write
- programs that are aware of the current state of the database. These
+ functions (<xref linkend="functions-recovery-info-table"/>) and the
+ <link linkend="monitoring-pg-stat-recovery-view">
+ <structname>pg_stat_recovery</structname></link> view allow users to
+ access information about the standby server. These facilities allow you to
+ write programs that are aware of the current state of the database. They
can be used to monitor the progress of recovery, or to allow you to
write complex programs that restore the database to particular states.
</para>
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d1a20d001e9..802cbe1d8e4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -340,7 +340,7 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
<row>
<entry><structname>pg_stat_recovery</structname><indexterm><primary>pg_stat_recovery</primary></indexterm></entry>
- <entry>Only one row, showing statistics about the state of recovery.
+ <entry>At most one row, showing statistics about the recovery state.
See <link linkend="monitoring-pg-stat-recovery-view">
<structname>pg_stat_recovery</structname></link> for details.
</entry>
@@ -1975,9 +1975,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</indexterm>
<para>
- The <structname>pg_stat_recovery</structname> view will contain only
+ The <structname>pg_stat_recovery</structname> view will contain at most
one row, showing statistics about the recovery state of the startup
- process. This view returns no row when the server is not in recovery.
+ process. This view returns no rows when the server is not in recovery
+ or the user does not have privileges of the
+ <literal>pg_read_all_stats</literal> role.
</para>
<table id="pg-stat-recovery-view" xreflabel="pg_stat_recovery">
@@ -2019,8 +2021,8 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structfield>last_replayed_end_lsn</structfield> <type>pg_lsn</type>
</para>
<para>
- End write-ahead log location of the last successfully replayed
- WAL record.
+ End write-ahead log location, plus one, of the last successfully
+ replayed WAL record.
</para></entry>
</row>
@@ -2049,18 +2051,20 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<structfield>replay_end_tli</structfield> <type>integer</type>
</para>
<para>
- Timeline of the WAL record currently being replayed.
+ Timeline of the WAL record currently being replayed. When no record
+ is being actively replayed, equals
+ <structfield>last_replayed_tli</structfield>.
</para></entry>
</row>
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>recovery_last_xact_time</structfield> <type>timestamp with time zone</type>
- </para>
- <para>
- Timestamp of the last transaction commit or abort replayed during
- recovery. This is the time at which the commit or abort WAL record
- for that transaction was generated on the primary.
+ <structfield>recovery_last_xact_time</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Timestamp of the last transaction commit or abort record replayed
+ during recovery. This is the time at which the commit or abort WAL
+ record for that transaction was generated on the primary.
</para></entry>
</row>
@@ -2070,8 +2074,9 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</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
+ with the latest WAL chunk received from streaming replication.
+ Used in recovery-conflict timing and replay/apply-lag diagnostics.
+ NULL if streaming WAL has not yet been received or the time is not
available.
</para></entry>
</row>
diff --git a/src/include/access/xlogrecovery.h b/src/include/access/xlogrecovery.h
index ba7750dca0b..61877f9c45c 100644
--- a/src/include/access/xlogrecovery.h
+++ b/src/include/access/xlogrecovery.h
@@ -112,8 +112,8 @@ typedef struct XLogRecoveryCtlData
TimestampTz recoveryLastXTime;
/*
- * timestamp of when we started replaying the current chunk of WAL data,
- * only relevant for replication or archive recovery
+ * timestamp of when we caught up with the latest WAL chunk received from
+ * streaming replication
*/
TimestampTz currentChunkStartTime;
/* Recovery pause state */
--
2.55.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Improve pg_stat_recovery documentation
@ 2026-07-18 00:39 Michael Paquier <michael@paquier.xyz>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Paquier @ 2026-07-18 00:39 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Fri, Jul 17, 2026 at 11:10:08PM +0900, Fujii Masao wrote:
> That behavior seems fine to me, but it isn't documented. So I'd like to propose
> the attached patch, which documents that behavior and also makes a few other
> documentation improvements:
>
> - Mention the view in high-availability.sgml as a way to monitor
> recovery state and replay progress, alongside the existing recovery
> information functions.
Why not.
> - Clarify that the view returns at most one row, not exactly one row,
> and no rows to users who lack the pg_read_all_stats privilege.
Yep, that's incorrect.
> - Correct the description of last_replayed_end_lsn to clarify that it
> is the end LSN of the last replayed record plus one.
> - Document that replay_end_tli equals last_replayed_tli when no WAL
> record is currently being replayed.
Okay.
> - Clarify that current_chunk_start_time is NULL until streaming WAL
> has been received.
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
+ with the latest WAL chunk received from streaming replication.
+ Used in recovery-conflict timing and replay/apply-lag diagnostics.
+ NULL if streaming WAL has not yet been received or the time is not
available.
[...]
- * timestamp of when we started replaying the current chunk of WAL data,
- * only relevant for replication or archive recovery
+ * timestamp of when we caught up with the latest WAL chunk received from
+ * streaming replication
*/
True that this does not work under archive recovery, so specifying
that only only gets updated due to streaming replication sounds good
here.
Looks good to me overall after reading the patch, thanks for the
suggestions.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (832B, ../../alrLPjr0oG0bIrlY@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Improve pg_stat_recovery documentation
@ 2026-07-23 10:28 Fujii Masao <masao.fujii@gmail.com>
parent: Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 3+ messages in thread
From: Fujii Masao @ 2026-07-23 10:28 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Sat, Jul 18, 2026 at 9:39 AM Michael Paquier <michael@paquier.xyz> wrote:
> Looks good to me overall after reading the patch, thanks for the
> suggestions.
Thanks for the review! I've pushed the patch.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-07-23 10:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-17 14:10 Improve pg_stat_recovery documentation Fujii Masao <masao.fujii@gmail.com>
2026-07-18 00:39 ` Michael Paquier <michael@paquier.xyz>
2026-07-23 10:28 ` Fujii Masao <masao.fujii@gmail.com>
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