Thread: [BUG] Streaming replica sees the old max_connections value
Hi, Linux 2.6.18-xenU-ec2-v1.0 (Engineyard) Gentoo Base System release 1.12.11.1 PostgreSQL 9.2.4 We have a master and two streaming replicas. Today, when I tried to change a value of max_connections I faced a strange postgres behavior on the replicas. The old value was 500. I changed it in the config files of all the servers to 100. Then I restarted postgres the master and everything was fine. However, when I restarted it the replicas I got a fatal on both of them: [...] LOG: database system is shut down LOG: database system was shut down in recovery at 2014-03-07 19:00:49 GMT LOG: entering standby mode FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 500) LOG: startup process (PID 12187) exited with exit code 1 [...] I checked if the new setting had been applied on the master, and it was: [local]:5432 postgres@postgres=# show max_connections [local]:5432 postgres@postgres-# ; max_connections ----------------- 100 (1 row) I restarted the replicas again with the same sad result. So, finally, I rolled the max_connections back to 500 and successfully restarted postgres on all the servers starting from the master. Are there any explanation of why the replicas didn't see the changes and how to work around it? Thank you. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On 2014-03-07 12:09:54 -0800, Sergey Konoplev wrote: > Today, when I tried to change a value of max_connections I faced a > strange postgres behavior on the replicas. The old value was 500. I > changed it in the config files of all the servers to 100. Then I > restarted postgres the master and everything was fine. However, when I > restarted it the replicas I got a fatal on both of them: > > [...] > LOG: database system is shut down > LOG: database system was shut down in recovery at 2014-03-07 19:00:49 GMT > LOG: entering standby mode > FATAL: hot standby is not possible because max_connections = 100 is a > lower setting than on the master server (its value was 500) > LOG: startup process (PID 12187) exited with exit code 1 That's easy to explain. The check is done based on the control file and that's already on the standby in your case. You need to first replay a bit of WAL from the primary after lowering the value, triggering the standby to update what it knows about the master's settings. When lowering values, it's simplest to first restart the primary with the new values, wait for the standbys to catch up, and then restart them. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Fri, Mar 7, 2014 at 12:22 PM, Andres Freund <andres@2ndquadrant.com> wrote: >> LOG: database system is shut down >> LOG: database system was shut down in recovery at 2014-03-07 19:00:49 GMT >> LOG: entering standby mode >> FATAL: hot standby is not possible because max_connections = 100 is a >> lower setting than on the master server (its value was 500) >> LOG: startup process (PID 12187) exited with exit code 1 > > That's easy to explain. The check is done based on the control file and > that's already on the standby in your case. You need to first replay a > bit of WAL from the primary after lowering the value, triggering the > standby to update what it knows about the master's settings. When > lowering values, it's simplest to first restart the primary with the new > values, wait for the standbys to catch up, and then restart them. Thank you. It helped. Looks like 20 seconds of the delay between restarts was not enough the first time. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
Hi, i got the same problem Andres Freund-3 wrote > The check is done based on the control file and > that's already on the standby in your case. Can you explain me where exactly is this done ? thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-Streaming-replica-sees-the-old-max-connections-value-tp5795209p5810574.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.