#!/bin/sh
#
# This file is released under the terms of the Artistic License.
# Please see the file LICENSE, included in this package, for details.
#
# Copyright (C) 2002-2006 Open Source Development Labs, Inc.
#               2014      2ndQuadrant, Ltd.
#               2002-2022 Mark Wong
#

while getopts "D:l:i:o:s:" opt; do
	case $opt in
	D)
		DBT2DBNAME=$OPTARG
		;;
	i)
		ITERATIONS=$OPTARG
		;;
	l)
		PORT=${OPTARG}
		;;
	o)
		OUTPUT_DIR=$OPTARG
		mkdir -p $OUTPUT_DIR
		;;
	s)
		SAMPLE_LENGTH=$OPTARG
		;;
	esac
done

if [ -z ${DBT2DBNAME} ]; then
	echo "DBT2DBNAME not defined, use -D."
	exit 1
fi

if [ ! "x${PORT}" = "x" ]; then
	PORTARG="-p ${PORT}"
	PORTARG2="-l ${PORT}"
fi

which psql > /dev/null 2>&1
if [ ! $? -eq 0 ]; then
	echo "ERROR: psql required to collect postgresql stats"
	exit 1
fi

PSQL="psql -X ${PORTARG} -d ${DBT2DBNAME} -t --no-align"
PGVERSION=`${PSQL} -c "show server_version_num;"`
PIDCOL="procpid"
QUERYCOL="current_query"
EXTRACOL=""
if [ ${PGVERSION} -gt 90099 ]; then
	PIDCOL="pid"
	QUERYCOL="query"
	EXTRACOL=", state, state_change"
fi

# Get database version.
which pg_config > /dev/null 2>&1
if [ ! $? -eq 0 ]; then
	${PSQL} -c "SELECT version();" >> $OUTPUT_DIR/readme-dbms.txt
else
	pg_config --version >> $OUTPUT_DIR/readme-dbms.txt
fi

# Get database parameters.
${PSQL} -c "show all"  > $OUTPUT_DIR/param.txt

# Get list of tables in the public schema, where dbt2 is expected to be.
# This file is used for the report generation.
${PSQL} -c "SELECT tablename "`
		   `"FROM pg_tables "`
		   `"WHERE schemaname = 'public'"`
		   `"ORDER BY tablename;" > ${OUTPUT_DIR}/table-list.txt

# Get list of indexes in the public schema, where dbt2 is expected to be.
# This file is used for the report generation.
${PSQL} -c "SELECT indexname "`
		   `"FROM pg_indexes "`
		   `"WHERE schemaname = 'public'"`
		   `"ORDER BY indexname;" > ${OUTPUT_DIR}/index-list.txt

# Get the plans before the test.
dbt2-pgsql-plans $PORTARG2 -d $DBT2DBNAME -o ${OUTPUT_DIR}/plan0.txt

LOCKSTAT="SELECT EXTRACT(EPOCH FROM TRANSACTION_TIMESTAMP()) AS ctime, \
relname, pid, mode, granted \
FROM pg_locks, pg_class \
WHERE relfilenode = relation \
ORDER BY relname"

DBACTIVITY="SELECT EXTRACT(EPOCH FROM TRANSACTION_TIMESTAMP()) AS ctime, \
datname, ${PIDCOL}, usesysid, usename, application_name, client_addr, \
client_port, backend_start, xact_start, query_start, \
${QUERYCOL} ${EXTRACOL} \
FROM pg_stat_activity"

STATDB="SELECT EXTRACT(EPOCH FROM TRANSACTION_TIMESTAMP()) AS ctime, \
datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, \
tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted \
FROM pg_stat_database"

STATTABLE="SELECT EXTRACT(EPOCH FROM TRANSACTION_TIMESTAMP()) AS ctime, \
a.schemaname, a.relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, \
n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, \
heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, \
toast_blks_hit, tidx_blks_read, tidx_blks_hit \
FROM pg_statio_all_tables a, pg_stat_all_tables b \
WHERE a.relid = b.relid"

STATINDEX="SELECT EXTRACT(EPOCH FROM TRANSACTION_TIMESTAMP()) AS ctime, \
a.schemaname, a.relname, a.indexrelname, idx_scan, idx_tup_read, \
idx_tup_fetch, idx_blks_read, idx_blks_hit \
FROM pg_stat_all_indexes a, pg_statio_all_indexes b \
WHERE a.indexrelid = b.indexrelid"

COUNTER=0

# Take the initial data points with headers so that data is identifiable later.
${PSQL} -c "COPY (${LOCKSTAT}) TO STDOUT (FORMAT CSV, HEADER);" \
		> $OUTPUT_DIR/lockstats.csv &

${PSQL} -c "COPY (${DBACTIVITY}) TO STDOUT (FORMAT CSV, HEADER);" \
		> $OUTPUT_DIR/db_activity.csv &

${PSQL} -c "COPY (${STATDB}) TO STDOUT (FORMAT CSV, HEADER);" \
		> $OUTPUT_DIR/pg_stat_databases.csv &

${PSQL} -c "COPY (${STATTABLE}) TO STDOUT (FORMAT CSV, HEADER);" \
		> $OUTPUT_DIR/pg_stat_tables.csv &

${PSQL} -c "COPY (${STATINDEX}) TO STDOUT (FORMAT CSV, HEADER);" \
		> $OUTPUT_DIR/pg_stat_indexes.csv &

while [ $COUNTER -lt $ITERATIONS ]; do
	COUNTER=$(( $COUNTER+1 ))
	sleep $SAMPLE_LENGTH

	${PSQL} -c "COPY (${LOCKSTAT}) TO STDOUT (FORMAT CSV);" \
			>> $OUTPUT_DIR/lockstats.csv &

	${PSQL} -c "COPY (${DBACTIVITY}) TO STDOUT (FORMAT CSV);" \
			>> $OUTPUT_DIR/db_activity.csv &

	${PSQL} -c "COPY (${STATDB}) TO STDOUT (FORMAT CSV);" \
			>> $OUTPUT_DIR/pg_stat_databases.csv &

	${PSQL} -c "COPY (${STATTABLE}) TO STDOUT (FORMAT CSV);" \
			>> $OUTPUT_DIR/pg_stat_tables.csv &

	${PSQL} -c "COPY (${STATINDEX}) TO STDOUT (FORMAT CSV);" \
			>> $OUTPUT_DIR/pg_stat_indexes.csv &
done
