:
# Test for VACUUM, single relation
# Relation size is NBuffers/XXX, see below and "insert" for size estimates.

#SHBUF       REL SIZE            APPROX # OF ROWS (INTEGER TYPE)

# 1. NBuffers/512.
#100GB s_b:  200 MB              5,760,000 rows
#20GB s_b:   40 MB               1,152,000 rows
#1GB s_b:    2 MB                57,600 rows
#128MB s_b:  0.25 MB             7,200 rows

# 2. NBuffers/256
#100GB s_b:  400 MB              11,520,000
#20GB s_b:   80 MB               2,304,000
#1GB s_b:    4 MB                115,200
#128MB s_b:  0.5 MB              14,400

# 3. NBuffers/128
#100GB s_b:  800 MB              23,040,000
#20GB s_b:   160 MB              4,608,000
#1GB s_b:    8 MB                230,400
#128MB s_b:  1 MB                28,800

# 4. NBuffers/64
#100GB s_b:  1600 MB             46,080,000
#20GB s_b:   320 MB              9,216,000
#1GB s_b:    16 MB               46,800
#128MB s_b:  2 MB                57,600

# 5. NBuffers/32
#100GB s_b:  3200 MB             92,160,000
#20GB s_b:   640 MB              18,432,000
#1GB s_b:    32 MB               921,600
#128MB s_b:  4 MB                115,200

# 6. NBuffers/16
#100GB s_b:  6400 MB             184,320,000
#20GB s_b:   1280 MB             36,864,000
#1GB s_b:    64 MB               1,843,200
#128MB s_b:  8 MB                230,400

# 7. NBuffers/8
#100GB s_b:  12800 MB            368,640,000
#20GB s_b:   2560 MB             73,728,000
#1GB s_b:    128 MB              3,686,400
#128MB s_b:  16 MB               460,800

set -x

DIR=`pwd`

# Update the path to directory depending on your environment
#PGDATA=/home/postgres/master/data #Master Directory
PGDATA=/home/postgres/cached/data #Patched Directory

DB=test

MNUM=1
# Number of tables = NUM ÷ SUB
# Currently, 1 relation
NUM=1000000
SUB=1000000

EXIT="exit 0"

########################################################
if [ "$1" = "create" ]; then

	createdb ${DB}

	COUNT=1

	while [ ${COUNT} -le ${MNUM} ]
	do

		CNT=1
		CNT2=${SUB}
		while [ ${CNT} -le ${NUM} ]
		do
			echo "CREATE TABLE table_${COUNT}_${CNT}_${CNT2}(COL1 int);" | psql -d ${DB}

			CNT=`expr $CNT2 + 1`
			CNT2=`expr ${CNT2} + ${SUB}`
		done

		COUNT=`expr $COUNT + 1`
	done

	${EXIT}
fi

#########################################################
#-----------------INSERT DATA TO TABLES-----------------#
if [ "$1" = "insert" ]; then

	COUNT=1
	date +%Y/%m/%d_%H:%M:%S.%3N
	while [ ${COUNT} -le ${MNUM} ]
	do
		CNT=1
		CNT2=${SUB}
		while [ ${CNT} -le ${NUM} ]
		do
            # 100  GB shared_buffers
            psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 5760000);" #NBuffers/512
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 11520000);" #NBuffers/256
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 23040000);" #NBuffers/128
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 46080000);" #NBuffers/64
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 92160000);" #NBuffers/32
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 184320000);" #NBuffers/16
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 368640000);" #NBuffers/8

            # 20 GB shared_buffers
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 1152000);" #NBuffers/512
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 2304000);" #NBuffers/256
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 4608000);" #NBuffers/128
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 9216000);" #NBuffers/64
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 18432000);" #NBuffers/32
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 38864000);" #NBuffers/16
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 73728000);" #NBuffers/8

            # 1 GB shared_buffers
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 57600);" #NBuffers/512
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1,115200);" #NBuffers/256
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 230400);" #NBuffers/128
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 460800);" #NBuffers/64
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 921600);" #NBuffers/32
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 1843200);" #NBuffers/16
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 3686400);" #NBuffers/8

            # 128 MB shared_buffers
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 7200);" #NBuffers/512
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 14400);" #NBuffers/256
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 28800);" #NBuffers/128
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 57600);" #NBuffers/64
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 115200);" #NBuffers/32
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 230400);" #NBuffers/16
            #psql -d ${DB} -c "INSERT into table_${COUNT}_${CNT}_${CNT2} SELECT generate_series(1, 460800);" #NBuffers/8

			CNT=`expr $CNT2 + 1`
			CNT2=`expr ${CNT2} + ${SUB}`
		done

		COUNT=`expr $COUNT + 1`
	done
	date +%Y/%m/%d_%H:%M:%S.%3N

	cd ${DIR}

	${EXIT}
