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 " "

Leave a Reply

Your email address will not be published. Required fields are marked *


*