From: | "Zwettler Markus (OIZ)" <Markus(dot)Zwettler(at)zuerich(dot)ch> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | sql query for postgres replication check |
Date: | 2019-11-22 13:20:59 |
Message-ID: | cf8b3fce87404edd836ceb705d36776b@zuerich.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jason L. Amerson | 2019-11-22 13:40:31 | RE: Remote Connection Help |
Previous Message | Moreno Andreo | 2019-11-22 13:13:44 | Re: [SPAM] Remote Connection Help |