sql query for postgres replication check

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