pg_is_in_recovery — Monitor PostgreSQL Standby Status

Monitor PostgreSQL Recovery Status with pg_is_in_recovery

Is this server a standby, and is it still applying WAL? One SELECT answers both questions. It calls pg_is_in_recovery() and the functions that report how far a standby has received and replayed the write-ahead log (WAL), and it adds the two legacy exclusive-backup functions for servers that still have them.

Purpose and Overview

A PostgreSQL server in recovery is replaying WAL instead of generating it. That is the normal state of a streaming-replication standby. It is also the state of a server restoring from a physical backup plus archived WAL, as in point-in-time recovery. In both cases a DBA needs the same few facts: is recovery still in progress, has replay been paused, how much WAL has arrived, and how much of it has been applied.

The WAL documentation describes the idea behind all of this: changes to data files must be written only after those changes have been logged, so after a crash the database can be recovered from the log by redoing any changes that were not yet applied to the data pages. It also says that by archiving WAL you can install a prior physical backup and replay the WAL just as far as the time you want. Every function in this script reports on part of that replay.

The query returns one row. Three columns are always populated: backup status, recovery status and backup start time. Four more are populated only when the server is in recovery, and are NULL on a primary. That makes it safe to run the same statement on every server in a fleet and read the result by role.

Sample Code

 1SELECT
 2  pg_is_in_backup(),
 3  pg_is_in_recovery(),
 4  pg_backup_start_time(),
 5  -- the following recovery control functions can only be executed during recovery - to get just the above use postgres_funcs.sql
 6  ( CASE WHEN pg_is_in_recovery() THEN pg_is_wal_replay_paused() END)       AS "pg_is_wal_replay_paused()",
 7  ( CASE WHEN pg_is_in_recovery() THEN pg_last_wal_receive_lsn() END)       AS "pg_last_wal_receive_lsn()",
 8  ( CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() END)        AS "pg_last_wal_replay_lsn()",
 9  ( CASE WHEN pg_is_in_recovery() THEN pg_last_xact_replay_timestamp() END) AS "pg_last_xact_replay_timestamp()"
10;

Notes: The query works as written on PostgreSQL 10 through 14. PostgreSQL 10 renamed the functions that referenced "xlog" to "wal", which is why the names here use wal. PostgreSQL 15 removed pg_is_in_backup() and pg_backup_start_time(), so on 15 and later the query fails until you delete those two lines. The SQL is unchanged from the original script.

Code Breakdown

The query is a list of seven function calls. The first three are plain; the last four are wrapped in CASE.

The backup functions

pg_is_in_backup() returns true if an on-line exclusive backup is in progress. pg_backup_start_time() returns the start time of the current on-line exclusive backup if one is in progress, and NULL otherwise. The word "exclusive" matters. These two functions describe only the older exclusive backup mode, so a backup taken with the non-exclusive API does not show up in them.

pg_is_in_recovery()

This returns true if recovery is still in progress. The documentation lists it with the recovery information functions, which "may be executed both during recovery and in normal running". On a primary it simply returns false, so it is a safe guard for the other calls.

The CASE-wrapped recovery columns

Each of the last four columns repeats CASE WHEN pg_is_in_recovery() THEN ... END. When the server is not in recovery, the condition is false, there is no ELSE, and the column is NULL.

The comment in the script says these functions can only be executed during recovery. The documentation draws a finer line. pg_is_wal_replay_paused() is in the recovery control group, and that group "may be executed only during recovery", so the guard is required for that column. The other three are recovery information functions that run in normal operation too. According to the documentation, pg_last_wal_replay_lsn() and pg_last_xact_replay_timestamp() return NULL when the server was started normally without recovery, and pg_last_wal_receive_lsn() returns NULL if streaming replication is disabled or has not yet started. For those three the CASE is harmless and keeps the output uniform, but it is not what prevents an error.

Key Recovery Values

Received versus replayed LSN

pg_last_wal_receive_lsn() returns the last WAL location that has been received and synced to disk by streaming replication. While streaming is in progress it increases monotonically. If recovery has completed, it stays at the location of the last WAL record received and synced during recovery.

pg_last_wal_replay_lsn() returns the last WAL location that has been replayed during recovery. It also increases monotonically while recovery is in progress and stays static once recovery completes. The two values answer different questions: the receive position says how much WAL the standby has on disk, and the replay position says how much of it has been applied to the data files.

What an LSN is

An LSN (Log Sequence Number) is stored in the pg_lsn type. The documentation describes it as a pointer to a location in the WAL, internally a 64-bit integer representing a byte position in the WAL stream, printed as two hexadecimal numbers separated by a slash, such as 16/B374D848. Two LSNs can be subtracted with the - operator, and the result is the number of bytes separating those WAL locations. That is how you turn the two LSN columns into a byte distance.

Last replayed transaction time

