PostgreSQL 13.4: Setting synchronous_commit using ALTER SYSTEM doesn't change it

Question

We're currently building a PostgreSQL cluster using PostgreSQL 13.4, Patroni, Consul and Confd. The setup was successful (Patroni cluster is replicating with one master and one replica), and we are trying to do some benchmarking using pgbench with different replication settings, specifically synchronous_commit.

The documentation makes it clear that valid values for synchronous_commit on the Master are:

• off
• local
• remote_write
• on
• remote_apply

with increasing safety regarding network failures and crashes (in broad terms). So the idea was to generate some benchmarks (using pgbench) with each of the settings. As explained by the documentation, the only valid values for synchronous_commit are on and off when synchronous_standby_names is empty. We have synchronous_standby_names as *.

However, when changing the value of synchronous_commit, it doesn't seem to change anything. Example:

gobsux59:/etc/patroni # su - postgres
postgres@gobsux59:~> psql
psql (13.4)
Type "help" for help.

postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 off
(1 row)

postgres=# alter system set synchronous_commit = 'local';
ALTER SYSTEM
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 off
(1 row)

postgres=# alter system set synchronous_commit = 'remote_write';
ALTER SYSTEM
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 off
(1 row)

postgres=# alter system set synchronous_commit = 'on';
ALTER SYSTEM
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 off
(1 row)

postgres=# alter system set synchronous_commit = 'remote_apply';
ALTER SYSTEM
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 off
(1 row)

postgres=#

There's always a chance that some system interferes here, so I checked if synchronous_commit is listed anywhere else in the applicable configuration files:

postgres@gobsux59:~/data> grep synchronous_commit *
postgresql.auto.conf:synchronous_commit = 'remote_apply'
postgresql.base.conf:#synchronous_commit = on           # synchronization level;
postgresql.base.conf.backup:#synchronous_commit = on            # synchronization level;

You can see that the postgresql.auto.conf (configuration file that is written automatically by PostgreSQL based on ALTER SYSTEM statements) contains the value set last using ALTER SYSTEM, yet when querying the database a different value is displayed.

I have looked into the possibility that there are different configuration scopes where I overwrite another scope than the one I am querying, but it doesn't seem to be the case (although I recognize that many settings can be set per-connection, per-transaction, per-cluster and so on).

I'm not sure how to proceed understanding this issue further, and I would appreciate any assistance.

Answer 1

All ALTER SYSTEM does is to edit postgresql.auto.conf in the data directory.

To make the changes take affect, you have to reload the server:

SELECT pg_reload_conf();

This can be done while the server is running and won't disrupt operations. It tells PostgreSQL to load the new configuration parameters from the files.