pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMonitoring Streaming Replication
2+ messages / 2 participants
[nested] [flat]
* Monitoring Streaming Replication
@ 2026-07-20 13:34 Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Daulat @ 2026-07-20 13:34 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi team,
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).
Could you suggest the best approach or a more comprehensive monitoring
script for PostgreSQL 10 streaming replication that covers not only
replication lag but also these failure scenarios? If some of these
conditions cannot be detected using SQL alone.
Thanks.
.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Monitoring Streaming Replication
@ 2026-07-20 14:32 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daulat <daulat.dba@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2026-07-20 14:32 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-07-20 14:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-20 13:34 Monitoring Streaming Replication Daulat <daulat.dba@gmail.com>
2026-07-20 14:32 ` Laurenz Albe <laurenz.albe@cybertec.at>
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