Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qRmsf-0034e4-8T for pgsql-general@arkaria.postgresql.org; Fri, 04 Aug 2023 04:57:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qRmsd-003mdA-Hs for pgsql-general@arkaria.postgresql.org; Fri, 04 Aug 2023 04:57:39 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qRmsd-003md0-58 for pgsql-general@lists.postgresql.org; Fri, 04 Aug 2023 04:57:39 +0000 Received: from mail-yw1-x1136.google.com ([2607:f8b0:4864:20::1136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qRmsa-000Toc-B0 for pgsql-general@lists.postgresql.org; Fri, 04 Aug 2023 04:57:38 +0000 Received: by mail-yw1-x1136.google.com with SMTP id 00721157ae682-5839f38342fso19038747b3.0 for ; Thu, 03 Aug 2023 21:57:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691125054; x=1691729854; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=LgMyd+xgCr+FYEyeHbrLJsLPmOh45ZzDSvcbfR+RqoI=; b=LXDu2sSLAeWwMpC33Txjm+gX4Frdfprb5ORRRpW3+QmVpmCFULbKPo/1wEcTguPprC U94DmrKeIY8Y+V7qgcaIQp/A5FSM0XrakYfIlmh48OMzhdwrwehIlRg7Vg1roOtz8EWH +CA7UT9xpdh2xoyxQbjq2+4ZrLWmXEwqXuqUMNOU3vO3Q68B9N3u6S3uYprFbm+dwcTG a9Y52lu6iyv8Jq3elXpdHe/o/UHVkehdnBpANoW1sOWhhBNr+RQmTrpXfdbFN27AtjLk 419QYFk/Lw4XrKdMAgx9sRkW/nI4tY5TlciT1tSS8XIGTWj/ntb2h4ttsyloo3FHktcU 6l4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691125054; x=1691729854; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=LgMyd+xgCr+FYEyeHbrLJsLPmOh45ZzDSvcbfR+RqoI=; b=INXZMrtiU7xqcy82i+lJR6DQewnMDbsqr+fqbFUVZkjaCUMf5fmuy0LNOZ0/APUzei 0VvqPOH0KsNP1Esd5WWap97dLybPkPLdwxfIZIfV+yvrFaWxDUhHDHFQEQ0ZUAUm1TS4 sasTfeZEN07MgDIsQyhd2wxj+XbRh/LwuHzBjQm636KkLcnBfm3s4n5pDUAllDDJj/gT l4H/rjXwxXjWnXuTBOzH3ErcmihCQnX1DfOSbcSxrAfV5354kkf2L6wbQiw6TrO2KLO/ OS4yHzndy7qr1dC40pvKjGljRTTv4llyBEprllPt1bYxLRBU/YC9espPD2kGMl5yaca0 7pIA== X-Gm-Message-State: AOJu0Yyq70QE4Wwbm+GBBkU09TwnH+wC7DhrafGp5LEx+cZQfpTpwl/5 +Nn1JFGbDdpE6n9z5vdANOWpngJzNrk= X-Google-Smtp-Source: AGHT+IFvxokNIfVobhiQMaz48pjfP8IF0wut3ZUEjzaLjSiQhlrvIgMdDEUyrIPZfth0xCC72WMwZw== X-Received: by 2002:a81:6cd5:0:b0:584:65b2:ba15 with SMTP id h204-20020a816cd5000000b0058465b2ba15mr817342ywc.14.1691125054354; Thu, 03 Aug 2023 21:57:34 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:1488:8492:d7a6:6515? ([2600:1700:bd2c:b810:1488:8492:d7a6:6515]) by smtp.googlemail.com with ESMTPSA id k188-20020a816fc5000000b0058461c9524fsm485277ywc.12.2023.08.03.21.57.33 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 03 Aug 2023 21:57:34 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------cLH9Tt6HWe9wGpd0tVw5w4ud" Message-ID: <25ff1149-8fcb-0fb5-2c1b-ec43ec928cc4@gmail.com> Date: Thu, 3 Aug 2023 23:57:33 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: PITR based recovery failing due to difference in max_connections Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------cLH9Tt6HWe9wGpd0tVw5w4ud Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------cLH9Tt6HWe9wGpd0tVw5w4ud Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------cLH9Tt6HWe9wGpd0tVw5w4ud--