Thread: [HACKERS] [PostgreSQL 10] default of hot_standby should be "on"?
Hi, Default for hot_standby parameter should be "on" from PostgreSQL 10? In PostgreSQL 10, -w option is default for [pg_ctl start]. So in order to start standby we have to setting hot_standby to "on" or start standby with -W option. Change hot_standby to "on" will fix this inconvenience. wal_level is also changed default to "replica" in PostgreSQL 10, this might not be affect to primary. Just a little bit but I attached the patch. --- Thanks and best regards, Dang Minh Huong NEC Solution Innovators, Ltd. http://www.nec-solutioninnovators.co.jp/en/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On 26 April 2017 at 08:30, Huong Dangminh <huo-dangminh@ys.jp.nec.com> wrote: > Default for hot_standby parameter should be "on" from PostgreSQL 10? > > In PostgreSQL 10, -w option is default for [pg_ctl start]. > So in order to start standby we have to setting hot_standby to "on" or > start standby with -W option. Yeah. That's a good reason to change it. I think at this point warm standby is clearly the less-used secondary option and hot_standby should be default. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Apr 26, 2017 at 9:36 AM, Craig Ringer <craig@2ndquadrant.com> wrote: > On 26 April 2017 at 08:30, Huong Dangminh <huo-dangminh@ys.jp.nec.com> wrote: > >> Default for hot_standby parameter should be "on" from PostgreSQL 10? >> >> In PostgreSQL 10, -w option is default for [pg_ctl start]. >> So in order to start standby we have to setting hot_standby to "on" or >> start standby with -W option. > > Yeah. That's a good reason to change it. I think at this point warm > standby is clearly the less-used secondary option and hot_standby > should be default. > The idea of changing the default value seems good to me but I'm not sure it's good idea to change the default value now under the circumstances where we're focus on stabilization. Also we should update the document as well. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
Re: [HACKERS] [PostgreSQL 10] default of hot_standby should be "on"?
From
"Tsunakawa, Takayuki"
Date:
From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Masahiko Sawada > The idea of changing the default value seems good to me but I'm not sure > it's good idea to change the default value now under the circumstances where > we're focus on stabilization. > Also we should update the document as well. > We can consider like this: the OP found a usability problem as a result of PG 10 development, so we will fix it as a stabilizationwork. Regards Takayuki Tsunakawa
On Wed, Apr 26, 2017 at 07:33:27AM +0000, Tsunakawa, Takayuki wrote: > From: pgsql-hackers-owner@postgresql.org > > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Masahiko Sawada > > The idea of changing the default value seems good to me but I'm not sure > > it's good idea to change the default value now under the circumstances where > > we're focus on stabilization. > > Also we should update the document as well. > > > > We can consider like this: the OP found a usability problem as a result of PG 10 development, so we will fix it as a stabilizationwork. We did work in Postgres 10 to make replication simpler with better defaults. This would be part of that improvement. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Wed, Apr 26, 2017 at 1:25 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Apr 26, 2017 at 07:33:27AM +0000, Tsunakawa, Takayuki wrote:
> From: pgsql-hackers-owner@postgresql.org
> > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Masahiko Sawada
> > The idea of changing the default value seems good to me but I'm not sure
> > it's good idea to change the default value now under the circumstances where
> > we're focus on stabilization.
> > Also we should update the document as well.
> >
>
> We can consider like this: the OP found a usability problem as a result of PG 10 development, so we will fix it as a stabilization work.
We did work in Postgres 10 to make replication simpler with better
defaults. This would be part of that improvement.
+1. I definitely think we should do it, and 10 would be the time to do it.
The failure scenario is that a standby node will no longer work by default *if* you have changed the master to minimal. But unless you have explicitly dropped that one, it would work.
So I definitely think we should change that.
I wonder if we should also consider changing the standby error message to be a WARNING instead of an ERROR. So that if you try to start up a standby with hot_standby=on but master with wal_level=replica it would turn into a cold standby.
We should change the default independently of that, I think, but it might make sense to do both.
Magnus Hagander <magnus@hagander.net> writes:
> +1. I definitely think we should do it, and 10 would be the time to do it.
Agreed. It's mainly a historical accident that the default is what it is,
I think.
> I wonder if we should also consider changing the standby error message to
> be a WARNING instead of an ERROR. So that if you try to start up a standby
> with hot_standby=on but master with wal_level=replica it would turn into a
> cold standby.
I'm -1 for that: if you fat-finger the configuration, you should be told
about it, not have the system start up in an unintended mode that lacks
critical functionality.
regards, tom lane
Re: [HACKERS] [PostgreSQL 10] default of hot_standby should be "on"?
From
Vaishnavi Prabakaran
Date:
On Wed, Apr 26, 2017 at 9:52 PM, Magnus Hagander <magnus@hagander.net> wrote:
I wonder if we should also consider changing the standby error message to be a WARNING instead of an ERROR. So that if you try to start up a standby with hot_standby=on but master with wal_level=replica it would turn into a cold standby.
Perhaps, you mean hot_standby=off here?
Regards,
Vaishnavi
Fujitsu Australia.
Thanks all for your comments. > Magnus Hagander <magnus@hagander.net> writes: > > +1. I definitely think we should do it, and 10 would be the time to do > it. > > Agreed. It's mainly a historical accident that the default is what it > is, > I think. > > > I wonder if we should also consider changing the standby error message > to > > be a WARNING instead of an ERROR. So that if you try to start up a standby > > with hot_standby=on but master with wal_level=replica it would turn into > a > > cold standby. > > I'm -1 for that: if you fat-finger the configuration, you should be told > about it, not have the system start up in an unintended mode that lacks > critical functionality. > > regards, tom lane I attached the patch which also update manual as the mention of sawada-san. --- Thanks and best regards, Dang Minh Huong NEC Solution Innovators, Ltd. http://www.nec-solutioninnovators.co.jp/en/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On Thu, Apr 27, 2017 at 9:54 AM, Huong Dangminh <huo-dangminh@ys.jp.nec.com> wrote: > Thanks all for your comments. > >> Magnus Hagander <magnus@hagander.net> writes: >> > +1. I definitely think we should do it, and 10 would be the time to do >> it. >> >> Agreed. It's mainly a historical accident that the default is what it >> is, >> I think. >> >> > I wonder if we should also consider changing the standby error message >> to >> > be a WARNING instead of an ERROR. So that if you try to start up a standby >> > with hot_standby=on but master with wal_level=replica it would turn into >> a >> > cold standby. >> >> I'm -1 for that: if you fat-finger the configuration, you should be told >> about it, not have the system start up in an unintended mode that lacks >> critical functionality. >> >> regards, tom lane > > I attached the patch which also update manual as the mention of sawada-san. > Thank you for updating the patch. Also maybe we can update line in PostgresNode.pm where hot_standby is set to on explicitly. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, Apr 27, 2017 at 11:48 AM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > Thank you for updating the patch. Also maybe we can update line in > PostgresNode.pm where hot_standby is set to on explicitly. I would refrain from doing that, having some parameters listed in the tests makes the intention behind those perl routines clear. -- Michael
> On Thu, Apr 27, 2017 at 11:48 AM, Masahiko Sawada <sawada.mshk@gmail.com> > wrote: > > Thank you for updating the patch. Also maybe we can update line in > > PostgresNode.pm where hot_standby is set to on explicitly. > > I would refrain from doing that, having some parameters listed in the > tests makes the intention behind those perl routines clear. > -- > Michael Thanks, attached patch update PostgresNode.pm file. I also did the regression test and found no problem. # sorry Sawada-san, Michael-san, because of security restriction # we could not mail to gmail address from our environment. --- Thanks and best regards, Dang Minh Huong NEC Solution Innovators, Ltd. http://www.nec-solutioninnovators.co.jp/en/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On Thu, Apr 27, 2017 at 1:58 PM, Huong Dangminh <huo-dangminh@ys.jp.nec.com> wrote: >> On Thu, Apr 27, 2017 at 11:48 AM, Masahiko Sawada <sawada.mshk@gmail.com> >> wrote: >> > Thank you for updating the patch. Also maybe we can update line in >> > PostgresNode.pm where hot_standby is set to on explicitly. >> >> I would refrain from doing that, having some parameters listed in the >> tests makes the intention behind those perl routines clear. Hmm, you've got a point. But when we changed the default values related to replication we dropped some explicitly settings from the regression test code. > > Thanks, attached patch update PostgresNode.pm file. > I also did the regression test and found no problem. Looks good to me. > > # sorry Sawada-san, Michael-san, because of security restriction > # we could not mail to gmail address from our environment. No problem! > --- > Thanks and best regards, > Dang Minh Huong > NEC Solution Innovators, Ltd. > http://www.nec-solutioninnovators.co.jp/en/ > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, Apr 27, 2017 at 4:33 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> On Thu, Apr 27, 2017 at 1:58 PM, Huong Dangminh
> <huo-dangminh@ys.jp.nec.com> wrote:
>>> On Thu, Apr 27, 2017 at 11:48 AM, Masahiko Sawada <sawada.mshk@gmail.com>
>>> wrote:
>>> > Thank you for updating the patch. Also maybe we can update line in
>>> > PostgresNode.pm where hot_standby is set to on explicitly.
>>>
>>> I would refrain from doing that, having some parameters listed in the
>>> tests makes the intention behind those perl routines clear.
>
> Hmm, you've got a point. But when we changed the default values
> related to replication we dropped some explicitly settings from the
> regression test code.
Looking at the patch. This is fine:
- # Change a setting and restart
- $node->append_conf('postgresql.conf', 'hot_standby = on');
- $node->restart();
But not that: print $conf "wal_log_hints = on\n";
- print $conf "hot_standby = on\n"; print $conf "max_connections = 10\n";
This is a minor point though.
--
Michael
> >>> I would refrain from doing that, having some parameters listed in the
> >>> tests makes the intention behind those perl routines clear.
> >
> > Hmm, you've got a point. But when we changed the default values
> > related to replication we dropped some explicitly settings from the
> > regression test code.
>
> Looking at the patch. This is fine:
> - # Change a setting and restart
> - $node->append_conf('postgresql.conf', 'hot_standby = on');
> - $node->restart();
>
> But not that:
> print $conf "wal_log_hints = on\n";
> - print $conf "hot_standby = on\n";
> print $conf "max_connections = 10\n";
>
> This is a minor point though.
Thanks, I attached the update patch.
---
Thanks and best regards,
Dang Minh Huong
NEC Solution Innovators, Ltd.
http://www.nec-solutioninnovators.co.jp/en/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachment
On Thu, Apr 27, 2017 at 11:05 PM, Huong Dangminh
<huo-dangminh@ys.jp.nec.com> wrote:
>> >>> I would refrain from doing that, having some parameters listed in the
>> >>> tests makes the intention behind those perl routines clear.
>> >
>> > Hmm, you've got a point. But when we changed the default values
>> > related to replication we dropped some explicitly settings from the
>> > regression test code.
>>
>> Looking at the patch. This is fine:
>> - # Change a setting and restart
>> - $node->append_conf('postgresql.conf', 'hot_standby = on');
>> - $node->restart();
>>
>> But not that:
>> print $conf "wal_log_hints = on\n";
>> - print $conf "hot_standby = on\n";
>> print $conf "max_connections = 10\n";
>>
>> This is a minor point though.
After some thoughts I agree to remain it in the perl code.
>
> Thanks, I attached the update patch.
So it looks good to me.
Regards,
--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
On Fri, Apr 28, 2017 at 3:43 AM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
On Thu, Apr 27, 2017 at 11:05 PM, Huong Dangminh
<huo-dangminh@ys.jp.nec.com> wrote:
>> >>> I would refrain from doing that, having some parameters listed in the
>> >>> tests makes the intention behind those perl routines clear.
>> >
>> > Hmm, you've got a point. But when we changed the default values
>> > related to replication we dropped some explicitly settings from the
>> > regression test code.
>>
>> Looking at the patch. This is fine:
>> - # Change a setting and restart
>> - $node->append_conf('postgresql.conf', 'hot_standby = on');
>> - $node->restart();
>>
>> But not that:
>> print $conf "wal_log_hints = on\n";
>> - print $conf "hot_standby = on\n";
>> print $conf "max_connections = 10\n";
>>
>> This is a minor point though.
After some thoughts I agree to remain it in the perl code.
>
> Thanks, I attached the update patch.
So it looks good to me.
Thanks!
Hi, On Tue, May 02, 2017 at 11:13:58AM +0200, Magnus Hagander wrote: > Looks good to me as well. Applied, with only a minor further docs addition > saying that this is the default also on the high availability page. I understand this is late, but a colleague alerted me to the following behaviour change: If you recover a server with default settings, it is our understanding that pg_isready will now return true immediately after the consistent state is reached and possibly well before recovery had actually ended (depending on the amount of outstanding wal). As hot standby works with log shipping, this is independent of the recovery.conf settings, i.e. even if standby_mode and primary_conninfo are not set. So if one was monitoring recovery like that before and expects pg_isready to only return true once the recovery is fully complete, this will now have to be adjusted. Also, if the recovered server is to be used for transactions, there will now be a window where the server accepts connections, but is in read-only mode. Before, one had the make the concious choice to set hot_standby to get the behaviour, now it might be surprising to users, or maybe I'm overthinking this? If that is indeed the case, maybe it should be mentioned more prominently in the documentation and/or get highlighted in the release notes? Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer
On Wed, May 3, 2017 at 4:18 PM, Michael Banck <michael.banck@credativ.de> wrote:
Hi,
On Tue, May 02, 2017 at 11:13:58AM +0200, Magnus Hagander wrote:
> Looks good to me as well. Applied, with only a minor further docs addition
> saying that this is the default also on the high availability page.
I understand this is late, but a colleague alerted me to the following
behaviour change: If you recover a server with default settings, it is
our understanding that pg_isready will now return true immediately after
the consistent state is reached and possibly well before recovery had
actually ended (depending on the amount of outstanding wal). As hot
standby works with log shipping, this is independent of the
recovery.conf settings, i.e. even if standby_mode and primary_conninfo
are not set. So if one was monitoring recovery like that before and
expects pg_isready to only return true once the recovery is fully
complete, this will now have to be adjusted. Also, if the recovered
server is to be used for transactions, there will now be a window where
the server accepts connections, but is in read-only mode.
Before, one had the make the concious choice to set hot_standby to get
the behaviour, now it might be surprising to users, or maybe I'm
overthinking this?
If that is indeed the case, maybe it should be mentioned more
prominently in the documentation and/or get highlighted in the release
notes?
Hmm. That's an interesting usecase.
I don't think it's a big enough one to revert this change, but it definitely makes sense to mention it under incompatible changes.
I wonder if what we really want here, at least long-term, is a flag for pg_isready that makes it wait for a server to actually go out of recovery?`Seems that a tool like the one mentioned here would have to do that -- it can be done now by doing pg_isready first and then psql to check the status, but it seems like it could be a worthwhile addition?