pg_last_xact_replay_timestamp() returns the time stamp of the last transaction replayed during recovery. The documentation specifies this as the time at which the commit or abort WAL record for that transaction was generated on the primary. If no transactions have been replayed, it returns NULL. Because it is a primary-side time, comparing it with the current time on the standby gives a time-based view of how far behind replay is. Because it is the time of the last replayed transaction, it can trail the clock when the primary has had no recent transactions, even if replay is current.

Pause requested versus paused

pg_is_wal_replay_paused() returns true if a recovery pause is requested. The documentation is explicit that a request does not mean recovery stops right away. To learn whether recovery is actually paused, use pg_get_wal_replay_pause_state(), which returns not paused, pause requested or paused. This function is documented in the PostgreSQL 14 and current pages, but it is not part of this script, so a true in the column means "a pause was asked for", not "replay has stopped".

Practical Applications

The query is most useful as a quick triage step. It tells you which role a server has and whether its recovery looks active before you look at anything more detailed.

Confirming a server's role

Run the query after a failover or a restore. If pg_is_in_recovery() is true, the server is still replaying WAL. If it is false, the four recovery columns are NULL and the server is accepting writes as a primary. This is a cleaner check than inferring the role from which host name you connected to. For a per-session check on a hot standby, PostgreSQL 14 and later also let you run SHOW in_hot_standby.

Checking that replay is moving

Run the query twice and compare. On a healthy standby in recovery, both LSN columns should increase while the primary is generating WAL, because the documentation says both increase monotonically while recovery is in progress. If the receive LSN advances but the replay LSN does not, WAL is arriving and not being applied. Check pg_is_wal_replay_paused() first, then call pg_get_wal_replay_pause_state() to see whether a pause is only requested or already in effect.

Watching a point-in-time restore

When you restore a physical backup and replay archived WAL to a target time, the same columns show progress. The replay LSN and the last replayed transaction time rise as the server works through the archive. When recovery finishes, pg_is_in_recovery() becomes false and the LSNs stay static at the last positions reached.

Spotting an exclusive backup left running

On PostgreSQL 14 and earlier, a true pg_is_in_backup() together with an old pg_backup_start_time() tells you an exclusive backup was started and not stopped. The PostgreSQL 15 release notes explain why this mode was removed: if the server stops abruptly while in exclusive backup mode, it could fail to start.

Version Compatibility

PostgreSQL 10 renamed SQL functions, tools and options that referenced "xlog" to "wal". For example, pg_switch_xlog() became pg_switch_wal(). The release notes add that the "xlog" terminology is no longer used in any user-facing places. The script requires PostgreSQL 10 or later for that reason. The original script header says it was tested on 10.x through 13.0; the functions it uses also appear in the PostgreSQL 14 documentation.

PostgreSQL 15 removed the long-deprecated exclusive backup mode. pg_start_backup() and pg_stop_backup() were renamed to pg_backup_start() and pg_backup_stop(), and pg_backup_start_time() and pg_is_in_backup() were removed. On 15 and later, delete the first and third lines of the select list. Everything else in the query still works, and the current documentation still lists pg_is_in_recovery(), pg_is_wal_replay_paused() and pg_get_wal_replay_pause_state().

On PostgreSQL 14 and later you can also use SHOW in_hot_standby for a per-session answer. On versions before 14 the parameter does not exist, and the documentation suggests SHOW transaction_read_only as a substitute on older servers.

Best Practices

• Read the role first — check pg_is_in_recovery() before trusting any of the recovery columns. A NULL in a recovery column on a primary is expected, not a fault.
• Treat the pause column as a request — pg_is_wal_replay_paused() reports that a pause was requested. Use pg_get_wal_replay_pause_state() when you need to know that replay has actually stopped.
• Compare two samples — one reading shows a position, two readings show movement. Both LSN values increase monotonically while recovery is in progress, so a flat value between samples needs an explanation.
• Subtract LSNs for a distance — use the - operator on pg_lsn values to get a byte difference between the receive and replay positions, rather than comparing the printed strings.
• Do not rely on the backup columns going forward — they cover only exclusive-mode backups and are gone from PostgreSQL 15. Remove them from monitoring queries when you upgrade.
• Read the timestamp as a transaction time — the last replayed transaction time is a primary-side timestamp, so it can trail the clock when the primary has had no recent transactions.

References

• PostgreSQL 14: System Administration Functions — the backup control, recovery information and recovery control function tables, including pg_is_in_backup(), pg_is_in_recovery() and pg_is_wal_replay_paused().
• PostgreSQL 15 release notes — the removal of exclusive backup mode and of pg_is_in_backup() and pg_backup_start_time().
• PostgreSQL: Hot Standby — what a hot standby is and how SHOW in_hot_standby reports it.
• PostgreSQL: Write-Ahead Logging (WAL) — the log-before-data rule and WAL replay after a crash or from a physical backup.
• HariSekhon/SQL-scripts: postgres_recovery.sql — original seed script.
• HariSekhon/SQL-scripts LICENSE (MIT) — Copyright 2020 Hari Sekhon. The script in this post is used under the MIT License.