On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier <michael.paqu...@gmail.com> wrote:
> On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes <jeff.ja...@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). 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
