PostgreSQL: backup script for all databases
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 " "