subpub_copy.txt

#!/bin/bash

# Create two clusters with wal_level=logical, running at two different
# ports.
PGPORT_PUB=5432
PGPORT_SUB=5433
PGHOST=/tmp
PGROLE=postgres
PGDATABASE=postgres
# Make this number just large enough for COPY to last while DROP
# SUBSCRIPTION runs.
NUM_RECORDS=500000

# The key point is to drop subscription during the table sync worker for
# initial synchronization is running. So, we might need to sleep before
# dropping subscription. The tablesync COPY fails, causing the DROP
# SUBSCRIPTION to miss progress cleanup which is in shared memory.
psql -p $PGPORT_PUB -c"CREATE TABLE tbl(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_PUB -c"insert into tbl values (generate_series(1,${NUM_RECORDS}))"
psql -p $PGPORT_PUB -c"CREATE TABLE test_01(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_PUB -c"CREATE TABLE test_02(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_PUB -c"CREATE TABLE test_03(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_PUB -c"CREATE TABLE test_04(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_PUB -c"CREATE TABLE test_05(id INTEGER PRIMARY KEY)"

psql -p $PGPORT_PUB -c"insert into test_01 select * from tbl;"
psql -p $PGPORT_PUB -c"insert into test_02 select * from tbl;"
psql -p $PGPORT_PUB -c"insert into test_03 select * from tbl;"
psql -p $PGPORT_PUB -c"insert into test_04 select * from tbl;"
psql -p $PGPORT_PUB -c"insert into test_05 select * from tbl;"

psql -p $PGPORT_PUB -c"CREATE PUBLICATION pub FOR ALL TABLES;"
psql -p $PGPORT_PUB -c"SELECT * FROM pg_publication;"

psql -p $PGPORT_SUB -c"CREATE TABLE tbl(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_SUB -c"CREATE TABLE test_01(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_SUB -c"CREATE TABLE test_02(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_SUB -c"CREATE TABLE test_03(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_SUB -c"CREATE TABLE test_04(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_SUB -c"CREATE TABLE test_05(id INTEGER PRIMARY KEY)"
psql -p $PGPORT_SUB -c"CREATE SUBSCRIPTION sub CONNECTION 'host=${PGHOST} port=${PGPORT_PUB} user=${PGROLE} dbname=${PGDATABASE}' PUBLICATION pub;"
psql -p $PGPORT_SUB -c"SELECT * FROM pg_subscription;"

sleep 2

psql -p $PGPORT_SUB -c"select * from pg_replication_origin"
psql -p $PGPORT_SUB -c"select * from pg_replication_origin_status"

psql -p $PGPORT_SUB -c"drop subscription sub ;"

psql -p $PGPORT_SUB -c"select version()"
psql -p $PGPORT_SUB -c"select * from pg_replication_origin"
psql -p $PGPORT_SUB -c"select * from pg_replication_origin_status"