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

[Peter Eisentraut]

On 7/27/17 16:14, Jeff Janes wrote:
> On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier
> > wrote:
> 
> On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes  > wrote:
> > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier 
> >
> > wrote:
> >> What do you think about the patch attached?
> >
> > Looks OK.  Should it mention specifically "On a hot standby" rather 
> than "On
> > a standby"?  Otherwise people might be left confused on how they are
> > supposed to do this on a generic standby.  It is the kind of thing 
> which is
> > obvious once you know it, but confusing the first time you encounter it.
> 
> Yes, right. Let's update as you suggest.
> 
> 
> new version looks good.

committed

I changed to links to xrefs, which automatically generated the correct
target texts.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jeff Janes]

On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier 
wrote:

> On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes  wrote:
> > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier <
> michael.paqu...@gmail.com>
> > wrote:
> >> What do you think about the patch attached?
> >
> > Looks OK.  Should it mention specifically "On a hot standby" rather than
> "On
> > a standby"?  Otherwise people might be left confused on how they are
> > supposed to do this on a generic standby.  It is the kind of thing which
> is
> > obvious once you know it, but confusing the first time you encounter it.
>
> Yes, right. Let's update as you suggest.
>

new version looks good.

Thanks,

Jeff

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

[Michael Paquier]

On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes  wrote:
> On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier 
> wrote:
>> What do you think about the patch attached?
>
> Looks OK.  Should it mention specifically "On a hot standby" rather than "On
> a standby"?  Otherwise people might be left confused on how they are
> supposed to do this on a generic standby.  It is the kind of thing which is
> obvious once you know it, but confusing the first time you encounter it.

Yes, right. Let's update as you suggest.

>>  
>>   You can retrieve a list of WAL sender processes via the
>> - 
>> + 
>>   pg_stat_replication view. Large differences
>> between
>> In the previous paragraph I have noticed that the link reference is
>> incorrect. pg_stat_replication is listed under
>> monitoring-stats-dynamic-views-table.
>
> Yes, that is clearly wrong.  But why not link directly to the description of
> the view itself, pg-stat-replication-view, rather than the correct table
> which mentions the view?  Is that the accepted docs style to link to the
> more generic place?  (Same thing applies to your patch, it could link
> directly to pg-stat-wal-receiver-view.

Yes, that's even better.

> Sorry for the delay, it took me awhile to get the new doc build system to
> work (solution seems to be, "Don't use CentOS6 anymore")

:)
Thanks for the review.
-- 
Michael


walreceiver-doc-v2.patch
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jeff Janes]

On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier 
wrote:

> On Fri, Jul 14, 2017 at 9:11 PM, Jeff Janes  wrote:
> > On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier
> >  wrote:
> >>
> >> On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes 
> wrote:
> >> >
> >> > 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?
> >
> >
> > That works for me.
>
> What do you think about the patch attached?
>

Looks OK.  Should it mention specifically "On a hot standby" rather than
"On a standby"?  Otherwise people might be left confused on how they are
supposed to do this on a generic standby.  It is the kind of thing which is
obvious once you know it, but confusing the first time you encounter it.



>
>  
>   You can retrieve a list of WAL sender processes via the
> - 
> + 
>   pg_stat_replication view. Large differences
> between
> In the previous paragraph I have noticed that the link reference is
> incorrect. pg_stat_replication is listed under
> monitoring-stats-dynamic-views-table.
>

Yes, that is clearly wrong.  But why not link directly to the description
of the view itself, pg-stat-replication-view, rather than the correct table
which mentions the view?  Is that the accepted docs style to link to the
more generic place?  (Same thing applies to your patch, it could link
directly to pg-stat-wal-receiver-view.

Sorry for the delay, it took me awhile to get the new doc build system to
work (solution seems to be, "Don't use CentOS6 anymore")

Cheers,

Jeff

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

[Michael Paquier]

On Fri, Jul 14, 2017 at 9:11 PM, Jeff Janes  wrote:
> On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier
>  wrote:
>>
>> On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes  wrote:
>> >
>> > 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?
>
>
> That works for me.

What do you think about the patch attached?

 
  You can retrieve a list of WAL sender processes via the
- 
+ 
  pg_stat_replication view. Large differences between
In the previous paragraph I have noticed that the link reference is
incorrect. pg_stat_replication is listed under
monitoring-stats-dynamic-views-table.
-- 
Michael


walreceiver-doc.patch
Description: Binary data

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Lucas Possamai]

2017-07-13 20:15 GMT+12:00 Michael Paquier :

> On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  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..

It works pretty well and I used bash.

Lucas

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

[Jeff Janes]

On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier  wrote:

> On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes  wrote:
> >
> > 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?
>

That works for me.

Cheers,

Jeff

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

[Michael Paquier]

On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes  wrote:
> On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier 
> wrote:
>>
>> On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jeff Janes]

On Thu, Jul 13, 2017 at 1:15 AM, Michael Paquier 
wrote:

> On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  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

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

[Michael Paquier]

On Thu, Jul 13, 2017 at 5:26 AM, Jeff Janes  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general