reproduce.txt

text/plain

Download

# 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