Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier <michael.paquier@gmail.com>
> wrote:
>>
>> 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).
>
> 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.

Hm. Hot standby may not involve streaming replication. What about a
paragraph here instead?
https://www.postgresql.org/docs/devel/static/warm-standby.html#streaming-replication

In the monitoring subsection, we could tell that on a standby the WAL
receiver status can be retrieved from this view when changes are
streamed. What do you think?
--
Michael