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.