max_connections different between primary and standby: is it possible?

On Thu, Feb 3, 2022, 3:07 PM Luca Ferrari <fluca1978@gmail.com> 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?

Thanks,
Luca

No. iirc, It has to be the same on all nodes in the physical replication
setup.

But if vis pgbouncer, you can maintain the same max_connection but alter

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