We would like to check the Postgres SYNC streaming replication status with Nagios using the same query on all servers (master + standby) and versions (9.6, 10, 12) for simplicity.
I came up with the following query which should return any apply lag in seconds.
select coalesce(replay_delay, 0) replication_delay_in_sec
from (
select datname,
(
select case
when received_lsn = latest_end_lsn then 0
else extract(epoch
from now() - latest_end_time)
end
from pg_stat_wal_receiver
) replay_delay
from pg_database
where datname = current_database()
) xview;
I would expect delays >0 in case SYNC or ASYNC replication is somehow behind. We will do a warning at 120 secs and critical at 300 secs.
Would this do the job or am I missing something here?
Thanks, Markus