test_1030.sh

application/octet-stream

Download

#!/bin/bash

port_publisher=5431
port_subscriber=5433
pg12bin=~/older/pg12/bin
pg12lib=~/older/pg12/lib
pg14bin=~/older/pg14/bin
pg14lib=~/older/pg14/lib


echo '=========='
echo '=Clean up='
echo '=========='

pg_ctl stop -D data_publisher
pg_ctl stop -D data_subscriber

rm -rf data_* *log

echo '=================='
echo '=Set up publisher='
echo '=================='

$pg12bin/initdb -D data_publisher -U postgres

cat << EOF >> data_publisher/postgresql.conf
wal_level = logical
port = $port_publisher
EOF

$pg12bin/pg_ctl -D data_publisher start -w -l publisher.log

(
    # Publication does not have a target for now
    echo -e "CREATE PUBLICATION pub;"
    echo -e "SELECT 'CREATE TABLE tbl_' || generate_series(1, 120) || ' (id int PRIMARY KEY, value int);'; \gexec"
    # Adds initial tuples to all tables
    echo -e "SELECT 'INSERT INTO tbl_' || generate_series(1, 120) || ' VALUES (generate_series(1, 40), 5);'; \gexec"
) | LD_LIBRARY_PATH=$pg12lib $pg12bin/psql -U postgres -p $port_publisher

echo '==================='
echo '=Set up subscirber='
echo '==================='

$pg14bin/initdb -U postgres -D data_subscriber

cat << EOF >> data_subscriber/postgresql.conf
port = $port_subscriber
wal_level = logical
EOF

$pg14bin/pg_ctl start -D data_subscriber -l subscriber.log

(
    echo -e "SELECT 'CREATE TABLE tbl_' || generate_series(1, 120) || ' (id int PRIMARY KEY, value int);'; \gexec"
    echo -e "CREATE SUBSCRIPTION sub CONNECTION 'user=postgres dbname=postgres port=$port_publisher' PUBLICATION pub;"
    # Create a function for waiting
    echo -e "CREATE OR REPLACE FUNCTION wait_until_synced() RETURNS integer AS \$\$
DECLARE
     counter INTEGER := 1;
BEGIN
    WHILE EXISTS (SELECT 1 FROM pg_subscription_rel WHERE srsubstate != 'r') LOOP
        counter := counter + 1;
    END LOOP;

    RETURN counter;
END;
\$\$ LANGUAGE plpgsql;
"
) | LD_LIBRARY_PATH=$pg14lib $pg14bin/psql -U postgres -p $port_subscriber

echo '========================================'
echo '= Add tables [1, 40] to the publication='
echo '========================================'

(
    echo -e "SELECT 'ALTER PUBLICATION pub ADD TABLE tbl_' || generate_series(1, 40) || ';'; \gexec"
) | LD_LIBRARY_PATH=$pg12lib $pg12bin/psql -U postgres -p $port_publisher

(
    echo -e "ALTER SUBSCRIPTION sub REFRESH PUBLICATION;"
    echo -e "SELECT wait_until_synced();"
) | LD_LIBRARY_PATH=$pg14lib $pg14bin/psql -U postgres -p $port_subscriber


echo '========================================='
echo '= Add tables [41, 80] to the publication='
echo '========================================='

(
    echo -e "SELECT 'ALTER PUBLICATION pub ADD TABLE tbl_' || generate_series(41, 80) || ';'; \gexec"
) | LD_LIBRARY_PATH=$pg12lib $pg12bin/psql -U postgres -p $port_publisher

(
    echo -e "ALTER SUBSCRIPTION sub REFRESH PUBLICATION;"
    echo -e "SELECT wait_until_synced();"
) | LD_LIBRARY_PATH=$pg14lib $pg14bin/psql -U postgres -p $port_subscriber

echo '=========================================='
echo '= Add tables [81, 120] to the publication='
echo '=========================================='

(
    echo -e "SELECT 'ALTER PUBLICATION pub ADD TABLE tbl_' || generate_series(81, 120) || ';'; \gexec"
) | LD_LIBRARY_PATH=$pg12lib $pg12bin/psql -U postgres -p $port_publisher

(
    echo -e "ALTER SUBSCRIPTION sub REFRESH PUBLICATION;"
    echo -e "SELECT wait_until_synced();"
) | LD_LIBRARY_PATH=$pg14lib $pg14bin/psql -U postgres -p $port_subscriber

echo '==================='
echo '= Add foreign keys='
echo '==================='

(
    echo -e "SELECT 'ALTER TABLE tbl_' || generate_series(1, 119) || ' ADD FOREIGN KEY (value) REFERENCES tbl_' || generate_series(2, 120) || '(id);'; \gexec"
) | LD_LIBRARY_PATH=$pg14lib $pg14bin/psql -U postgres -p $port_subscriber