test-logical-replication-recovery.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
pgbench -i -s 10 -p 15432

# 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
psql -p 25432 <<SQL
CREATE TABLE public.pgbench_accounts (
    aid integer NOT NULL,
    bid integer,
    abalance integer,
    filler character(84)
)
WITH (fillfactor='100');
ALTER TABLE ONLY public.pgbench_accounts
    ADD CONSTRAINT pgbench_accounts_pkey PRIMARY KEY (aid);
SQL

# prepare pub
psql -p 15432 <<SQL
    CREATE PUBLICATION pub_pgbench FOR TABLE pgbench_accounts;
SQL

# init sub
psql -p 25432 <<SQL
    CREATE SUBSCRIPTION sub_pgbench CONNECTION 'host=/tmp port=15432 dbname=postgres' PUBLICATION pub_pgbench;
SQL

while [[ `psql -p 25432 -Atc "select count(*) from pg_subscription_rel where srsubstate <> 'r'"` != [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)
pgbench -i -s 10 -p 15432

# 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 25432 -Atc "select count(*) from pg_subscription_rel where srsubstate <> 'r'"` != [0] ]]; do
    sleep 1
    echo "wait sync"
done
echo "sync complete"

# insert new data
pgbench -p 15432 -i -s 50

# wait sync on replica
while [[ `psql -p 25432 -Atc "select count(*) from pg_subscription_rel where srsubstate <> 'r'"` != [0] ]]; do
    sleep 1
    echo "wait sync"
done
echo "sync complete"

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

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