PostgreSQL Backup Script

PostgreSQL Backup Script

A small script for dynamically backing up all databases in the main cluster of a postgresql host and saving it on a external backuphost.  Requires that ssh is setup with no-password RSA key-pair and of course that the postgresql cluster is up and running (online).

# Global stuff from cluster: roles etc.
su postgres --command "pg_dumpall --globals-only" | ssh -q user@backuphost "dd of=/path/to/backup/of/postgresql/globals.sql" > /dev/null 2>&1

# And then each database (except templates)
su postgres --command 'psql --no-align --tuples-only --command="SELECT datname from pg_database WHERE NOT datistemplate"' | while read databasename; do su postgres --command "pg_dump --format=c $databasename" | ssh -q user@backuphost "dd of=/path/to/backup/of/postgresql/$databasename.sqlc" > /dev/null 2>&1; done