Re: Monitoring Streaming Replication

On Mon, 2026-07-20 at 19:04 +0530, Daulat wrote:
> We are currently using the following script on our PostgreSQL 10 standby server
> to monitor streaming replication lag:
>
> replication_lag=$(psql -U gateway postgres -tAc "
> SELECT CASE
>     WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
>         THEN 0
>     ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
> END;
> ")
>
> This script appears to measure only the replay lag on the standby.
> However, I am concerned that it may not detect certain replication failure
> scenarios and could incorrectly report a healthy status.
>
> Specifically, I would like to know how to monitor the following situations:
>
> 1. The standby is disconnected from the primary.
>
> 2. The WAL receiver process has stopped.
>
> 3. The standby cannot continue replication because the required WAL files
> have already been removed from the primary (for example, requested WAL
> segment ... has already been removed).

The best is to monitor replication on the primary using pg_stat_replication.

"application_name" can identify the standby servers (if you set that parameter
in primary_conninfo) and you get the various lag entries (NULL if there is no
activity).

If the standby is disconnected, its row in the view is missing.
That will also be the normal state if the required WAL has already been removed
from the primary, because that terminates the replication connection.
So you can simply alert on missing rows in the view.

If you are using replication slots, pg_replication_slots provides additional
information.

Yours,
Laurenz Albe