Skip to content

Instantly share code, notes, and snippets.

@janit
Created June 26, 2017 06:15
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save janit/cf2f7e865fbb9a8e2ee36dc27456d097 to your computer and use it in GitHub Desktop.
Save janit/cf2f7e865fbb9a8e2ee36dc27456d097 to your computer and use it in GitHub Desktop.
Rotating PostgreSQL Backup Script
#!/bin/bash
# for use with cron, eg:
# 0 3 * * * postgres /var/db/db_backup.sh foo_db
if [[ -z "$1" ]]; then
echo "Usage: $0 <db_name> [pg_dump args]"
exit 1
fi
DB="$1"; shift
DUMP_ARGS=$@
DIR="/var/db/backups/$DB"
KEEP_DAILY=7
KEEP_WEEKLY=5
KEEP_MONTHLY=12
function rotate {
rotation=$1
fdate=`date +%Y-%m-%d -d $date`
file=$DIR/daily/*$fdate*.gz
mkdir -p $DIR/$rotation/ || abort
if [ -f $file ]; then
cp $file $DIR/$rotation/ || abort
else
echo
fi
}
function prune {
dir=$DIR/$1
keep=$2
ls $dir | sort -rn | awk " NR > $keep" | while read f; do rm $dir/$f; done
}
function abort {
echo "aborting..."
exit 1
}
mkdir -p $DIR/daily || abort
mkdir -p $DIR/weekly || abort
mkdir -p $DIR/monthly || abort
mkdir -p $DIR/yearly || abort
date=`date +%Y-%m-%d` || abort
day=`date -d $date +%d` || abort
weekday=`date -d $date +%w` || abort
month=`date -d $date +%m` || abort
# Do the daily backup
/usr/bin/pg_dump $DB $DUMP_ARGS | gzip > $DIR/daily/${DB}_$date.sql.gz
test ${PIPESTATUS[0]} -eq 0 || abort
# Perform rotations
if [[ "$weekday" == "0" ]]; then
rotate weekly
fi
if [[ "$day" == "01" ]]; then
rotate monthly
fi
if [[ "$month/$day" == "01/01" ]]; then
rotate yearly
fi
prune daily $KEEP_DAILY
prune weekly $KEEP_WEEKLY
prune monthly $KEEP_MONTHLY
@kunthar
Copy link

kunthar commented Jun 18, 2022

put this little jewel to: /etc/crontab file. not a crontab -e .

@salex
Copy link

salex commented Feb 4, 2023

Than you for this jewel.

I had been using a modified version of the Postgres script for years when it start blowing up on a DB that didn't have a password. I backup 4 databases and just put a semi config script in front:

DATABASES=(myvfw_production ptgolf_production our_books_production rcash_production);

for DATABASE in "${DATABASES[@]}"
do
    # echo $DATABASE
    /home/rails/backups/apps/db_backup.sh "$DATABASE"
done

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment