postgresql-maintenance.cron

#!/bin/bash

# Vacuum, analyze and re-index all non-template postgres databases
# Should be run as root to avoid any permissions issues

db_list_sql="SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn ORDER BY datname"

for db in $(su - postgres -c "psql -At -c '$db_list_sql'"); do
  su - postgres -c "vacuumdb --analyze --quiet $db"
  su - postgres -c "PGOPTIONS='--client-min-messages=warning' reindexdb --quiet $db"
done