Re: Script to check replication

Le Fri, 4 Mar 2016 19:35:16 +0000,
Ashish Chauhan <Ashish(dot)Chauhan(at)support(dot)com> a écrit :

> Hi,
>
> We are running Streaming replication with Hot standby in our current
> production. I do have below script to check replication status and
> replication lag. Does anyone have script which runs every 15 mins to check
> replication status and send out email if replication is not running or
> lagging behind.
>
> SELECT pg_last_xlog_receive_location() receive,
> pg_last_xlog_replay_location() replay, now() -
> pg_last_xact_replay_timestamp() AS replication_delay, (extract(epoch FROM
> now()) - extract(epoch FROM pg_last_xact_replay_timestamp()))::int lag
>
> Thanks for your help!

Usually, such requirement is fullfilled by a monitoring system. Eg. Nagios,
Zabbix, Munin, and so on.

Considering the replication check or lag check, we are using
check_pgactivity[1] (see releases [2]). See its "streaming_delta" service. If
you don't use Nagios or a compatible derivative, writing a wrapper around this
script is quite easy to do.

Regards,

[1] https://github.com/OPMDG/check_pgactivity
[2] https://github.com/OPMDG/check_pgactivity/releases