On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier <michael.paquier@xxxxxxxxx> wrote:
On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes <jeff.janes@xxxxxxxxx> 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).
I think that pg_stat_wal_receiver should be crossreferenced in https://www.postgresql.org/docs/9.6/static/hot-standby.html , near the same place which it crossreferences table 9-79. That would make it more discoverable.
Cheers,
Jeff
