PostgreSQL Automated Backup Script

postgresql_backup.sh

#!/bin/bash

# PostgreSQL Backup util
# Automatic backup utility, run in cron job under postgres user
# Example cron line:
# 53 *  *   *   *     /data/scripts/backup_database.sh

# Defaults - I
PREFIX='hourly_'
HOUR=`date +%H`
DAY=`date +%d`


# if its 1am we will name the tar file differently so it wont get scrubbed
# after a few weeks
if [ $HOUR -eq 1 ]; then
    PREFIX='daily_'
fi

# if its the end of the month we will name the tar file differently so it wont get
# scrubbed after a few months
if [  $DAY -eq 1 -a $HOUR -eq 1 ]; then
    PREFIX='monthly_'
fi

# Defaults - II
FDATE=`date +%Y-%m-`$DAY'_'$HOUR
FPATH='/data/backups/db/postgresql/'
logfile=$FPATH$FDATE'-backup.log.out'
logfile2=$FPATH'backup.log'
errors=''
tarfile=$FPATH$PREFIX'db-backup-'$FDATE'.tar.bz2'
outfiles=$FPATH'*.out'

# P - Ignore leading / in path (dont error)
TARCMD='/bin/tar --remove-files -jcvPf '$tarfile' '$outfiles' > /dev/null'

FINDCMD='/usr/bin/find'


# Get a list of all databases
# Except for template0, template1 and postgres
DBS=$(psql template1 -c "\l"|tail -n+4|cut -d'|' -f 1|sed -e '/^ *$/d;/template0/d;/template1/d;/postgres/d;'|sed -e '$d')


echo -e "Dumping pg databases:: \n" >> $logfile
# Dump the contents of every database
for db in $DBS
  do
    echo -e '  started db: '$db' date: '`date`"\n" >> $logfile
    DUMP_CMD='pg_dump '$db' > '$FPATH$PREFIX$FDATE'-'$db'-dump.out'
    eval "$DUMP_CMD" && \
    echo -e '  completed db: '$db "\n">> $logfile  || \
    errors=$errors"  failed db: '$db' date: \n"`date`
done

echo -e "Errors: " >> $logfile
echo -e $errors >> $logfile

echo -e "Removing Old Files: " >> $logfile

# Only keep the hourly backups for the last two weeks
echo $($FINDCMD $FPATH -mtime +10 -name "hourly*" | xargs rm -f )"\n" >> $logfile

# and the daily backups for the last 100 days
echo $($FINDCMD $FPATH -mtime +100 -name "daily*" | xargs rm -f ) "\n">> $logfile

# and the daily backups for the last 365 days
echo $($FINDCMD $FPATH -mtime +365 -name "monthly*" | xargs rm -f ) "\n">> $logfile

echo `cat $logfile`>> $logfile2

# Compress results
eval "$TARCMD >> $logfile2"