test.sh

application/x-shellscript

Download

#!/usr/bin/bash

OUTDIR=$(date +%s)
DATADIR=/home/user/tmp/data-checksums
DATADIR2=/home/user/tmp/data-checksums-standby

mkdir $OUTDIR

killall -9 postgres
rm -Rf $DATADIR $DATADIR2

# init primary
pg_ctl -D $DATADIR init
echo 'wal_level = logical' >> $DATADIR/postgresql.conf 2>&1
echo "log_line_prefix = '%n %m [%p] [%b:%a] [%c:%l] [%s] [%v/%x] '" >> $DATADIR/postgresql.conf 2>&1
echo "log_min_duration_statement = 0" >> $DATADIR/postgresql.conf 2>&1
echo "checkpoint_timeout = '1min'" >> $DATADIR/postgresql.conf 2>&1

pg_ctl -D $DATADIR -l $OUTDIR/pg-primary.log start

# init a replica
pg_basebackup -D $DATADIR2 -c fast -R -C -S replica
echo "port = 5433" >> $DATADIR2/postgresql.conf 2>&1
pg_ctl -D $DATADIR2 -l $OUTDIR/pg-standby.log start


# small test
sleep=10
m=fast
s=enabled

# 100 loops of the primary restarts
for r in $(seq 1 200); do

	x=$((RANDOM % 10))
	db="test_$x"

	scale=$((RANDOM % 40 + 10))

	dropdb --if-exists $db
	createdb $db
	pgbench -i -s $scale $db

	# run pgbench in the background
	#echo `date` "loop $r pgbench scale $cale"
	echo `date` "loop $r pgbench"

	pgbench -c 1 -P 1 -T 3600 $db >> $OUTDIR/pgbench.log 2>&1 &

	# sleep for a bit
	x=$((RANDOM % sleep + 1))
	echo `date` "loop $r sleeping for $x seconds"
	sleep $x

	# start the checksums change
	if [ "$s" == "disabled" ]; then

		fast="false"
		x=$((RANDOM % sleep + 1))
		if [ "$x" == "0" ]; then
			fast="true"
		fi

		echo `date` "loop $r enabling checksums fast $fast"
		psql $db -c "select pg_enable_data_checksums(fast := $fast)"
		s="enabled"
	else
		echo `date` "loop $r disabling checksums"
		psql $db -c "select pg_disable_data_checksums()"
		s="disabled"
	fi

	# sleep for a bit
	x=$((RANDOM % sleep + 1))
	echo `date` "loop $r sleeping for $x seconds"
	sleep $x

	psql $db -c "SELECT * FROM pg_stat_progress_data_checksums"

	# stop the primary in some way
	m=$((RANDOM % 3))

	if [ "$m" == "0" ]; then
		pg_ctl -D $DATADIR -m immediate stop
	elif [ "$m" == "1" ]; then
		pg_ctl -D $DATADIR -m fast stop
	fi

	# stop the primary in some way
	m=$((RANDOM % 3))

	if [ "$m" == "0" ]; then
		pg_ctl -D $DATADIR2 -m immediate stop
	elif [ "$m" == "1" ]; then
		pg_ctl -D $DATADIR2 -m fast stop
	fi

	# start the primary again
	echo `date` "loop $r start primary"
	pg_ctl -D $DATADIR -l $OUTDIR/pg-primary.log start

	# start the standy again
	echo `date` "loop $r start standby"
	pg_ctl -D $DATADIR2 -l $OUTDIR/pg-standby.log start

	# run pgbench in the background
	echo `date` "loop $r pgbench"
	pgbench -c 1 -P 1 -T 3600 $db >> $OUTDIR/pgbench.log 2>&1 &

	# sleep for a bit
	x=$((RANDOM % sleep + 1))
	echo `date` "loop $r sleeping for $x seconds"
	sleep $x

	# start the checksums change (again, to restart the workers)
	if [ "$s" == "enabled" ]; then

		fast="false"
		x=$((RANDOM % sleep + 1))
		if [ "$x" == "0" ]; then
			fast="true"
		fi

		echo `date` "loop $r enabling checksums fast $fast"
		psql $db -c "select pg_enable_data_checksums(fast := $fast)"
	else
		echo `date` "loop $r disabling checksums"
		psql $db -c "select pg_disable_data_checksums()"
	fi

	# wait for the checksums to get enabled/disabled
	echo `date` "loop $r waiting for checksums to change in the instance"
	while /bin/true; do

		c=$(psql -t -A $db -c "SELECT setting FROM pg_catalog.pg_settings WHERE name = 'data_checksums'")

		psql $db -c "SELECT * FROM pg_stat_progress_data_checksums"

		if [ "$s" == "enabled" ] && [ "$c" == "on" ]; then
			break;
		elif [ "$s" == "disabled" ] && [ "$c" == "off" ]; then
			break;
		fi

		echo `date` "loop $r checksum state: $c (sleeping)"
		sleep 1

	done

	# stop the primary in some way
	m=$((RANDOM % 2))

	if [ "$m" == "0" ]; then
		pg_ctl -D $DATADIR -m immediate stop
	else
		pg_ctl -D $DATADIR -m fast stop
	fi

	if [ "$s" == "enabled" ] && [ "$m" != "0" ]; then
		pg_checksums -c $DATADIR
	fi

	pg_ctl -D $DATADIR -l $OUTDIR/pg-primary.log start


	# stop the standby in some way
	m=$((RANDOM % 2))

	if [ "$m" == "0" ]; then
		pg_ctl -D $DATADIR2 -m immediate stop
	else
		pg_ctl -D $DATADIR2 -m fast stop
	fi

	if [ "$s" == "enabled" ] && [ "$m" != "0" ]; then
		pg_checksums -c $DATADIR2
	fi

	pg_ctl -D $DATADIR2 -l $OUTDIR/pg-standby.log start

	# wait for standy to catch up
	while /bin/true; do

		d=$(psql -t -A postgres -c "select (pg_current_wal_lsn() - replay_lsn) from pg_stat_replication")
		x=$(psql -t -A postgres -c "select (pg_current_wal_lsn() - replay_lsn) < 16384 from pg_stat_replication")

		if [ "$x" == "t" ]; then
			break
		fi

		echo `date` "loop $r waiting for standby to catch up ($d bytes)"
		sleep 1

	done

done