Message-ID: <ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel@cybertec.at>
Subject: Re: Monitoring Streaming Replication
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Daulat <daulat.dba@gmail.com>, pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Mon, 20 Jul 2026 16:32:54 +0200
In-Reply-To: 
 <CALfOM6JJpXSMCu10eCSq0uuvUKn72fsxn5PBu1=Kj9Aiz2u8og@mail.gmail.com>
References: 
	<CALfOM6JJpXSMCu10eCSq0uuvUKn72fsxn5PBu1=Kj9Aiz2u8og@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel%40cybertec.at>
Precedence: bulk

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:
>=20
> replication_lag=3D$(psql -U gateway postgres -tAc "
> SELECT CASE
> =C2=A0 =C2=A0 WHEN pg_last_xlog_receive_location() =3D pg_last_xlog_repla=
y_location()
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 THEN 0
> =C2=A0 =C2=A0 ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timesta=
mp())
> END;
> ")
>=20
> This script appears to measure only the replay lag on the standby.
> However, I am concerned that it may not detect certain replication failur=
e
> scenarios and could incorrectly report a healthy status.
>=20
> Specifically, I would like to know how to monitor the following situation=
s:
>=20
> 1. The standby is disconnected from the primary.
>=20
> 2. The WAL receiver process has stopped.
>=20
> 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 parame=
ter
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 rem=
oved
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 additiona=
l
information.

Yours,
Laurenz Albe