Postgres master-replica setup to test different synchronous_commit values

master_setup.sh

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"

replica_setup.sh

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..."

run_pgbench.sh

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