Postgres subscriber node took some time to reflect publisher config change which leads to postgres restart issue

Question

I am aware of the postgres config caveats mentioned here: The easiest way to ensure this does not become a problem is to have these parameters set on the standbys to values equal to or greater than on the primary

So for example, when I am reducing the param max_connections from 300 to 200, First I am reducing in publisher and then doing the same in subscriber to avoid the issue mentioned above.

Here, I found that, If we do it immediately in subscriber (say within 2 seconds after updating in publisher), got encountered by the issue:

hot standby is not possible because max_connections = 200 is a lower setting than on the master server (its value was 300)

It seems that the config update change in publisher not transmitted / subscriber still not aware of the update in publisher.

So i introduced a delay like : replication_lag + 2 seconds (not sure, whether it is right way or not). Now it is working fine.

So my question is:

How to avoid this issue when updating config in publisher first / Best practice steps ?
If introducing the delay is fine, How to compute delay correctly ?
Or Is there anything to check and confirm that the publisher config updated transmitted to all the subscribers ?

PS: Update 1:

Cluster setup (A test setup with 2 node cluster) - streaming replication
For each configuration update, postgres restarted.
Issue logs seen when restarting the subscriber node with new value. Also verified that the publisher got updated with new value (200) using pg_settings table.
These steps are done using a custom script from publisher node which can update configuration in both nodes (also restart the service on update).
Confirming that subscriber node max_connections=200 updated (also postgres restarted) immediately (within 2 seconds) once after the publisher node config updated (postgres restarted and verified the value in pg_settings).

"Subscriber" sounds like logical replication, but that error indicates streaming replication. Please clarify! Also, changing max_connections requires a restart, and you wrote nothing about a restart. Your question lacks clarity. — Laurenz Albe
– Laurenz Albe, Commented Apr 24, 2024 at 14:44

Laurenz Albe · Accepted Answer · 2024-04-24 17:45:04Z

1

max_connections can only changed upon server restart. Right when the server starts, after it is done with crash recovery (if there was any), it will write a WAL record that contains the current max_connections setting. As soon as the standby server has received and replayed that record, it knows about the new setting, and you can change the parameter on the standby.

So you could follow a procedure like this:

change the parameter on the primary and restart
get the result of pg_current_wal_lsn() on the primary
poll pg_stat_replication until replay_lsn for the standby has reached or passed that log sequence number
change the parameter on the standby and restart it

answered Apr 24, 2024 at 17:45

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres subscriber node took some time to reflect publisher config change which leads to postgres restart issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related