Skip to content

Instantly share code, notes, and snippets.

@kmoppel kmoppel/master_setup.sh
Last active Mar 15, 2019

Embed
What would you like to do?
Postgres master-replica setup to test different synchronous_commit values
PATH=/usr/local/pgsql/bin:$PATH
MASTER_PORT=5432
REPLICA_IP=172.31.28.169
function append_param_to_config {
OPTS=$(cat <<HERE
${1}
HERE
)
echo $OPTS >> master/postgresql.conf
}
echo "running initdb..."
initdb --locale=en_US.UTF-8 --encoding UTF8 -D master &>/dev/null
if [ $? -ne 0 ]; then
echo "failed to run initdb. exit"
exit 1
fi
echo "setting master config params..."
append_param_to_config "port=${MASTER_PORT}"
append_param_to_config "listen_addresses='*'"
append_param_to_config "shared_buffers=8GB"
append_param_to_config "checkpoint_timeout=1h"
append_param_to_config "max_wal_size=10GB"
append_param_to_config "max_wal_senders=2"
append_param_to_config "wal_level=replica"
append_param_to_config "hot_standby=on"
append_param_to_config "autovacuum=off"
echo $(cat <<HERE
host replication all ${REPLICA_IP}/32 trust
HERE
) >> master/pg_hba.conf
echo "starting master..."
pg_ctl -l /dev/null -D master -w start
echo "started"
PATH=/usr/local/pgsql/bin:$PATH
MASTER_IP=172.31.28.170
MASTER_PORT=5432
REPLICA_PORT=5432
echo "building replica with pg_basebackup..."
pg_basebackup -h ${MASTER_IP} -p ${MASTER_PORT} -X s -R -D replica1
if [ $? -ne 0 ]; then
echo "failed to run pg_basebackup. exit"
exit 1
fi
sed -i "s/primary_conninfo = '/primary_conninfo = 'application_name=replica1 /g" replica1/recovery.conf
sed -i "s/port=${MASTER_PORT}/port=${REPLICA_PORT}/g" replica1/postgresql.conf
echo "done..."
pg_ctl -l /dev/null -D replica1 start
if [ $? -ne 0 ]; then
echo "failed to run start replica. exit"
exit 1
fi
echo "replica started..."
PATH=/usr/local/pgsql/bin:$PATH
MASTER_PORT=5432 # assumes being run on master
TEST_DURATION_SECONDS=300
SCALE=100
CLIENTS=8
THREADS=2
function recreate_testdb_and_init_schema {
echo "recreating testdb..."
psql -p $MASTER_PORT -d postgres -c "drop database test"
psql -p $MASTER_PORT -d postgres -c "create database test"
echo "initializing pgbench with scale $SCALE..."
pgbench -i -s $SCALE -p $MASTER_PORT test &>/dev/null
}
function restart_and_run_test() {
echo "restarting..."
pg_ctl -l /dev/null -D master -w restart
echo "done"
echo "starting pgbench: pgbench -T $TEST_DURATION_SECONDS -p $MASTER_PORT -c$CLIENTS -j$THREADS --protocol=prepared -n test"
pgbench -T $TEST_DURATION_SECONDS -p $MASTER_PORT -c$CLIENTS -j$THREADS --protocol=prepared -n test
}
function append_param_to_config {
OPTS=$(cat <<HERE
${1}
HERE
)
echo $OPTS >> master/postgresql.conf
}
recreate_testdb_and_init_schema
echo ""
echo "* running test in ASYNC mode with: synchronous_commit=on (default)"
restart_and_run_test
recreate_testdb_and_init_schema
append_param_to_config "synchronous_commit=off"
echo ""
echo "* running test in ASYNC mode with: synchronous_commit=off"
restart_and_run_test
# switch to synchronous mode
append_param_to_config "synchronous_standby_names='replica1'"
recreate_testdb_and_init_schema
append_param_to_config "synchronous_commit=on"
echo ""
echo "* running test in SYNC mode with: synchronous_commit=on"
restart_and_run_test
recreate_testdb_and_init_schema
append_param_to_config "synchronous_standby_names='replica1'"
append_param_to_config "synchronous_commit=remote_write"
echo ""
echo "* running test in SYNC mode with: synchronous_commit=remote_write"
restart_and_run_test
recreate_testdb_and_init_schema
append_param_to_config "synchronous_commit=remote_apply"
echo ""
echo "* running test in SYNC mode with: synchronous_commit=remote_apply"
restart_and_run_test
#pg_ctl -D master stop
#pg_ctl -D replica1 stop
#rm -rf master replica1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.