test_1003.sh

application/octet-stream

Download

#!/bin/bash

port_primary=5431
port_secondary=5432
port_subscriber=5433
port_subscriber2=5434

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

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

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'
hot_standby_feedback = on
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 2s
psql -U postgres -p $port_secondary -c "SELECT * FROM pg_replication_slots;"

echo 'Moving lsns on primary so that slot-creation on secondary is successful'
psql -U postgres -p $port_primary -c "INSERT INTO tbl VALUES (2)"
psql -U postgres -p $port_primary -c "INSERT INTO tbl VALUES (3)"
psql -U postgres -p $port_primary -c "INSERT INTO tbl VALUES (4)"

#Allow sometime for successful slot creation on standby
sleep 10s

echo '===================='
echo '=Set up subscirber2='
echo '===================='

initdb -U postgres -D data_subscriber2

cat << EOF >> data_subscriber2/postgresql.conf
port = $port_subscriber2
wal_level = logical
EOF

pg_ctl start -D data_subscriber2 -l subscriber2.log

psql -U postgres -p $port_subscriber2 -c "CREATE TABLE tbl (id int primary key);"
psql -U postgres -p $port_subscriber2 -c "CREATE SUBSCRIPTION sub CONNECTION 'user=postgres dbname=postgres port=$port_secondary' PUBLICATION pub WITH (create_slot = off, slot_name = 'sub', copy_data = on)"