Re: sql query for postgres replication check

Michael Paquier <[email protected]> Mon, 25 Nov 2019 13:14:28 +0900

Newsgroups	gmane.comp.db.postgresql.general
Message-ID	<[email protected]>

On Fri, Nov 22, 2019 at 01:20:59PM +0000, Zwettler Markus (OIZ) wrote:
> 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.

pg_stat_wal_receiver is available only on the receiver, aka the
standby so it would not really be helpful on a primary.  On top of
that streaming replication is system-wide, so there is no actual point
to look at databases either.

> Would this do the job or am I missing something here?

Here is a suggestion for Nagios: hot_standby_delay, as told in
https://github.com/bucardo/check_postgres/blob/master/check_postgres.pl
--
Michael

signature.asc (application/pgp-signature, 833 B)