Thread: Standby server won't start

Standby server won't start

From
Tatsuo Ishii
Date:
I changed primary servers max_connections from 100 to 4 for just a
testing purpose. Now standby server won't start and complains:

hot standby is not possible because max_connections = 4 is a lower setting than on the master server (its value was
100)

My guess is this is because standby's pg_control file contains
previous primary setting (max_connections = 100). Is there any way to
start the standby server without re-creating pg_control (which implies
getting base backup again)? If not, there should be some way to allow
to start standby server without getting base backup...

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp



Re: Standby server won't start

From
Rajeev rastogi
Date:
On 21 March 2014 13:41, Tatsuo Wrote:
> I changed primary servers max_connections from 100 to 4 for just a
> testing purpose. Now standby server won't start and complains:
>
> hot standby is not possible because max_connections = 4 is a lower
> setting than on the master server (its value was 100)
>
> My guess is this is because standby's pg_control file contains previous
> primary setting (max_connections = 100). Is there any way to start the
> standby server without re-creating pg_control (which implies getting
> base backup again)? If not, there should be some way to allow to start
> standby server without getting base backup...

I think there is no way to do this because parameter from master is already set
in pg_control file, which can not be changed without taking new backup from master.

Also this is not recommended to have standby's max_connection values lesser than the master's max_connection value.

Thanks and Regards,
Kumar Rajeev Rastogi




Re: Standby server won't start

From
Tatsuo Ishii
Date:
>> I changed primary servers max_connections from 100 to 4 for just a
>> testing purpose. Now standby server won't start and complains:
>> 
>> hot standby is not possible because max_connections = 4 is a lower
>> setting than on the master server (its value was 100)
>> 
>> My guess is this is because standby's pg_control file contains previous
>> primary setting (max_connections = 100). Is there any way to start the
>> standby server without re-creating pg_control (which implies getting
>> base backup again)? If not, there should be some way to allow to start
>> standby server without getting base backup...
> 
> I think there is no way to do this because parameter from master is already set
> in pg_control file, which can not be changed without taking new backup from master.

Yes, that's the problem.

> Also this is not recommended to have standby's max_connection values lesser than the master's max_connection value.

In my case I had already changed primary's max_connections to 4 and
restarted it. So at that point both postgresql.conf of primary and
standby were 4.

Maybe we could enhance it something like this:

1) When standby starts, it get max_connections info from primary and  updates pg_control file if it's different.

2) Then standby restarts itself if max_connections has been changed in #1.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp



Re: Standby server won't start

From
Rajeev rastogi
Date:
On 21 March 2014 16:17, Tatsuo Wrote:


> In my case I had already changed primary's max_connections to 4 and
> restarted it. So at that point both postgresql.conf of primary and
> standby were 4.

If you changed max_connection to 4 only in primary, then I am not able to understand, how it got changed in standby
also(if you have not taken back again)? 
Let me know If I have missed something.

Thanks and Regards,
Kumar Rajeev Rastogi



Re: Standby server won't start

From
"MauMau"
Date:
From: "Rajeev rastogi" <rajeev.rastogi@huawei.com>
If you changed max_connection to 4 only in primary, then I am not able to 
understand, how it got changed in standby also (if you have not taken back 
again)?
Let me know If I have missed something.

The primary log the new value as an XLOG_PARAMETER_CHANGE WAL record at 
startup when the parameter value in postgresql.conf does not match the one 
in pg_control.  Then, the WAL record is sent to the standby and applied, 
which changes the value in pg_control on the standby.

Regards
MauMau




Re: Standby server won't start

From
Tatsuo Ishii
Date:
> The primary log the new value as an XLOG_PARAMETER_CHANGE WAL record
> at startup when the parameter value in postgresql.conf does not match
> the one in pg_control.  Then, the WAL record is sent to the standby
> and applied, which changes the value in pg_control on the standby.

Last time I tested in following way, max_connections in pg_control of
standby did not reflect the change in primary.

1) stop primary
2) stop standby
3) change max_connections to 4 in primary
4) change max_connections to 4 in standby
5) start primary
6) start standby but it failed as I said before

So the particular WAL record was not sent to standby?
I'm going to test again...

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp



Re: Standby server won't start

From
"MauMau"
Date:
From: "Tatsuo Ishii" <ishii@postgresql.org>
> Last time I tested in following way, max_connections in pg_control of
> standby did not reflect the change in primary.
>
> 1) stop primary
> 2) stop standby
> 3) change max_connections to 4 in primary
> 4) change max_connections to 4 in standby
> 5) start primary
> 6) start standby but it failed as I said before
>
> So the particular WAL record was not sent to standby?
> I'm going to test again...

