The following shell script can be used as a cron job. It will backup all databases with a unique name based on date and time. Backup files are rotated automatically (delete old backup). This script is based on another one written by Etienne Pouliot: http://www.defitek.com/blog/2010/01/06/a-simple-yet-effective-postgresql-backup-script/
#!/bin/sh
# Posrgres executables
CMD_PSQL=/usr/local/pgsql/bin/psql
CMD_DUMP=/usr/local/pgsql/bin/pg_dump
# prefix for backup filenames
NAME_PREFIX=`date +%j`
# directory where to save the database backup
NAME_BACKUP_DIR=/tmp
# age of the older file to keep (in days)
NB_DAYS_TO_KEEP=7
# Start backuping
Databases=`$CMD_PSQL -tq -d template1 -c "select datname from pg_database"`
echo "Starting backup of all databases..."
for current_db in `echo $Databases`
do
$CMD_DUMP -D $current_db > /$NAME_BACKUP_DIR/$NAME_PREFIX.$current_db.backup
done
echo "Backup finnished !"
echo " "
# start deleting old file
echo "Deleteting old backup files..."
oldbackup=`find $NAME_BACKUP_DIR -type f -mtime +$NB_DAYS_TO_KEEP -name "*.backup"`
for current_file in `echo $oldbackup`
do
rm -f $current_file
echo "$current_file deleted"
done
echo "Old file deletion finished !"
echo " "
