test.sh

application/octet-stream

Download

#!/bin/bash

port_pub=5431
port_sub=5432
bindir=/usr/local/pgsql/bin/

echo 'Clean up'

pg_ctl stop -D data_N2
pg_ctl stop -D data_N1
pg_ctl stop -D data_N3

rm -r data_N1 data_N2 data_N3 *log

echo 'Set up'

initdb -D data_N1 -U postgres
initdb -D data_N2 -U postgres
initdb -D data_N3 -U postgres

cat << EOF >> data_N1/postgresql.conf
wal_level = logical
port = $port_pub
EOF

cat << EOF >> data_N2/postgresql.conf
wal_level = logical
port = $port_sub
EOF

cat << EOF >> data_N3/postgresql.conf
wal_level = logical
port = $port_sub
EOF

pg_ctl -D data_N1 start -w -l N1.log
pg_ctl -D data_N2 start -w -l N2.log

# Create publication

(
    echo -e "CREATE TABLE tbl AS SELECT generate_series(1,10) AS a;"
    echo -e "CREATE PUBLICATION pub FOR ALL TABLES;"
) | psql -U postgres -p $port_pub

# Create subscription with copy_data = off

(
    echo -e "CREATE TABLE tbl (a int);"
    echo -e "CREATE SUBSCRIPTION sub CONNECTION 'user=postgres port=$port_pub' PUBLICATION pub WITH (copy_data=off, synchronous_commit=remote_apply);"
) | psql -U postgres -p $port_sub

sleep 1s

# Enable synchronous replication to ensure being synchronized

cat << EOF >> data_N1/postgresql.conf
synchronous_standby_names = 'sub'
EOF
pg_ctl reload -D data_N1

# Insert more data

(
    echo -e "INSERT INTO tbl VALUES (generate_series(11, 20));"
) | psql -U postgres -p $port_pub

# Do pg_upgrade, but would fail

pg_ctl stop -D data_N2
pg_upgrade -b $bindir -B $bindir -d data_N2/ -D data_N3/ -r -U postgres