pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: PITR based recovery failing due to difference in max_connections
Date: Thu, 3 Aug 2023 23:57:33 -0500
Message-ID: <25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com> (raw)
In-Reply-To: <CAKOU5k5RtQ_KXwwsgFdzaMUPMqse0RppimjQFXwUkrzWuBGCyw@mail.gmail.com>
References: <CAKOU5k5RtQ_KXwwsgFdzaMUPMqse0RppimjQFXwUkrzWuBGCyw@mail.gmail.com>

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.

view thread (3+ messages)  latest in thread

Message-ID: <25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com>
Permalink:  ../25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com/
Also on:    postgresql.org/message-id/25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: PITR based recovery failing due to difference in max_connections
  In-Reply-To: <25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com>
  References: <CAKOU5k5RtQ_KXwwsgFdzaMUPMqse0RppimjQFXwUkrzWuBGCyw@mail.gmail.com> <25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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