pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PITR based recovery failing due to difference in max_connections
3+ messages / 2 participants
[nested] [flat]

* PITR based recovery failing due to difference in max_connections
@ 2023-08-04 04:47  Kalit Inani <inanikalit31@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Kalit Inani @ 2023-08-04 04:47 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

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

   Next, we did a PITR based recovery in another instance. During the
   recovery we have used a config file with MAX_CONNECTIONS as 150.
   5.

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PITR based recovery failing due to difference in max_connections
@ 2023-08-04 04:57  Ron <ronljohnsonjr@gmail.com>
  parent: Kalit Inani <inanikalit31@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Ron @ 2023-08-04 04:57 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PITR based recovery failing due to difference in max_connections
@ 2023-08-04 08:57  Kalit Inani <inanikalit31@gmail.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Kalit Inani @ 2023-08-04 08:57 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-08-04 08:57 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-04 04:47 PITR based recovery failing due to difference in max_connections Kalit Inani <inanikalit31@gmail.com>
2023-08-04 04:57 ` Ron <ronljohnsonjr@gmail.com>
2023-08-04 08:57   ` Kalit Inani <inanikalit31@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox