v21_setup.sh

text/x-sh

Download

#!/bin/bash

##################
### Definition ###
##################
port_pub=6633
port_sub=6634

## prefix
PUB_PREFIX="$HOME/project/pg1/postgres/inst/bin"

## scale factor
SCALE=100

## pgbench init command
INIT_COMMAND="pgbench -i -U postgres postgres -s $SCALE"

SOURCE=$1

################
### clean up ###
################

./pg_ctl stop -D data_pub -w
./pg_ctl stop -D data_sub -w
rm -rf data* *log

#######################
### setup publisher ###
#######################

./initdb -D data_pub -U postgres
cat << EOF >> data_pub/postgresql.conf
port=$port_pub
# autovacuum = false
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
wal_level = logical
EOF

./pg_ctl -D data_pub start -w -l pub.log
${PUB_PREFIX}/$INIT_COMMAND -p $port_pub
./psql -U postgres -p $port_pub -c "CREATE PUBLICATION pub FOR ALL TABLES;"

#######################
### setup sublisher ###
#######################

./initdb -D data_sub -U postgres

cat << EOF >> data_sub/postgresql.conf
port=$port_sub
# autovacuum = false
autovacuum_naptime = '30s'
shared_buffers = '30GB'
max_wal_size = 20GB
min_wal_size = 10GB
track_commit_timestamp = on
#log_min_messages = DEBUG1
EOF

./pg_ctl -D data_sub start -w -l sub.log
./$INIT_COMMAND -p $port_sub

(
    echo "CREATE TABLE pgbench_pub_history (tid int,bid int,aid bigint,delta int,mtime timestamp,filler char(22));"
    echo "CREATE TABLE pgbench_pub_tellers (tid int not null primary key,bid int,tbalance int,filler char(84));"
    echo "CREATE TABLE pgbench_pub_accounts (aid bigint not null primary key,bid int,abalance int,filler char(84));"
    echo "CREATE TABLE pgbench_pub_branches (bid int not null primary key,bbalance int,filler char(88));"
) | ./psql -p $port_sub -U postgres

if [ $SOURCE = "head" ]
then
    ./psql -U postgres -p $port_sub -c "CREATE SUBSCRIPTION sub CONNECTION 'port=6633 user=postgres' PUBLICATION pub;"
else
    ./psql -U postgres -p $port_sub -c "CREATE SUBSCRIPTION sub CONNECTION 'port=6633 user=postgres' PUBLICATION pub WITH (retain_conflict_info = on);"
fi

# Wait until all the table sync is done                                                                                 

REMAIN="f"

while [ "$REMAIN" = "f" ]
do
    # Sleep a bit to avoid running the query too much                                                                   
    sleep 1s

    # Check pg_subscription_rel catalog. This query is ported from wait_for_subscription_sync()                         
    # defined in Cluster.pm.                                                                                            
    REMAIN=`./psql -qtA -U postgres -p $port_sub -c "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');"`

    # Print the result for the debugging purpose                                                                        
    echo $REMAIN
done