That's because the parameter is checked at the beginning of recovery (i.e. 
at standby start) before XLOG_PARAMETER_CHANGE is received and applied on 
the standby.  Please see CheckRequiredParameterValues() in StartupXLOG().

To persist the max_connections change:

1) stop primary
2) change max_connections on the primary
3) start primary
4) watch pg_stat_replication to wait until the standby is sync with the 
primary (XLOG_PARAMETER_CHANGE is applied)
5) stop standby
6) change max_connections on the standby
7) start standby

Regards
MauMau




Re: Standby server won't start

From
Tatsuo Ishii
Date:
> That's because the parameter is checked at the beginning of recovery
> (i.e. at standby start) before XLOG_PARAMETER_CHANGE is received and
> applied on the standby.  Please see CheckRequiredParameterValues() in
> StartupXLOG().
> 
> To persist the max_connections change:
> 
> 1) stop primary
> 2) change max_connections on the primary
> 3) start primary
> 4) watch pg_stat_replication to wait until the standby is sync with
> the primary (XLOG_PARAMETER_CHANGE is applied)
> 5) stop standby
> 6) change max_connections on the standby
> 7) start standby

Unfotunately this did not work for me. pg_stat_replication showed
replay_location and sent_location are identical, and I assume the
standby is sync with the primary in step #4. Still the standby did not
start in #7 with same error message I showed. This is PostgreSQL
9.3.3. Also pg_controldata <standby DB cluster> showed the old
max_connections at #7. So I guess XLOG_PARAMETER_CHANGE has not been
sent for some reason. Will look into this.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp



Re: Standby server won't start

From
Fujii Masao
Date:
On Sat, Mar 22, 2014 at 9:33 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>> That's because the parameter is checked at the beginning of recovery
>> (i.e. at standby start) before XLOG_PARAMETER_CHANGE is received and
>> applied on the standby.  Please see CheckRequiredParameterValues() in
>> StartupXLOG().
>>
>> To persist the max_connections change:
>>
>> 1) stop primary
>> 2) change max_connections on the primary
>> 3) start primary
>> 4) watch pg_stat_replication to wait until the standby is sync with
>> the primary (XLOG_PARAMETER_CHANGE is applied)
>> 5) stop standby
>> 6) change max_connections on the standby
>> 7) start standby
>
> Unfotunately this did not work for me. pg_stat_replication showed
> replay_location and sent_location are identical, and I assume the
> standby is sync with the primary in step #4. Still the standby did not
> start in #7 with same error message I showed. This is PostgreSQL
> 9.3.3. Also pg_controldata <standby DB cluster> showed the old
> max_connections at #7. So I guess XLOG_PARAMETER_CHANGE has not been
> sent for some reason. Will look into this.

ISTM that's because WAL has not been flushed after XLOG_PARAMETER_CHANGE
is generated.  Attached patch fixes this problem.

Regards,

--
Fujii Masao

Attachment

Re: Standby server won't start

From
Fujii Masao
Date:
On Mon, Mar 24, 2014 at 8:59 PM, Fujii Masao <masao.fujii@gmail.com> wrote:
> On Sat, Mar 22, 2014 at 9:33 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:
>>> That's because the parameter is checked at the beginning of recovery
>>> (i.e. at standby start) before XLOG_PARAMETER_CHANGE is received and
>>> applied on the standby.  Please see CheckRequiredParameterValues() in
>>> StartupXLOG().
>>>
>>> To persist the max_connections change:
>>>
>>> 1) stop primary
>>> 2) change max_connections on the primary
>>> 3) start primary
>>> 4) watch pg_stat_replication to wait until the standby is sync with
>>> the primary (XLOG_PARAMETER_CHANGE is applied)
>>> 5) stop standby
>>> 6) change max_connections on the standby
>>> 7) start standby
>>
>> Unfotunately this did not work for me. pg_stat_replication showed
>> replay_location and sent_location are identical, and I assume the
>> standby is sync with the primary in step #4. Still the standby did not
>> start in #7 with same error message I showed. This is PostgreSQL
>> 9.3.3. Also pg_controldata <standby DB cluster> showed the old
>> max_connections at #7. So I guess XLOG_PARAMETER_CHANGE has not been
>> sent for some reason. Will look into this.
>
> ISTM that's because WAL has not been flushed after XLOG_PARAMETER_CHANGE
> is generated.  Attached patch fixes this problem.

Committed.

Regards,

-- 
Fujii Masao