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.
Thanks, that looks like what I want (or will be, once I get the other side to upgrade to 9.6).