On Thu, Jul 18, 2019 at 08:39:48AM -0400, Jesper Pedersen wrote:
> mkdir /tmp/wal
> initdb /tmp/pgsql
> pg_ctl -D /tmp/pgsql -l /tmp/logfile start
> psql postgres
> SELECT pg_create_physical_replication_slot('replica1');
> CREATE ROLE repluser WITH LOGIN REPLICATION PASSWORD 'replpass';
> \q
>
> synchronous_commit = on
> synchronous_standby_names = 'replica1'
>
> pg_ctl -D /tmp/pgsql -l /tmp/logfile restart
> pg_receivewal -D /tmp/wal -S replica1 --synchronous -h localhost -p 5432 -U
> repluser -W
> psql -c 'SELECT * FROM pg_stat_replication;' postgres
> psql -c 'SELECT * FROM pg_replication_slots;' postgres
> psql -c 'CREATE DATABASE test' postgres
>
> In what scenarios do you see 'on' working ?
Because the code says so, "on" is an alias for "remote_flush" (which
is not user-visible by the way):
src/include/access/xact.h:#define SYNCHRONOUS_COMMIT_ON
SYNCHRONOUS_COMMIT_REMOTE_FLUSH
And if you do that it works fine (pg_receivewal --synchronous runs in
the background and I created a dummy table):
=# SELECT application_name, sync_state, flush_lsn, replay_lsn FROM
pg_stat_replication;
application_name | sync_state | flush_lsn | replay_lsn
------------------+------------+-----------+------------
pg_receivewal | sync | 0/15E1F88 | null
(1 row)
=# set synchronous_commit to on ;
SET
=# insert into aa values (2);
INSERT 0 1
This part however is as expected, just blocking:
=# set synchronous_commit to remote_apply ;
SET
=# insert into aa values (3);
^CCancel request sent
WARNING: 01000: canceling wait for synchronous replication due to
user request
DETAIL: The transaction has already committed locally, but might not
have been replicated to the standby.
LOCATION: SyncRepWaitForLSN, syncrep.c:266
INSERT 0 1
--
Michael