test.sh

text/x-sh

Download

#!/bin/sh

P1="bin/psql -d postgres -p 5551 -e"
P2="bin/psql -d postgres -p 5552 -e"

echo "step-1: setup publisher"
bin/pg_ctl stop -D node1 -mi
bin/pg_ctl stop -D node2 -mi
rm -rf node1 node2
bin/initdb -D node1 -E UTF8 --no-locale -k
cat <<EOF >> node1/postgresql.conf
wal_level = logical
autovacuum = off
max_wal_size = 512MB
wal_sender_timeout = 1min
log_min_messages = debug2
port = 5551
EOF
bin/pg_ctl start -D node1

echo "step-2: setup subscriber"
bin/initdb -D node2 -E UTF8 --no-locale -k
cat <<EOF >> node2/postgresql.conf
wal_level = logical
wal_sender_timeout = '10 min'
#wal_receiver_status_interval = 0
port = 5552
EOF
bin/pg_ctl start -D node2
sleep 3

$P1 -c "select pg_current_wal_lsn()"
$P2 -c "select pg_current_wal_lsn()"

echo "step-3: create table"
$P1 -c "create table test (c int)"
$P2 -c "create table test (c int)"

echo "step-4: create publication"
$P1 -c "create publication test_pub for table test;"

echo "step-5: create subscription"
$P2 -c "create subscription test_sub connection 'dbname=postgres port=5551' publication test_pub with (copy_data = 'false');"
sleep 5

echo "step-6: Insert a row and create a checkpoint"
$P1 -c "insert into test values(1);"
$P1 -c "checkpoint;"
$P1 -c "select 'CHECKPOINT-1', pg_current_wal_lsn();"

echo "step-7: Run 4 concurrent transactions"
# client-1
cat <<EOF | $P1 &
begin;
insert into test values (1);
select 'should be 731', txid_current();
select pg_sleep(3);
commit;
EOF

# client-2
sleep 1
$P1 -c "checkpoint"
$P1 -c "select 'CHECKPOINT-2', pg_current_wal_lsn()"

# client-3
sleep 3
cat <<EOF | $P1 &
begin;
create table test2 (c int);
insert into test2 values(2);
insert into test values(1);
select 'should be 732', txid_current();
select pg_sleep(7);
commit;
EOF

# client-4
for i in `seq 1 6`
do
    # consume WAL files.
    $P1 -c "insert into test values(1);" > /dev/null 2>&1
    $P1 -c "select pg_switch_wal();" > /dev/null 2>&1
    sleep 0.5
done
$P1 -c "select 'after consuming WAL files', pg_current_wal_lsn()"

# client-2
$P1 -c "checkpoint"
$P1 -c "select 'CHECKPOINT-3', pg_current_wal_lsn()"
sleep 7 # wait for XID=732 to finish

echo "step-8: Run 4 concurrent transactions"
# client-1
cat <<EOF | $P1 &
begin;
create table test3 (c int);
insert into test3 values (3);
insert into test values (1);
select 'should be 739', txid_current();
select pg_sleep(15);
commit;
EOF

# client-2
sleep 0.5
for i in `seq 1 6`
do
    # consume WAL files.
    $P1 -c "insert into test values(1);" > /dev/null 2>&1
    $P1 -c "select pg_switch_wal();" > /dev/null 2>&1
    sleep 0.5
done

# client-3
sleep 3
$P1 -c "checkpoint"
$P1 -c "select 'CHECKPOINT-4', pg_current_wal_lsn()"

# client-4
sleep 3
cat <<EOF | $P1
begin;
insert into test values(1);
select 'should be 746', txid_current();
commit;
select pg_switch_wal();
EOF