sql query for postgres replication check

["Zwettler Markus (OIZ)" <Markus.Zwettler@xxxxxxxxxx>]

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