Skip to content

Instantly share code, notes, and snippets.

@hattwj
Last active August 29, 2015 14:00
Show Gist options
  • Save hattwj/11405978 to your computer and use it in GitHub Desktop.
Save hattwj/11405978 to your computer and use it in GitHub Desktop.
PostgreSQL Automated Backup Script
#!/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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment