Patroni: Provide way to change parameters that require master-first restart (like max_connections)

[ainlolcat]

I used simple approach to update parameters:

1. Prepare list of new parameters
2. Patch dcs config using curl: curl -XPATCH http://localhost:8008/config..
3. If some of parameters require restart of postgres - send restart request http://localhost:8008/restart

This working fine if I increase parameter max_connections but sometimes fails if I decrease it because replica cannot start with lower value but current dcs update does not guarantee reload order and replication state. So I need to enforce restart of master before replica, check if replica received new settings and then restart replica as well. For now I assume approach external to patroni:

1. Move parameters from dcs to file and import it;
2. During update put patroni to pause mode;
3. Update file with paramters;
4. Restart/reload master manually and then restart/reload replicas;
5. Resume patroni.

I wondering if such approach is fine or this situation should be handled by patroni. May be I need to use another approach?

[alexeyklyukin]

This working fine if I increase parameter max_connections but sometimes fails if I decrease it because replica cannot start with lower value but current dcs update does not guarantee reload order and replication state.

The reload order makes no difference here, since you are restarting nodes anyway. You should be able to restart all instances, Patroni will take care of the replicas starting before the master: if they fail because of the mismatch in max_connnections - Patroni will keep trying to start them again, until it succeeds (when the master gets the new settings applied).

[ainlolcat]

Sometimes I found sequence like this

1. Set max_connections 100 instead of 200.
2. Send restart.
3. Replica restarted with max_connections 100 before master.
4. Postgresql cannot start with such settings because last known master state was 200.
5. Patroni tries to start replica but cannot because replica has wrong configuration.

Sometheng like this https://www.postgresql.org/message-id/20150811073721.GA15633%40awork2.anarazel.de

[CyberDem0n]

Patroni will keep trying to start them again, until it succeeds

Unfortunately it will never finish, because postgres will simply refuse to start do to small value of max_connections :(

@ainlolcat Unfortunately we don't have a good solution for that. When decreasing max_connections, max_worker_processes, max_prepared_transactions or max_locks_per_transaction you have to restart master first. After that you have to wait until replica will receive those changes and restart it.

Basically you can record xlog location on the master right after restart and wait until replay location on the replica will become higher. Starting from this moment it is safe to restart particular replica.

P.S. you can use patronictl edit-config <cluster-name> to change configuration and patronictl restart <cluster-name> <node-name> instead of using REST API.

[ainlolcat]

So WA is fine and there is no internal mechanism for such case. Thank You. I wonder if such case can be handled in patroni in future? Since I either way need to implement WA or normal solution I can implement it in patroni. For example if change contains update for parameters like max_connections master should restart first. In such case master tries to update postgresql.conf, restart itself, wait for replication on replicas and then update dcs and return status 200(exit code 0). Replicas will get update on next run and change theirs configs.

[ainlolcat]

Considering WA. I tried to set include parameter via rest:

curl -XPATCH -d "{\"postgresql\":{ \"parameters\":{ \"include\": \"/patroni/pg_conf_initial.conf\"}} } " $(hostname -i):8008/config

and found some problems:

1. Patroni removes "include" if I change config via rest but uses "include" after restart.
2. Patroni uses its own "max_connections" default so I cannot use "include" for it because I cannot guarantee order of default "max_connections" and value from "include" file.

Config after start with include:

cat /var/lib/pgsql/data/postgresql_node2/postgresql.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
include 'postgresql.base.conf'

cluster_name = 'common'
hot_standby = 'on'
include = '/patroni/pg_conf_initial.conf'
listen_addresses = '0.0.0.0'
max_connections = '100'
....

Looks like WA cannot be implemented. Also I cannot use xlog_location because it doesnot work if I increase parameter (replica terminates itself).

For now I put patroni in pause mode, apply settings on master, restart it via REST, monitor logs for "started streaming WAL from primary" on replica and apply/restart it then. I suppose I dont need this ticket anymore. May be it can help another users if restrictions (for inlcude parameter and some parameters decreasing) will be mentioned in docs.

[jberkus]

I've renamed the issue to reflect the actual desired feature.

[ainlolcat]

If I understand correctly this issue was fixed in https://github.com/zalando/patroni/commit/e939304001dbce26b895a0cc22b72722b14e5ecd ?

[CyberDem0n]

https://github.com/zalando/patroni/commit/e939304001dbce26b895a0cc22b72722b14e5ecd -- is a definitely good anti-foot gun, because it won't leave the replica in the "start failed" state.
I.e. if you restart postgres earlier than it managed to replay the wal, containing max_connections change, you'll not break it and pending_restart will not be reset.

[princetos]

The issues are yet to be fix

[alexeyklyukin]

@princetos did you try it with https://github.com/zalando/patroni/commit/e939304001dbce26b895a0cc22b72722b14e5ecd ? Could you elaborate why do you think that change does not fix it or share any logs demonstrating the issue is still there with that change applied.