Обсуждение: max_connections different between primary and standby: is it possible?

Hi all,
running PostgreSQL 14, physical replication with slot, after changing
(increasing) the max_connections on the primary, I had this message at
a restart from the standby:

DETAIL:  max_connections = 100 is a lower setting than on the primary
server, where its value was 300.

and the standby does not start until I raise also its max_connections.
Why is PostgreSQL requiring the max_connections to be aligned between
the primary and the standby?

Thanks,
Luca

Hi,

On Thu, Feb 03, 2022 at 10:36:37AM +0100, Luca Ferrari wrote:
> Hi all,
> running PostgreSQL 14, physical replication with slot, after changing
> (increasing) the max_connections on the primary, I had this message at
> a restart from the standby:
> 
> DETAIL:  max_connections = 100 is a lower setting than on the primary
> server, where its value was 300.
> 
> and the standby does not start until I raise also its max_connections.
> Why is PostgreSQL requiring the max_connections to be aligned between
> the primary and the standby?

The value needs to be at least equal as the value on the primary node, but it
can be bigger.

That's because the standby needs to have enough resources to replay the
activity from the primary, including some heavyweight locks acquisition, and
the number of locks you can hold at one time is partially based on
max_connections.

On Thu, Feb 3, 2022 at 3:17 PM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> Hi,
>
> On Thu, Feb 03, 2022 at 10:36:37AM +0100, Luca Ferrari wrote:
> > Hi all,
> > running PostgreSQL 14, physical replication with slot, after changing
> > (increasing) the max_connections on the primary, I had this message at
> > a restart from the standby:
> >
> > DETAIL:  max_connections = 100 is a lower setting than on the primary
> > server, where its value was 300.
> >
> > and the standby does not start until I raise also its max_connections.
> > Why is PostgreSQL requiring the max_connections to be aligned between
> > the primary and the standby?
>
> The value needs to be at least equal as the value on the primary node, but it
> can be bigger.
>
> That's because the standby needs to have enough resources to replay the
> activity from the primary, including some heavyweight locks acquisition, and
> the number of locks you can hold at one time is partially based on
> max_connections.

Agree that the standby should atleast have the capacity that the
primary has in terms of resources. But what I don't like about that
code is calling RecoveryRequiresIntParameter for each parameter
separately and crashing the server FATALly for each insufficient
parameter. Imagine if all the parameters were set to insufficient
values on the standby and users keep setting the reported parameter to
the right value and restart the server. At max, 5 FATAL failure-set
right value-restart have to be performed. Instead, it would be better
if the server emits a single log with all the insufficient
parameters(max_connections, max_worker_processes, max_wal_senders,
max_prepared_transactions and max_locks_per_transaction) values and
crashes FATALly. The users can look at the logs at once, set all the
insufficient parameters to right values and restart the server.

Regards,
Bharath Rupireddy.

On Thu, Feb 03, 2022 at 05:39:57PM +0530, Bharath Rupireddy wrote:
> 
> Agree that the standby should atleast have the capacity that the
> primary has in terms of resources. But what I don't like about that
> code is calling RecoveryRequiresIntParameter for each parameter
> separately and crashing the server FATALly for each insufficient
> parameter. Imagine if all the parameters were set to insufficient
> values on the standby and users keep setting the reported parameter to
> the right value and restart the server. At max, 5 FATAL failure-set
> right value-restart have to be performed. Instead, it would be better
> if the server emits a single log with all the insufficient
> parameters(max_connections, max_worker_processes, max_wal_senders,
> max_prepared_transactions and max_locks_per_transaction) values and
> crashes FATALly. The users can look at the logs at once, set all the

Sure, but one failed start / inspect logs / modify configuration / start will
always by longer than just reading the docs and making sure that the
configuration is appropriate.  It also won't help if you want to modify the
settings on your primary and make sure that you won't have an incident on your
HA setup.

Julien Rouhaud <rjuju123@gmail.com> writes:
> On Thu, Feb 03, 2022 at 05:39:57PM +0530, Bharath Rupireddy wrote:
>> ... Instead, it would be better
>> if the server emits a single log with all the insufficient
>> parameters(max_connections, max_worker_processes, max_wal_senders,
>> max_prepared_transactions and max_locks_per_transaction) values and
>> crashes FATALly. The users can look at the logs at once, set all the

> Sure, but one failed start / inspect logs / modify configuration / start will
> always by longer than just reading the docs and making sure that the
> configuration is appropriate.  It also won't help if you want to modify the
> settings on your primary and make sure that you won't have an incident on your
> HA setup.

I don't recall any field complaints, ever, about this behavior.
So I'm skeptical that it's a place to expend effort.

            regards, tom lane