pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Laurenz Albe <laurenz.albe@cybertec.at>
To: Daulat <daulat.dba@gmail.com>
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Monitoring Streaming Replication
Date: Mon, 20 Jul 2026 16:32:54 +0200
Message-ID: <ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel@cybertec.at> (raw)
In-Reply-To: <CALfOM6JJpXSMCu10eCSq0uuvUKn72fsxn5PBu1=Kj9Aiz2u8og@mail.gmail.com>
References: <CALfOM6JJpXSMCu10eCSq0uuvUKn72fsxn5PBu1=Kj9Aiz2u8og@mail.gmail.com>
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
view thread (2+ messages)
Message-ID: <ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel@cybertec.at>
Permalink: ../ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel@cybertec.at/
Also on: postgresql.org/message-id/ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel@cybertec.at
reply
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-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, daulat.dba@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Monitoring Streaming Replication
In-Reply-To: <ebc48d596acd6144ffdef7fe264c3eee94bbe5ab.camel@cybertec.at>
* 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