postgresql-vacuum/pgvacuum

#!/usr/bin/env bash
(

	flock -n 9 || { logger -t PostgreSQL databases vacuum is already running!; exit 1; }
	
	print_elapsed_time() {
		time_elapsed=$(( $2-$1 ))
		milliseconds=$(( $time_elapsed%1000 ))
		seconds=$(( ($time_elapsed/1000)%60 ))
		minutes=$(( ($time_elapsed/1000/60)%60 ))
		hours=$(( ($time_elapsed/1000/60/60)%24 ))

		(( $hours > 0 )) && printf '%dh' $hours
		(( $minutes > 0 )) && printf '%dm' $minutes
		(( $seconds > 0 )) && printf '%ds' $seconds
		printf '%dms\n' $milliseconds
	}
	
	# ... commands executed under lock ...
	# Milliseconds from epoch
	vacuum_start_time=$(date +%s%3N)

	echo '###################################################################################################'
	echo '###########################   PostgreSQL databases vacuum has started!   ##########################'
	echo "###########################           $(date '+%Y-%m-%d %H:%M:%S')                 #########################"
	echo '###################################################################################################'

	# Retrieve all databases from PostgreSQL
	excluded_db="db.datname NOT LIKE E'\'template%\''"
	excluded_schema="nspname NOT LIKE E'\'pg_toast%\'' AND nspname NOT LIKE E'\'pg_temp%\'' AND nspname NOT IN (E'\'pg_catalog\'', E'\'information_schema\'')"

	echo "$(date '+%Y-%m-%d %H:%M:%S') - retrieve all databases"
	databases=$(su - postgres -c "psql -Atc 'SELECT db.datname FROM pg_database db INNER JOIN pg_authid a ON a.oid = db.datdba WHERE $excluded_db ORDER BY db.datname'")
	
	for db in $databases; do
		start_time=$(date +%s%3N)

		echo "$(date '+%Y-%m-%d %H:%M:%S') - ==============================================================================="
		echo "$(date '+%Y-%m-%d %H:%M:%S') - vacuum for $db database has started!"   					  
		echo "$(date '+%Y-%m-%d %H:%M:%S') - ==============================================================================="

		dbsize=$(su - postgres -c "psql -d $db -Atc 'SELECT pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database WHERE datname=E'\'$db\'''")
		echo "$(date '+%Y-%m-%d %H:%M:%S') - + $db size before vacuum: $dbsize"

		# Retrieve all shemas for current database
		schemas=$(su - postgres -c "psql -d $db -Atc 'SELECT nspname FROM pg_catalog.pg_namespace WHERE $excluded_schema ORDER BY nspname'")

		for schema in $schemas; do
			schema_start_time=$(date +%s%3N)
			
			echo "$(date '+%Y-%m-%d %H:%M:%S') - + => vacuum for $schema schema has started!"
            
			tables=$(su - postgres -c "psql -d $db -Atc 'SELECT tablename FROM pg_tables WHERE schemaname=E'\'$schema\'' ORDER BY tablename'")

			for table in $tables; do
				su - postgres -c "vacuumdb -q -f -z -d $db --t $schema.$table"
			done

			schema_end_time=$(date +%s%3N)
			echo "$(date '+%Y-%m-%d %H:%M:%S') - + .... vacuum for $schema schema has completed after" $(print_elapsed_time $schema_start_time $schema_end_time)
		done

		dbsize=$(su - postgres -c "psql -d $db -Atc 'SELECT pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database WHERE datname=E'\'$db\'''")
		echo "$(date '+%Y-%m-%d %H:%M:%S') - + $db size after vacuum: $dbsize"

		end_time=$(date +%s%3N)
		echo "$(date '+%Y-%m-%d %H:%M:%S') - + vacuum for $db database has completed after" $(print_elapsed_time $start_time $end_time)
		
	done

	vacuum_end_time=$(date +%s%3N)
	echo "$(date '+%Y-%m-%d %H:%M:%S') - PostgreSQL vacuum has completed after" $(print_elapsed_time $vacuum_start_time $vacuum_end_time)

) 9> /var/tmp/vacuum.lock

exit $?