fi


##########################################################
#-----------TRUNCATE EACH TABLE (SEPARATE TXN)-----------#
if [ "$1" = "truncate" ]; then

	COUNT=1
	while [ ${COUNT} -le ${MNUM} ]
	do

		CNT=1
		CNT2=${SUB}
		while [ ${CNT} -le ${NUM} ]
		do
			date +%Y/%m/%d_%H:%M:%S.%3N
			psql -d ${DB} -c "TRUNCATE table_${COUNT}_${CNT}_${CNT2};"

			CNT=`expr $CNT2 + 1`
			CNT2=`expr ${CNT2} + ${SUB}`
		done
		date +%Y/%m/%d_%H:%M:%S.%3N

		COUNT=`expr $COUNT + 1`
	done

	${EXIT}
fi

##########################################################
#------------VACUUM EACH TABLE (SEPARATE TXN)------------#
if [ "$1" = "vacuum" ]; then

	COUNT=1
	while [ ${COUNT} -le ${MNUM} ]
	do
		CNT=1
		CNT2=${SUB}
		while [ ${CNT} -le ${NUM} ]
		do
			date +%Y/%m/%d_%H:%M:%S.%3N
			psql -d ${DB} -c "VACUUM table_${COUNT}_${CNT}_${CNT2};"

			CNT=`expr $CNT2 + 1`
			CNT2=`expr ${CNT2} + ${SUB}`
		done
		date +%Y/%m/%d_%H:%M:%S.%3N

		COUNT=`expr $COUNT + 1`
	done

	${EXIT}
fi

##########################################################
#---------DELETE EACH TABLE (SEPARATE TXN)---------#
if [ "$1" = "delete" ]; then

	COUNT=1
	while [ ${COUNT} -le ${MNUM} ]
	do

		CNT=1
		CNT2=${SUB}
		while [ ${CNT} -le ${NUM} ]
		do
			date +%Y/%m/%d_%H:%M:%S.%3N
			psql -d ${DB} -c "DELETE FROM table_${COUNT}_${CNT}_${CNT2};"

			CNT=`expr $CNT2 + 1`
			CNT2=`expr ${CNT2} + ${SUB}`
		done
		date +%Y/%m/%d_%H:%M:%S.%3N

		COUNT=`expr $COUNT + 1`
	done

	${EXIT}
fi

########################################################
if [ "$1" = "pause" ]; then

	psql -c "SELECT pg_wal_replay_pause();" ${DB}

	${EXIT}
fi

########################################################
if [ "$1" = "resume" ]; then

	psql -c "SELECT pg_wal_replay_resume();" postgres

	date +%M:%S.%3N

	pg_ctl promote -D ${PGDATA}

	set +x

	while [ 1 ]
	do
		RS=`psql -Atc "select pg_is_in_recovery();" postgres`		

		if [ ${RS} = "f" ]; then
			break
		fi
	done

	date +%M:%S.%3N

	set -x

	${EXIT}
fi

########################################################
if [ "$1" = "stop" ]; then

	pg_ctl stop -D ${PGDATA} -w -mi

	${EXIT}
fi

########################################################
if [ "$1" = "dropdb" ]; then

	COUNT=1
	while [ ${COUNT} -le ${MNUM} ]
	do
		CNT=1
		CNT2=${SUB}
		while [ ${CNT} -le ${NUM} ]
		do
			date +%Y/%m/%d_%H:%M:%S.%3N
			psql -d ${DB} -c "DROP table_${COUNT}_${CNT}_${CNT2};"

			CNT=`expr $CNT2 + 1`
			CNT2=`expr ${CNT2} + ${SUB}`
		done
		date +%Y/%m/%d_%H:%M:%S.%3N

		COUNT=`expr $COUNT + 1`
	done

	dropdb ${DB}
	${EXIT}
fi

########################################################