Postgres automated backups

postgresbak.sh

#!/bin/bash
#
# postgresbak - psql backup for xxx.yyy.zzz.qqq
#
# Created by Brant Faircloth on 2009-04-06.
# Copyright (c) 2009 Brant Faircloth. All rights reserved.
# 
# Run by cron w/ 
# 0	0	*	*	*	/path/to/script/postgresbak.sh
#
# password is in ~/.pgpass

BDATE=`/bin/date +%m-%d-%Y-%H_%M`
BNAME=$BDATE.db.$HOSTNAME.sql.gz
GLOBALS=$BDATE.globals.$HOSTNAME.sql.gz
BLOC=/dir/to/save/backups
USER=backuser

# change to the working directory
cd $BLOC
# run the backup, saving files to $BLOC
pg_dumpall -g -U $USER | /bin/gzip > $BLOC/$GLOBALS 
psql -AtU $USER template1 -c "SELECT datname FROM pg_database WHERE NOT datistemplate" | \
while read f; 
   do pg_dump -U $USER --format=c $f | /bin/gzip -c > $BLOC/$BNAME;
done;
# remove any files older than 14 days (2 weeks)
/usr/bin/find $BLOC/* -mtime +14 -type f -name "*.sql.gz" -exec rm {} \;