Hi, When I read the following documentation related to the "synchronized_standby_slots", I misunderstood that data loss would not occur in the case of synchronous physical replication. However, this is incorrect (see reproduce.txt).
> Note that in the case of asynchronous replication, there remains a risk of > data loss for transactions committed on the former primary server but have > yet to be replicated to the new primary server. https://www.postgresql.org/docs/17/logical-replication-failover.html Am I missing something? IIUC, could you change the documentation as suggested in the attached patch? I also believe it would be better to move the sentence to the next paragraph because the note is related to "synchronized_standby_slots.". Regards, -- Masahiro Ikeda NTT DATA CORPORATION
v1-0001-fix-documentation-related-to-synchronized_standby.patch
Description: v1-0001-fix-documentation-related-to-synchronized_standby.patch
# PREPARE
# 1. create primary
# initdb -D primary
# postgresql.conf
port = 5432
wal_level = logical
# disable "synchronized_standby_slots"
# pg_ctl start -D primary
# psql -p 5432
psql=# SELECT pg_create_physical_replication_slot('pslot1');
psql=# CREATE TABLE t1 (a int PRIMARY KEY);
psql=# INSERT INTO t1 VALUES (1), (2), (3);
psql=# CREATE PUBLICATION pub1 FOR ALL TABLES;
# 2. create standby
# pg_basebackup -D standby -R -c fast
# postgresql.conf
wal_level = logical
port = 5433
cluster_name = 'sby1'
hot_standby = on
primary_conninfo = 'port=5432 application_name=sby1 dbname=postgres'
recovery_target_timeline = 'latest'
primary_slot_name = 'pslot1'
sync_replication_slots = on
hot_standby_feedback = on
# 3. create subscriber
# initdb -D subscriber
# postgresql.conf
port = 6432
wal_level = 'logical'
# 4. enable sync physical replication
echo "synchronous_standby_names = 'sby1'" >> primary/postgresql.conf
pg_ctl restart -D primary
pg_ctl start -D standby
# 5. create subscription
# pg_ctl start -D subscriber
# psql -p 6432 # subsriber
psql=# CREATE TABLE t1 (a int PRIMARY KEY);
psql=# CREATE SUBSCRIPTION sub1 CONNECTION 'port=5432 dbname=postgres'
PUBLICATION pub1 WITH (failover);
# TEST
# 1. stop standby to simulate NW down and so on
pg_ctl stop -D standby
# 2. insert new data
psql -p 5432 -c "INSERT INTO t1 VALUES (4);" # wait because the standby was
stopped
# 3. check subscriber
psql -p 6432 -c "SELECT * FROM t1;" # It has '4' which standby doesn't have
# 4. stop primary
pg_ctl stop -D primary -m immediate
# 5. failover
pg_ctl start -D standby
psql -p 5433 -c "SELECT pg_promote();"
psql -p 6432 -c "ALTER SUBSCRIPTION sub1 CONNECTION 'port=5433'" # subscriber
# 5. check if data loss occurs even in the case of synchronous physical
replication
psql -p 5433 -c "SELECT COUNT(*) FROM t1;" # standby: 3
psql -p 6432 -c "SELECT COUNT(*) FROM t1;" # subscriber: 4
