PostgreSQL logical replication + pg_basebackup test

logical-replication-test.sh

#!/bin/sh -ex
#export PATH=/usr/pgsql-11/bin:$PATH

pg_ctl stop -D /tmp/master || echo "ok"
pg_ctl stop -D /tmp/slave || echo "ok"
rm -rf /tmp/master
rm -rf /tmp/slave

# setup master
initdb -D /tmp/master
sed -i 's|#port = 5432|port=15432|g' /tmp/master/postgresql.conf
sed -i 's|#wal_level = replica|wal_level = logical|g' /tmp/master/postgresql.conf
sed -i 's|#wal_keep_segments = 0|wal_keep_segments = 1000|g' /tmp/master/postgresql.conf
pg_ctl  -D /tmp/master start -w
createdb -p 15432
psql -p 15432 <<SQL
CREATE TABLE large_test (num1 bigint, num2 double precision, num3 double precision);

INSERT INTO large_test (num1, num2, num3)
SELECT round(random()*10), random(), random()*142
FROM generate_series(1, 200000) s(i);
SQL

# setup slave
initdb -D /tmp/slave
sed -i 's|#port = 5432|port=25432|g' /tmp/slave/postgresql.conf
pg_ctl  -D /tmp/slave start -w
createdb -p 25432
psql -p 25432 <<SQL
CREATE TABLE large_test (num1 bigint, num2 double precision, num3 double precision);
SQL

# prepare pub
psql -p 15432 <<SQL
    CREATE PUBLICATION pub_test FOR TABLE large_test;
SQL

# init sub
psql -p 25432 <<SQL
    CREATE SUBSCRIPTION sub_test CONNECTION 'host=/tmp port=15432 dbname=%username% application_name=sub_test' PUBLICATION pub_test;
SQL

# wait initial sync
while [[ `psql -p 25432 -XAt -c "select count(*) from pg_subscription_rel where srsubstate NOT IN ('r', 's')"` != [0] ]]; do
    sleep 1
    echo "wait sync"
done
echo "sync complete"

rm -rf /tmp/slave_backup
pg_basebackup -p 25432 -D /tmp/slave_backup --checkpoint=fast


# kill slave and change data on master
kill -9 $(cat /tmp/slave/postmaster.pid | head -n 1)
psql -p 15432 <<SQL
INSERT INTO large_test (num1, num2, num3)
SELECT round(random()*10), random(), random()*142
FROM generate_series(1, 10000) s(i);
SQL

# start slave from backup
rm -rf /tmp/slave
mv /tmp/slave_backup /tmp/slave
pg_ctl start -D /tmp/slave -w

# wait sync on replica
while [[ `psql -p 15432 -XAt -c "SELECT pg_current_wal_lsn() <= replay_lsn FROM pg_stat_replication WHERE application_name = 'sub_test'"` != 't' ]]; do
    sleep 1
    echo "wait sync"
done
echo "sync complete"

# insert new data
psql -p 15432 <<SQL
INSERT INTO large_test (num1, num2, num3)
SELECT round(random()*10), random(), random()*142
FROM generate_series(1, 1000) s(i);
SQL

# wait sync on replica
while [[ `psql -p 15432 -XAt -c "SELECT pg_current_wal_lsn() <= replay_lsn FROM pg_stat_replication WHERE application_name = 'sub_test'"` != 't' ]]; do
    sleep 1
    echo "wait sync"
done
echo "sync complete"

# data on logical publisher:
psql -p 15432 <<SQL
    select count(*) from large_test;
SQL

# no new data on subscriber:
psql -p 25432 <<SQL
    select count(*) from large_test;
SQL