Somethings never change. Needing to backup data is one of those things. Since webapps are constantly in development, and new features often means changes to the database schema, I find it super useful (and comforting) to do hourly backups of my applications database. If I make some boneheaed mistake, at most I’ll only lose 1 hour of work. And because I’m super paranoid, I put the output of the backup in two locations… the local server and a physically remote server (incase of massive datacenter failure at one location).

To save disk space, this script will automatically bzip the sql dump file. Your mileage will vary depending on how big your database is, but on a small webapp I can usually run this thing hourly, 24/7 and only use 1% of the available drive space after a year. Point being that you can backup as often as your disk space allocation will allow… and usually it will allow for more than you can imagine. One thing to note is that this script may not be the best strategy for high-volume sites, since it assumes you will lock the database while doing the dump.

Below are the setup instructions.

1. Copy the contents below into a file named pg-backup.sh placed in the root account home (on Ubuntu it’s ‘/root’). For simplicity, I’m using the root account, but on your production servers, you may want to create a dedicated “backupuser” account that executes these actions.
   
   
   [cci lang="bash"]

   #!/bin/sh

   DATE=`date “+%Y-%m-%d-%H-%M-%S”`
   BACKUP_DIR=”pgbackups”

   #DO NOT CHANGE ANYTHING BELOW THIS LINE
   DUMP=”pg_dump”
   REMOTE=”"

   usage() {
   echo Usage:
   echo “\t$0 [options]”
   echo
   echo “Where options is as follows:”
   echo “\t-r\t–remote\t\tThe remote host to copy the DB backup to (mandatory)”
   echo “\t-u\t–user\t\t\tDatabase user name; defaults to the current linux user”
   echo “\t-d\t–database\t\tDatabase name; defaults to the database user name”
   echo “\t-p\t–password\t\tDatabase password; if none is specified you will be prompted to enter one”
   echo “\t-R\t–remote–destination\tThe directory on the remote host to upload the DB backup to; defaults to ~/$BACKUP_DIR”
   echo “\t-L\t–local–destination\tThe directory on local host to save the DB backup; defaults to $HOME/$BACKUP_DIR”
   }

   while [ "$1" != "" ]; do
   case $1 in
   -h | –help ) usage
   exit
   ;;
   -u | –user ) shift
   USER=$1
   ;;
   -d | –database ) shift
   DB=$1
   ;;
   -p | –password ) shift
   export PGPASSWORD=$1
   ;;
   -r | –remote ) shift
   REMOTE=$1
   ;;
   -R | –remote-destination ) shift
   REMOTE_DIR=$1
   ;;
   -L | –local-destination ) shift
   LOCAL_DIR=$1
   ;;
   esac
   shift
   done
   if [ "$REMOTE" = "" ]; then
   usage
   exit
   fi

   LOCAL_DIR=${LOCAL_DIR:=”$HOME/$BACKUP_DIR”}
   REMOTE_DIR=${REMOTE_DIR:=”~/$BACKUP_DIR”}
   USER=${USER:=`whoami`}
   DB=${DB:=$USER}
   DEST=”$LOCAL_DIR/$DB.$DATE.bz2″

   mkdir -p $LOCAL_DIR 2>/dev/null
   `$DUMP -U $USER $DB | bzip2 > $DEST`
   ssh $REMOTE mkdir -p $REMOTE_DIR 2>/dev/null
   scp $DEST $REMOTE:$REMOTE_DIR

   [/cci]

   
   

2. Make the file executable:
   
   [cci lang="bash"]
   chmod 755 pg-backup.sh
   [/cci]
   
   
3. If you don’t have one already, create an ssh key pair for the account that will be running the cron (‘root’ in mycase). I set the key type to RSA / 2048 bits. When prompted for a password by the keygen creator, leave it blank.
   
   [cci lang="bash"]
   ssh-keygen -t rsa -b 2048
   [/cci]
   
   
4. Now take the id_rsa.pub key that was created in the previous step ( by default, it is placed in ~/.ssh/ ) and put it on the remote machine that we want to copy the backups to. It will need to be placed in a specific file called “authorized_keys” in the ‘.ssh’ directory of the user you will be ssh-ing in as. Thus, if you are ssh-ing into the remote machine as “jsmith”, put the key in a file called “authorized_keys” in /home/jsmith/.ssh/. The key should be placed into ‘authorized_keys’ as one line. Make sure to also enable passwordless ssh on the remote box, else the script won’t automatically be able to scp the file over.
   
   You can test this users ability to ssh into the remote machine without being prompted by a password using simple ssh test:
   
   [cci lang="bash"]
   ssh jsmith@[remoteserver.com]
   [/cci]
   
   Only continue to the next step when you get password-less ssh working!
   
   
5. Next, create a cronjob to execute the script periodically. As root, you can use the “crontab -e” command to edit/create a crontab account. I’ve chosen every hour, on the 15th minute:
   
   [cci lang="bash"]
   15 */1 * * * sh /root/pg-backup.sh -r [username]@[offsite.com] -u [dbusr] -p [dbpass] -d [dbname] -R [remote server path]
   [/cci]
   
   
6. You may want to confirm that your server time is correct, as the backup file names will be date stamped with their time of creation. Also confirm that the backup files made it into the local and remote machine directories. And ofcourse, test the integrity of the created backup files by untaring/ un-bzipping them and restoreing them in a scratch database.
   
   

Hope this was helpful! I’ll be back next week with a similar script solution for MySQL users.