PostgreSQL Backup Script II (follow up)

Backing up every database on every version and each running cluster on a host in a single script. I'm beginning to like PostgreSQL more and more. :)

#!/bin/bash
# PostgreSQL backup script
# Run as postgres user

BACKUP_HOST=replace-with-your-backup-host
BACKUP_HOST_USER=replace-with-your-backup-host-user
BACKUP_HOST_PATH=replace-with-your-backup-host-/path/to/backup/of/postgresql

# For all running PostgreSQL clusters
awk -- '{ print $1"/"$2 }' <(pg_lsclusters --no-header | grep online) | while read cluster;
do

# Create cluster-path on backuphost
ssh -q $BACKUP_HOST_USER@$BACKUP_HOST "mkdir -p $BACKUP_HOST_PATH/$cluster";

# Global stuff from cluster: roles etc.
pg_dumpall --cluster $cluster --globals-only | ssh -q $BACKUP_HOST_USER@$BACKUP_HOST "dd of=$BACKUP_HOST_PATH/$cluster/globals.sql" > /dev/null 2>&1;

# And then each database (except templates)
psql --cluster $cluster --no-align --tuples-only --command="SELECT datname from pg_database WHERE NOT datistemplate" | while read databasename;
do pg_dump --cluster $cluster --format=c -- $databasename | ssh -q $BACKUP_HOST_USER@$BACKUP_HOST "dd of=$BACKUP_HOST_PATH/$cluster/$databasename.sqlc" > /dev/null 2>&1; done;

done;

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