#! /bin/sh
#
# Script for automatic maintenance of Debian PostgreSQL, to be run by cron,
# with the owner being the postgres superuser.
# If the username and password have to be used, make sure the cron script is not
# world-readable!

syntax() {
	echo Syntax: $0 [-v] [-a] [-u user -p password]
	exit 1
}

verbose=
analyse=
while getopts avu:p: arg
do
	case $arg
		in
		a)
			analyse=analyze
			;;
		v)
			verbose=verbose
			;;
		u)
			user=$OPTARG
			;;
		p)
			password=$OPTARG
			;;
		*)
			syntax;;
	esac
done

if [ -n "$user" -a -z "$password" ]
then
	syntax
fi

if [ -z "$user" -a -n "$password" ]
then
	syntax
fi

set -a
. /etc/postgresql/postgresql.env    # set standard environment

if [ -z "$user" ]
then
	dblist=$(psql -t -c "select datname from pg_database order by datname" -d template1)
else
	dblist=$(psql -U $user -W -t -c "select datname from pg_database order by datname" -d template1 <<EOI | tail +1
$password
EOI
)
fi

for database in $dblist
do
	if [ -n "$verbose" ]
	then
		echo Vacuuming $database
	fi
	if [ -z "$user" ]
	then
		/usr/bin/psql -c "vacuum ${verbose} ${analyse}" -q -S -d $database
	else
		/usr/bin/psql -U $user -W -c "vacuum ${verbose} ${analyse}" -q -S -d $database <<EOI
$password
EOI
	fi
done