Hi, Thanks for the quick response. Regarding your questions, *Why did you do that?* This is our requirement and we are restoring this on another instance(destination instance) where are the 'max_connection' value should be less than that of the source instance(150 in our case).
On Fri, Aug 4, 2023 at 1:13 PM Ron <ronljohnso...@gmail.com> wrote: > On 8/3/23 23:47, Kalit Inani wrote: > > Hi all, > During PITR based recovery of a postgres instance, we are getting the > following error - > '2023-06-21 23:52:52.232 PDT [24244] FATAL: hot standby is not possible > because max_connections = 150 is a lower setting than on the master server > (its value was 500)' > > Here are the steps we are following - > > 1. > > We took a snapshot of the data disk from a Postgres leader node, let’s > call this as source instance. > 2. > > Then, we modified the MAX_CONNECTIONS in that source instance to 500. > > > Why did you do that? > > > 1. > > Due to the modification, the following wal_file entry gets generated - > rmgr: > XLOG len (rec/tot): 54/54, tx: 0, lsn: 1/5E0000A0, prev 1/5E000028, desc: > PARAMETER_CHANGE max_connections=500 max_worker_processes=8 > max_wal_senders=10 max_prepared_xacts=0 max_locks_per_xact=64 > wal_level=replica wal_log_hints=off track_commit_timestamp=off > 2. > > Next, we did a PITR based recovery in another instance. During the > recovery we have used a config file with MAX_CONNECTIONS as 150. > 3. > > However, the recovery fails with the following error - '2023-06-21 > 23:52:52.232 PDT [24244] FATAL: hot standby is not possible because > max_connections = 150 is a lower setting than on the master server (its > value was 500)' > > What are the probable solutions to fix this issue? > > Since it complains about a MAX_CONNECTIONS mismatch... *don't mismatch > MAX_CONNECTIONS*. > > Take a snapshot *after* setting MAX_CONNECTIONS = 500. > > One of the approaches we tried was to set ‘hot_standby = off’ in > postgresql.conf. By doing this, we are successfully able to restore the > source’s content on the destination instance. However, is this the correct > way to move forward? > > We also read the postgres documentation for hot_standby - > https://www.postgresql.org/docs/current/hot-standby.html > > It mentions - > “The settings of some parameters determine the size of shared memory for > tracking transaction IDs, locks, and prepared transactions. These shared > memory structures must be no smaller on a standby than on the primary in > order to ensure that the standby does not run out of shared memory during > recovery. For example, if the primary had used a prepared transaction but > the standby had not allocated any shared memory for tracking prepared > transactions, then recovery could not continue until the standby's > configuration is changed.” > > Does this mean that turning off hot_standby and then performing a recovery > operation may lead to some unintended consequences? Do we always have to > keep these parameter (‘max_connections’) values greater than equal to that > of the source instance? > > Thank you, > Kalit. > > > -- > Born in Arizona, moved to Babylonia. >
