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/
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
#!/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 " " |