Skip to content

Instantly share code, notes, and snippets.

@mynameisrufus
Created February 9, 2011 03:05
Show Gist options
  • Save mynameisrufus/817805 to your computer and use it in GitHub Desktop.
Save mynameisrufus/817805 to your computer and use it in GitHub Desktop.
Postgres backup script (rails focused)
# Postgres backup script
# mynameisrufus
#
# Cron tab examples:
#
# daily keeping the last 5 days (run at 1:00AM, 2 minutes after the hour)
#
# 02 1 * * * postgres /script/db_backup.sh daily /var/backups/db $DATABASE_OWNER 5 >> /var/backups/db/backup.log 2>&1
#
#
# weekly keeping the last 2 weeks (3:00AM, Sunday 02 minutes after the hour)
#
# 02 3 * * 0 postgres /script/db_backup.sh weekly /var/backups/db $DATABASE_OWNER 2 >> /var/backups/db/backup.log 2>&1
#
#
# monthly keeping the last 2 months (5:00AM, On the first of the month, 02 minutes after the hour)
#
# 02 5 1 * * postgres /script/db_backup.sh monthly /var/backups/db $DATABASE_OWNER 2 >> /var/backups/db/backup.log 2>&1
#
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`
mkdir -p $full_path/$date
ignore="staging|test"
database_list=`psql -l | egrep -v $ignore | grep $user | awk '{print $1}' | grep -v \|`
count=`ls $full_path | egrep "^[0-9]{8}$" | wc -l`
if [ $count -gt $keep ]
then
remove=`expr $count - $keep`
directories=`ls $full_path| egrep "^[0-9]{8}$" | sort -n | head -$remove`
for directory in $directories
do
rm -rf $full_path/$directory
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/$date/$database.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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment