Script to perform VACUUM (to prevent wrap around) on a scheduled window

vacuum_freeze_demand.sh

#!/bin/bash

set -o pipefail

PROCESS_TIMEOUT_SEC=7200
VACUUM_FREEZE_TABLE_AGE=150000000 # 150M
VACUUM_FREEZE_MIN_AGE=50000000    #  50M

START_TIME_EPOCH=$( date +%s )
END_TIMEOUT_EPOCH=$(( ${START_TIME_EPOCH} + ${PROCESS_TIMEOUT_SEC} ))

log() {
	echo "`date +'%F %T %Z'` LOG: $@"
}

get_timeout() {
	now_epoch=$( date +%s )
	echo $(( ( ${END_TIMEOUT_EPOCH} - ${now_epoch} ) * 1000 ))
}

vacuum_relation() {
	local rel="$1"
	log "VACUUM $rel"
	statement_timeout=$( get_timeout )
	if [ ${statement_timeout} -lt 10000 ]; then
		log "Timeout reached."
		exit 0
	fi
	{
		echo "RESET ALL;"
		echo "SET vacuum_freeze_table_age TO ${VACUUM_FREEZE_TABLE_AGE};"
		echo "SET vacuum_freeze_min_age TO ${VACUUM_FREEZE_MIN_AGE};"
		echo "SET statement_timeout TO ${statement_timeout};"
		echo "SELECT 'Age: ' || age(relfrozenxid) FROM pg_class WHERE oid = '${rel}'::regclass;"
		echo "VACUUM ${rel};"
		echo "SELECT 'Age: ' || age(relfrozenxid) FROM pg_class WHERE oid = '${rel}'::regclass;"
	} | psql --set=ON_ERROR_STOP=ON "${db}" -AXqt >&2
	ret=$?
	if [ $ret -ne 0 ]; then
		statement_timeout=$( get_timeout )
		if [ ${statement_timeout} -le 0 ]; then
			log "Timeout reached. Last relation (${rel}) canceled"
			exit 0
		else
			log "Error $ret for relation $rel"
			return 1
		fi
	fi
	return $ret
}

for db in $( psql -AXtqc "SELECT datname FROM pg_database WHERE datallowconn AND age(datfrozenxid) >= ${VACUUM_FREEZE_TABLE_AGE} ORDER BY age(datfrozenxid) DESC"); do
	log "Processing database ${db}"
	for rel in $( psql "${db}" -AXtqc "SELECT oid::regclass FROM pg_class WHERE relkind IN ('r', 't', 'm') AND age(relfrozenxid) >= ${VACUUM_FREEZE_TABLE_AGE} ORDER BY age(relfrozenxid) DESC" ); do
		vacuum_relation "${rel}"
	done
	log "Done database ${db}"
done

log "Done with no timeout"