Re: PITR based recovery failing due to difference in max_connections

Kalit Inani <inanikalit31@xxxxxxxxx> · Fri, 4 Aug 2023 14:27:13 +0530

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@xxxxxxxxx> 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 - 

            We took a snapshot of the data disk from a Postgres leader node, let’s call this as source instance.
            Then, we modified the MAX_CONNECTIONS in that source instance to 500.

    Why did you do that?

            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
            Next, we did a PITR based recovery in another instance. During the recovery we have used a config file with MAX_CONNECTIONS as 150.
            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.