Tuesday, 19 July 2011

Backing Up a Brightbox DB Server

I've just finished setting up the backups for one of our Brightbox database servers, and I thought it might be interesting to share how are we doing them.

As we work a lot with Ruby we decided to use two gems: Backup and Whenever; to manage the backups.

Backup allows us to easily define which database to backup, where to store the backups, if we want the backup to be compressed and which type of notification should be used.

Whenever allows us to manage our server's Cron tasks with ease, allowing us to write the necessary tasks in Ruby.

For this server we decided to compress our backups with Gzip, to transfer them to Amazon S3 and to use email as our notification system. So our system was setup like this:

ruby 1.8.7
gem backup, 3.0.16
gem whenever, 0.6.8
gem mail, 2.2.19
gem fog, 0.7.2 #to handle the S3 storage

We are using Ruby 1.8.7 because it the default in the server and we didn't feel the need to change it.

After having all the necessary tools installed we created the config files. We want to backup our databases daily, monthly and yearly, so we need three backup files. Unfortunately the Backup gem doesn't seem to handle multiple databases in the same config file, so we created three files for each database.

The configuration is very straightforward. You just need to add the details of your database, the details of the S3 storage and of the mail notifications.

After having created the config files we created the schedule.rb file for the whenever gem.

Note that for the cron job to be able to execute the backup command we need to set the environment variable PATH. In the following lines the gem will go through the existing database names and add a line in crontab to execute each of the type of backups (daily, monthly and yearly).

To add these tasks to cron we just need to run:

whenever -w "[a_name]".

And by running the following command we can see the crontab list:
crontab -l

Which might look something like:

So this is how we are backing up this database server. How are you backing up yours?

If you have any questions, or suggestions about this please let me know in the comments. =)

I just realised that I forgot to mention the Server details:
Operating System: Ubuntu 8.04 LTS
Database Server: PostgreSQL 9.0