PostgreSQL Backup Script

gistfile1.sh

#!/bin/bash
TIMESTAMP=$(date +\%Y\%m\%d_\%H\%M\%S)
DEST=/var/backups/db/
OWNER=backuppc
GROUP=backuppc

echo -n "Creating temp dir..."
TMPDIR=$(mktemp -d)
echo " Done."

echo -n "Backup globals..."
pg_dumpall -U postgres --globals-only | gzip -c > $TMPDIR/pg.globals.$TIMESTAMP.sql.gz || exit 1
echo " Done."

echo "Backup databases"
sql="SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"
databases=$(psql -U postgres --tuples-only -P format=unaligned -c "$sql")
for db in $databases; do
    echo -n "- $db..."
    pg_dump -U postgres --format=custom --blobs $db --file=$TMPDIR/pg.$db.$TIMESTAMP.pgdump || exit 1
    echo " Done."
done

echo -n "Update permissions..."
chown $OWNER:$GROUP $TMPDIR/*
chmod 600 $TMPDIR/*
echo " Done."

echo -n "Move backups..."
mv $TMPDIR/* $DEST/
rm -r $TMPDIR
echo " Done."