test.sh

application/octet-stream

Download

#!/bin/bash

pub_port=7654
sub_port=7655

echo $PATH
pg_ctl --version

pg_ctl -D data_pub stop -m immediate
pg_ctl -D data_sub stop -m immediate
rm -rf data_pub data_sub  sub.log pub.log

initdb -D data_pub -U postgres
initdb -D data_sub -U postgres

cat << EOF >> data_pub/postgresql.conf
wal_level = logical
port = $pub_port
autovacuum = off
wal_sender_timeout = 30s
wal_receiver_timeout = 30s
EOF

cat << EOF >> data_sub/postgresql.conf
wal_level = logical
port = $sub_port
autovacuum = off
wal_sender_timeout = 30s
wal_receiver_timeout = 30s
#log_min_messages = debug1
#log_line_prefix = '%m [%p] %b '
#log_statement = all

EOF

pg_ctl -D data_pub start -l pub.log
pg_ctl -D data_sub start -l sub.log

psql -d postgres -Upostgres -p $pub_port -c "create table tbl_pub (a int primary key, b text);"
psql -d postgres -Upostgres -p $pub_port -c "create table tbl_nopub (a int primary key, b text);"
echo `date` ":  start insert data"
psql -d postgres -Upostgres -p $pub_port -c "insert into tbl_nopub SELECT i, 'abcdefg' FROM generate_series(1, 50000000) s(i);"
#psql -d postgres -Upostgres -p $pub_port -c "insert into tbl_nopub SELECT i, 'abcdefg' FROM generate_series(1, 500000) s(i);"
echo `date` ":  end insert data"

echo `date` ":  start create materialized view"
psql -d postgres -Upostgres -p $pub_port -c "create materialized view mate_nopub as select * from tbl_nopub;"
echo `date` ":  end create materialized view"

psql -d postgres -Upostgres -p $pub_port -c "create publication pub for table tbl_pub;"

sleep 3

psql -d postgres -Upostgres -p $sub_port -c "create table tbl_pub (a int primary key, b text);"
psql -d postgres -Upostgres -p $sub_port -c "create subscription sub connection 'dbname=postgres port=$pub_port user=postgres ' publication pub;"

echo synchronous_standby_names = \'any 1\(sub\)\' >> data_pub/postgresql.conf
pg_ctl -D data_pub/ -l pub.log  reload

#exit 0

echo `date` ":  refresh materialized view in publisher-side"
psql -d postgres -Upostgres -p $pub_port -c "refresh materialized view mate_nopub;"
echo `date` ":  done"