Re: PITR based recovery failing due to difference in max_connections

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 <ronljohnsonjr(at)gmail(dot)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.
>