normal_vacuum_case_sample.sh

#!/bin/bash

## Set your PGHOME here
#PGHOME="/usr/local/debug/pgsql/"

## Set yout PGDATA
DBCLUSTER="/test/cluster"

${PGHOME}/bin/pg_ctl -D  ${DBCLUSTER} stop
rm -rf ${DBCLUSTER}
${PGHOME}/bin/initdb -D ${DBCLUSTER} --no-locale -E UTF8 -k

## Configuration
echo "logging_collector = on"  >> ${DBCLUSTER}/postgresql.conf
echo "autovacuum_freeze_max_age = 200000" >> ${DBCLUSTER}/postgresql.conf
echo "autovacuum = off"  >> ${DBCLUSTER}/postgresql.conf
echo "autovacuum_naptime = 5s"  >> ${DBCLUSTER}/postgresql.conf
echo "autovacuum_max_workers = 5" >> ${DBCLUSTER}/postgresql.conf
echo "vacuum_freeze_min_age = 0"  >> ${DBCLUSTER}/postgresql.conf
echo "max_locks_per_transaction = 5000" >> ${DBCLUSTER}/postgresql.conf

${PGHOME}/bin/pg_ctl -D ${DBCLUSTER} start

## Create table for CTAS source
${PGHOME}/bin/psql -c "CREATE TABLE src (col1 int);"
${PGHOME}/bin/psql -c "INSERT INTO src SELECT generate_series(1,100);"

## Create tables (20000)
${PGHOME}/bin/psql << "EOF"
DO $$ DECLARE v1 int;
BEGIN
  FOR v1 IN 1..20000 LOOP
    EXECUTE 'CREATE UNLOGGED TABLE tbl_' || v1::text || '  AS SELECT * FROM src';
  END LOOP;
END$$;
EOF

## Delete rows for each tables
${PGHOME}/bin/psql << "EOF"
DO $$ DECLARE v1 int;
BEGIN
  FOR v1 IN 1..20000 LOOP
    EXECUTE 'DELETE FROM  tbl_' || v1::text || ' WHERE col1 < 90 ';
  END LOOP;
END$$;
EOF

## Drop source table
${PGHOME}/bin/psql -c "DROP TABLE src;"

${PGHOME}/bin/pg_ctl -D ${DBCLUSTER} stop

echo "autovacuum = on"  >> ${DBCLUSTER}/postgresql.conf

${PGHOME}/bin/pg_ctl -D ${DBCLUSTER} start