Re: PITR based recovery failing due to difference in max_connections

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
> <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.