test_0827.sh

application/octet-stream

Download

#!/bin/bash

PORT_PUB=5431
PORT_SUB=5432

DATA_PUB=data_pub
DATA_SUB=data_sub

LOG_PUB=log.pub
LOG_SUB=log.sub

# Cleanup previous run

pg_ctl stop -D $DATA_PUB
pg_ctl stop -D $DATA_SUB

rm -rf $DATA_PUB $DATA_SUB $LOG_PUB $LOG_SUB

# initilize publisher

initdb -D $DATA_PUB -U postgres -c wal_level=logical -c port=$PORT_PUB

# define tables and a publication

pg_ctl start -D $DATA_PUB -l $LOG_PUB
(
    echo "CREATE PUBLICATION pub1 FOR ALL TABLES;"
    echo "CREATE TABLE foo (id int PRIMARY KEY);"
    echo "CREATE TABLE var (id int PRIMARY KEY);"
) | psql -U postgres -p $PORT_PUB

# setup subscriebr

pg_basebackup -U postgres -p $PORT_PUB -D $DATA_SUB

echo "port=$PORT_SUB" >> $DATA_SUB/postgresql.conf
pg_ctl start -D $DATA_SUB -l $LOG_SUB

# define a subscription
(
    echo "CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres user=postgres port=$PORT_PUB' PUBLICATION pub1 WITH (copy_data=false);"
) | psql -U postgres -p $PORT_SUB


(
    # define a trigger function for foo
    echo "
CREATE FUNCTION ban_foo() RETURNS trigger AS \$\$
BEGIN
        RAISE 'changing data in foo is prohibit';
END;
\$\$ LANGUAGE plpgsql;"

    # define a trigger for foo
    echo "
CREATE TRIGGER foo_trigger BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE
ON foo
EXECUTE FUNCTION ban_foo();"

    # do same things for var
    echo "
    CREATE FUNCTION ban_var() RETURNS trigger AS \$\$
BEGIN
        RAISE 'changing data in bar is prohibit';
END;
\$\$ LANGUAGE plpgsql;"

    echo "
CREATE TRIGGER var_trigger BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE
ON foo
EXECUTE FUNCTION ban_var();
"
) | psql -U postgres -p $PORT_SUB