test_0925.sh

application/octet-stream

Download

#!/bin/bash

port_primary=5431
port_secondary=5432
port_subscriber=5433

echo '=========='
echo '=Clean up='
echo '=========='

pg_ctl stop -D data_primary
pg_ctl stop -D data_secondary
pg_ctl stop -D data_subscriber

rm -rf data_* *log

echo '======================='
echo '=Set up primary server='
echo '======================='

initdb -D data_primary -U postgres

cat << EOF >> data_primary/postgresql.conf
wal_level = logical
port = $port_primary
standby_slot_names = 'physical'
synchronize_slot_names = 'sub'
log_replication_commands = 'on'
EOF

cat << EOF >> data_primary/pg_hba.conf
host all,replication all 0.0.0.0/0 trust
EOF

pg_ctl -D data_primary start -w -l primary.log
psql -U postgres -p $port_primary -c "CREATE TABLE tbl (id int primary key);"
psql -U postgres -p $port_primary -c "INSERT INTO tbl VALUES (1)"
psql -U postgres -p $port_primary -c "CREATE PUBLICATION pub FOR ALL TABLES"
psql -U postgres -p $port_primary -c "SELECT * FROM pg_create_physical_replication_slot('physical');"

echo '========================='
echo '=Set up secondary server='
echo '========================='

pg_basebackup -D data_secondary -U postgres -p $port_primary

cat << EOF >> data_secondary/postgresql.conf
port = $port_secondary
primary_conninfo = 'user=postgres port=$port_primary application_name=secondary dbname=postgres'
primary_slot_name = 'physical'
EOF

cat << EOF >> data_secondary/standby.signal
EOF

pg_ctl -D data_secondary start   -w -l secondary.log

psql -U postgres -p $port_primary -c "SELECT pg_log_standby_snapshot();"
psql -U postgres -p $port_secondary -c "SELECT * FROM pg_create_physical_replication_slot('physical');"

echo '==================='
echo '=Set up subscirber='
echo '==================='

initdb -U postgres -D data_subscriber

cat << EOF >> data_subscriber/postgresql.conf
port = $port_subscriber
wal_level = logical
EOF

pg_ctl start -D data_subscriber -l subscriber.log

psql -U postgres -p $port_subscriber -c "CREATE TABLE tbl (id int primary key);"
psql -U postgres -p $port_subscriber -c "CREATE SUBSCRIPTION sub CONNECTION 'user=postgres dbname=postgres port=$port_primary' PUBLICATION pub WITH (copy_data = on)"

sleep 1s

pg_ctl -D data_secondary reload
sleep 1s
psql -U postgres -p $port_secondary -c "SELECT * FROM pg_replication_slots;"

sleep 2s
# promote...

pg_ctl -D data_primary stop -w
pg_ctl -D data_secondary promote -w
psql -U postgres -p $port_subscriber -c "ALTER SUBSCRIPTION sub DISABLE"
pg_ctl -D data_subscriber restart -l subscriber.log
psql -U postgres -p $port_secondary -c "INSERT INTO tbl VALUES (generate_series(21, 30))"
psql -U postgres -p $port_subscriber -c "ALTER SUBSCRIPTION sub CONNECTION 'user=postgres dbname=postgres port=$port_secondary'"
psql -U postgres -p $port_subscriber -c "ALTER SUBSCRIPTION sub ENABLE"