Monday, May 2, 2016

PostgreSQL Backup Script IV (follow up)

Came around to this old stuff - and realizing that it didn't really work :-( Bash loops and pipes can be tricky :-)

So an update was long overdue:

#!/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
while read -u 3 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)
while read -u 4 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 4< <(psql --cluster $cluster --no-align --tuples-only --command="SELECT datname from pg_database WHERE NOT datistemplate");
done 3< <( awk -- '{ print $1"/"$2 }' <(pg_lsclusters --no-header | grep online) ) 

No comments: