postgresql-backup.cron

#!/bin/bash

# Create a clean SQL dump of each non-template postgres database
# Should be run as root to avoid any permissions issues

dest_dir=/var/local/backup/postgres

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 "pg_dump --clean $db" | gzip -9 > $dest_dir/$db.sql.gz
done