Re: Changing the setting of wal_sender_timeout per standby

On Tue, Sep 18, 2018 at 5:27 PM, Tsunakawa, Takayuki
<tsunakawa.takay@jp.fujitsu.com> wrote:
> From: Kyotaro HORIGUCHI [mailto:horiguchi.kyotaro@lab.ntt.co.jp]
>> At Fri, 14 Sep 2018 18:22:33 +0900, Masahiko Sawada <sawada.mshk@gmail.com>
>> wrote in
>> <CAD21AoBr2Y=N4iH8+6m5ara2GWdKE6ZrzWaqjZux6ErZ9pyAxQ@mail.gmail.com>
>> > On Thu, Sep 13, 2018 at 12:32 PM, Michael Paquier <michael@paquier.xyz>
>> wrote:
>> > > On Thu, Sep 13, 2018 at 01:14:12AM +0000, Tsunakawa, Takayuki wrote:
>> > >> Some customer wants to change the setting per standby, i.e., a shorter
>> > >> timeout for a standby in the same region to enable faster detection
>> > >> failure and failover, and a longer timeout for a standby in the remote
>> > >> region (for disaster recovery) to avoid mis-judging its health.
>> > >
>> > > This argument is sensible.
>> > >
>> > >> The current PGC_HUP allows to change the setting by editing
>> > >> postgresql.conf or ALTER SYSTEM and then sending SIGHUP to a specific
>> > >> walsender.  But that's not easy to use.  The user has to do it upon
>> > >> every switchover and failover.
>> > >>
>> > >> With PGC_BACKEND, the user would be able to tune the timeout as follows:
>> > >>
>> > >> [recovery.conf]
>> > >> primary_conninfo = '... options=''-c wal_sender_timeout=60000'' ...'
>> > >>
>> > >> With PGC_USERSET, the user would be able to use different user
>> > >> accounts for each standby, and tune the setting as follows:
>> > >>
>> > >> ALTER USER repluser_remote SET wal_sender_timeout = 60000;
>> > >
>> > > It seems to me that switching to PGC_BACKENDwould cover already all
>> the
>> > > use-cases you are mentioning, as at the end one would just want to
>> > > adjust the WAL sender timeout on a connection basis depending on the
>> > > geographical location of the receiver and the latency between primary
>> > > and standby.
>> >
>> > +1 for PGC_BACKEND. It looks enough for most use cases.
>>
>> +1, and we need a means to see the actual value, in
>> pg_stat_replication?
>
> Thanks, the patch attached.  I'll add this to the next CF shortly.  As Michael said, I think viewing the configured
valuewould be a separate feature.
 
>

Thank you for the patch.

+       <tip>
+        <para>
+         The <literal>%q</literal> escape is useful when including
information that is
+         You can also set this in recovery.conf as follows.  This
allows you to set a
+         longer timeout for a standby in the remote data center
across the slow WAN.
+<programlisting>
+primary_conninfo = 'host=192.168.1.50 port=5432 user=foo
password=foopass options=''-c wal_sender_timeout=5000'''
+</programlisting>
+        </para>
+       </tip>

I didn't follow the first sentence of the above hunk. Is the
wal_sender_timeout relevant with %q?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center