psql -U postfix -h localhost -l -t | awk '/^ [a-zA-Z]+/ { print $1; }' | grep -v '^\(template0\)' | while read DBNAME

do

...

done

#! /bin/sh

# Keep n days worth of backups
GENERATIONS=180

BACKUP_DIR=”/var/backups/generational/pgsql”;
BACKUP_CURRENT=”/var/backups/pgsql”;
DATE=`date +’%Y%m%d’`
HOSTNAME=`hostname`
DBUSER=”postgres”

# load defaults, over-riding script supplied values
[ -f /etc/default/pgbackup ] && . /etc/default/pgbackup

DAILY_BACKUP_DIR=”$BACKUP_DIR/$DATE”

mkdir -p $DAILY_BACKUP_DIR

psql -U $DBUSER -h $HOSTNAME -l -t | awk ‘/^ [a-zA-Z]+/ { print $1; }’ | grep -v ‘^\(template0\)’ | while read DBNAME
do
if [ “$DBNAME” ];
then
printf “Backing up PostgreSQL database $DBNAME, ”
BACKUP_FILE=”$DAILY_BACKUP_DIR/$DBNAME”

pg_dump –format=t -U $DBUSER -h $HOSTNAME $DBNAME | gzip -9 > “$BACKUP_FILE.tar.gz”
rc1=$?
pg_dump –format=p –column-inserts -c -U $DBUSER -h $HOSTNAME $DBNAME | gzip -9 > “$BACKUP_FILE.sql.gz”
rc2=$?

if [ $rc1 -eq 0 ] && [ $rc2 -eq 0 ];
then
printf “OK\n”
else
printf “FAILED\n”
fi
fi
done

rsync -arv $DAILY_BACKUP_DIR/* $BACKUP_CURRENT/

COUNT=`ls -c1 $BACKUP_DIR | wc -l`
DELETE=`echo “$COUNT – $GENERATIONS” | bc`

if [ $DELETE -gt 0 ];
then
# we have exceeded the number of backup generations we want to
# keep, so delete the oldest

echo “Deleting old backup files”

ls -c1 “$BACKUP_DIR” | sort | head -$DELETE | while read f;
do
rm -rvf $BACKUP_DIR/$f
done

fi

“/etc/cron.daily/1pgbackup” 58L, 1326C 1,1 All