Обсуждение: Standby server won't start
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
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
>> 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
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
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
> 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
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
> 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
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
Вложения
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