test_r.sh

application/octet-stream

Download

#!/bin/bash

#####

SLOT_NAME=test
PLUGIN_NAME=pgoutput
NUM_PART=5000
NUM_LOOP=5

#####

# Cleanup previous result

pg_ctl stop -D data
rm -rf data logfile

# Initialize an instance

initdb -D data -U postgres -c wal_level=logical

# Start the instance
pg_ctl -D data -l logfile start

# Create a root and leaf tables
psql -U postgres -c "CREATE TABLE tbl_r (id int) PARTITION BY HASH (id);"

for i in `seq 1 $NUM_PART`
do
    psql -U postgres -c "CREATE TABLE tbl_p${i} PARTITION OF tbl_r FOR VALUES WITH (MODULUS $NUM_PART, REMAINDER $(($i-1)));"
done

# Create another table which is not hierarchical one
psql -U postgres -c "CREATE TABLE tbl_XXX (id int);"

# Create publications and inclide tables one by one
(
    echo "CREATE PUBLICATION pub1 FOR TABLE tbl_r;"
    echo "CREATE PUBLICATION pub2 FOR TABLE tbl_XXX;"
) | psql -U postgres

# Create a replication slot
psql -U postgres -c "SELECT pg_create_logical_replication_slot('$SLOT_NAME', '$PLUGIN_NAME');"

# Setup histories for the benchmark
(
    # INSERT tuples to all the leaf tables
    echo "SELECT 'INSERT INTO tbl_r VALUES (' || generate_series(1,$NUM_PART) || ');' \gexec"

    # Rename twice
    echo "ALTER PUBLICATION pub2 RENAME TO pub2_renamed;"
    echo "ALTER PUBLICATION pub2_renamed RENAME TO pub2;"

    # INSERT tuples to all the leaf tables again
    echo "SELECT 'INSERT INTO tbl_r VALUES (' || generate_series(1,$NUM_PART) || ');' \gexec"
) | psql -U postgres

for i in `seq 1 $NUM_LOOP`
do
    pg_ctl -D data -l logfile restart
    (time psql -U postgres -c "SELECT count(*) FROM pg_logical_slot_peek_binary_changes('$SLOT_NAME', NULL, NULL, 'proto_version', '4', 'publication_names', 'pub1');") &> test_${i}.dat
done