On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > > I think that none of the recovery information functions > (https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE) > can distinguish a hot standby which is connected to an idle master, versus > one which is disconnected. For example, because the master has crashed, or > someone has changed the firewall rules. > > Is there a way to monitor from SQL the last time the standby was able to > contact the master and initiate streaming with it? Other than trying to > write a function that parses it out of pg_log?
Not directly I am afraid. One way I can think about is to poll periodically the state of pg_stat_replication on the primary or pg_stat_wal_receiver on the standby and save it in a custom table. The past information is not persistent as any replication-related data in catalogs is based on the shared memory state of the WAL senders and the WAL receiver, and those are wiped out at reconnection. -- Michael
That works for me too! I do this way... cron job runs that every X minutes and if the replication lag is higher than 1 second it sends me an email..