#!/bin/bash
db_user=postgres
db_host=localhost
db_port=5432
remote_db_ip=192.168.xxx.xxx
prod_host=www.example.com
backups_dir=~/db_backups/
SQL_FILE=/tmp/production.sql

#A little SQL file to  clean out the local/dev db before restoring
CLEAN_FILE=~/clean.sql
#If I asked for a new copy, or there isn't an existing copy
if [  "$1" == "get"  -o  ! -f "$SQL_FILE"  ]; then
    echo "Downloading latest database data..."
    ssh -C $prod_host "pg_dumpall -U postgres -h $remote_db_ip -p 5432"  > "$SQL_FILE"
    echo "Download complete."
fi
if [ "$1" == "backup" ]; then
    echo "Compressing for backup..."
    bzip2 -v "$SQL_FILE"
    OUTFILE="$backups_dir`date "+%F_%H:%M"`.sql.bz2"
    mv "$SQL_FILE.bz2" "$OUTFILE"
    echo "Backup saved at $OUTFILE"
else
    if [ -f "$CLEAN_FILE" ]; then
        echo "Running $CLEAN_FILE..."
        psql -U $db_user -h $db_host -p $db_port < "$CLEAN_FILE"
    fi
    echo "Starting database restore..."
    psql -U $db_user -h $db_host -p $db_port < "$SQL_FILE"
    echo "Restore finished."
    echo "Running vaccum..."
    vacuumdb -U $db_user -h $db_host -p $db_port -a -f -z
fi
echo "Done."