Stop using Ruby to back up the postgres database in your Rails app. Use bash instead!

README.md

Backing up Postgres with Bash

Stop using Ruby to back up the postgres database in your Rails app. Use bash instead!

##Install:

cd /usr/local/bin
git clone https://gist.github.com/4552332.git db_backup
chmod +x db_backup/db_backup.sh
cp db_backup/db_backup /etc/cron.d/

Then you need to create a backup folder that the postgres user can write to:

mkdir -p /backup
chown postgres:postgres /backup

Restart your cron and enjoy the easy backups. Enjoy!

##What's next?

These backups are stored on your server. That ain't good. The next steps are to get the backups moving over to S3 using either s3cmd or s3fs

db_backup

# Add this file to /etc/cron.d

02 * * * * postgres /usr/local/bin/db_backup/db_backup.sh hourly /backup/db $DATABASE_NAME 24 >> /backup/db/backup.log 2>&1
02 1 * * * postgres /usr/local/bin/db_backup/db_backup.sh daily /backup/db $DATABASE_NAME 5 >> /backup/db/backup.log 2>&1
02 3 * * 0 postgres /usr/local/bin/db_backup/db_backup.sh weekly /backup/db $DATABASE_NAME 4 >> /backup/db/backup.log 2>&1
02 5 1 * * postgres /usr/local/bin/db_backup/db_backup.sh monthly /backup/db $DATABASE_NAME 4 >> /backup/db/backup.log 2>&1

db_backup.sh

# Postgres backup script

fail()
{
    echo `date +%h:%m:%s` error: $1
    kill -sigint $$
}

if [ "$1" ]
then
   frequency=$1
else
    fail "frequency missing (arg 1)"
fi

if [ "$2" ]
then
   backup_dir=$2
else
    fail "path to backup dir missing (arg 2)"
fi

if [ "$3" ]
then
    user=$3
else
    fail "user missing (arg 3)"
fi

if [ "$4" ]
then
    keep=$4
else
    fail "number of keeps missing (arg 4)"
fi

before=`date +%s`
printf "\n------------------------------------------------------------------------------\n"
printf "%s: STARTING %s backups ........\n" `date +%h:%m:%s` $frequency

full_path=$backup_dir/$frequency
date=`date +%Y%m%d%H%M`
mkdir -p $full_path

ignore="staging|test"

database_list=`psql -l | egrep -v $ignore | grep $user | awk '{print $1}' | grep -v \|`

count=`ls $full_path | wc -l`

if [ $count -gt $keep ]
then
    remove=`expr $count - $keep`
    files=`ls $full_path | sort -n | head -$remove`
    for file in $files
    do    
        rm $full_path/$file
    done
fi

for database in $database_list
do
    database_before=`date +%s`
    printf "%s: creating %s backup for %s\n" `date +%h:%m:%s` $frequency $database
    dump_file="$full_path/$database-$date.gz"
    `pg_dump --no-acl --no-owner --clean $database | gzip > $dump_file`
    database_after=`date +%s`
    database_elapsed_seconds=`expr $database_after - $database_before`
    printf "%s: %s backup for %s finished in %s seconds\n" `date +%h:%m:%s` $frequency $database $database_elapsed_seconds
done

after=`date +%s`
elapsed_seconds=`expr $after - $before`

if [ "$elapsed_seconds" ]
then
   printf "%s: COMPLETED %s backups in %s seconds\n" `date +%h:%m:%s` $frequency  $elapsed_seconds
fi