Automatted Postgres DB Backup

To make life easy I use a script to backup my postgres DB’s. The script runs from CRON several times a day and puts a compressed DB SQL image into a directory in a time stamped format. To assist recovery there is a recovery script mentioned in another post that also restores the DB from the same spot so I have a complete backup and recovery process in place.

To help keep everything tidy I cleanup my backups even 7 days using another script that logs everything it does. Its all basically set and forget. Anothe job put the backups onto tape using Backup Exec 12.5 so its all covered. Testing recovery from tape is easy too, select an old file and go!

Additionally the backup script is aware of the postgres environment I run, I have 3 environments PROD, DEV and STAGE. The Production environment runs on its own VM while DEV and STAGE run on another VM (both on the same VM). So for consistency I setup both boxes the same and have fully recovery and backup in a very flexible fashion.

To perform backup I use this script:

#!/bin/bash
#
# Dump raw data from DB and compress it on the fly to a mounted partition
#
# use DBENV to specify PROD, DEV or STAGE
#
#
#
#
# sid 2008-11-18 changed to mounted partition
#
BACKUP_DIR="/data/db-backups"
mkdir -p $BACKUP_DIR
chmod 777 $BACKUP_DIR



SERIAL="`date +%Y%m%d-%H%M%S`"

#
# Common looging code
#
function LogStart
{
echo "====== Log Start =========" >> $LF
echo "Time: `date`" >> $LF
echo " " >> $LF
}


function LogEnd
{
echo " " >> $LF
echo "Time: `date`" >> $LF
echo "====== Log End =========" >> $LF
}

FILE_DATE=`date '+%Y-%m-%d'`
LF_DIR=/logs/db-backup
LF=$LF_DIR/db-backup-$FILE_DATE.log
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF

LogStart
#
# test cmd line need prod,dev or stage
#
if [ $# -ne 1 ]
then
echo "ERROR - no environment PROD,DEV etc defined" >> $LF
else
echo "Environment [$DBENV]" >> $LF
DBENV=$1
case $DBENV in
"PROD"|"prod")
H="prod.internal"
PORT="5432"
DBENV="prod"
;;
"STAGE"|"stage")
# H="stage.internal"
# migrated 18-1-2010
H="localhost"
PORT="35432"
DBENV="stage"
;;
"DEV"|"dev")
# H="dev.internal"
# migrated 18-1-2010
H="localhost"
PORT="25432"
DBENV="dev"
;;
*)
echo "ERROR - invalid env [$DBENV] specified" >> $LF
DBENV=""
;;
esac
if [ $DBENV != "" ]
then
DBFILE=$BACKUP_DIR/db-$DBENV-$SERIAL.sql.gz
echo "Host [$H]" >> $LF
echo "DB File [$DBFILE]" >> $LF
if [ -a $DBFILE ]
then
mv $DBFILE $DBFILE.`date '+%M%S'`
fi
su - postgres -c "pg_dumpall -o -c -v -p $PORT -h $H -o -c -v|gzip -9>$DBFILE" >> $LF 2>&1
else
echo "DB backup aborted" >> $LF
fi
fi
LogEnd
Advertisements