cascade_3node_setup_with_slots.sh

#!/bin/bash

#
# This script tests a case of cascading replication.
#
# Creating system,:
#   node1-(physical replication)->node2-(physical replication)->node3
#

# Stop instances
sudo pkill -9 postgres
pg_ctl stop -D node1
pg_ctl stop -D node2
pg_ctl stop -D node3

# Remove old files
rm -rf node1
rm -rf node2
rm -rf node3
rm log_node2 log_node1 log_node3

# Initialize primary
initdb -D node1

echo "wal_level = logical" >> node1/postgresql.conf
echo "max_replication_slots=10" >> node1/postgresql.conf

pg_ctl -D node1 -l log_node1 start

psql -d postgres -c "CREATE DATABASE db1";
psql -d db1 -c "CHECKPOINT";

sleep 3

# Initialize standby
pg_basebackup -h 127.0.0.1 -X stream -S slot1 -C -v -R -D node2
echo "Port=9000" >> node2/postgresql.conf

pg_ctl -D node2 -l log_node2 start

# Initialize another standby
pg_basebackup -h 127.0.0.1 -X stream -p 9000 -S slot2 -C -v -R -D node3
echo "Port=9001" >> node3/postgresql.conf
pg_ctl -D node3 -l log_node3 start

# Insert a tuple to primary
psql -d db1 -c "CREATE TABLE c1(a int)";
psql -d db1 -c "Insert into c1 Values(2)";
sleep 3

# And verify it can be seen on another standby
psql -d db1 -p 9001 -c "Select * from c1";


pg_createsubscriber -D node3/ -P "host=localhost port=5432 dbname=postgres" -d postgres -d db1 -p 9001 -r -v
pg_ctl -D node3 -l log_node3 start
psql -d db1 -c "INSERT INTO c1 VALUES(3)";
psql -d db1 -p 9001 -c "SELECT * FROM c1";