opensoul.org

Automatically backing up your remote database on deploy

February 9, 2007 code 3 min read

Ever had a migration fail when you’re deploying a sparkling new release of your snazzy web app, leaving your database in an inconsistent state? I have. Fortunately, a faulty migration has never completely hosed my production database, but I have had to ssh in, comment some lines out of a migration, and re-run it.

Now, I can rest assured that I’ll never have the experience of hosing my production database without being able to recover it. Here are Capistrano recipes to backup your remote production database whenever you run migrate on your remote database.

require 'yaml'

desc "Backup the remote production database"
task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{application}.dump.#{Time.now.to_i}.sql.bz2"
  file = "/tmp/#{filename}"
  on_rollback { delete file }
  db = YAML::load(ERB.new(IO.read(File.join(File.dirname(__FILE__), 'database.yml'))).result)['production']
  run "mysqldump -u #{db['username']} --password=#{db['password']} #{db['database']} | bzip2 -c > #{file}"  do |ch, stream, data|
    puts data
  end
  `mkdir -p #{File.dirname(__FILE__)}/../backups/`
  get file, "backups/#{filename}"
  # capistrano < 1.4
  # `rsync #{user}@#{roles[:db][0].host}:#{filename} #{File.dirname(__FILE__)}/../backups/`
  delete file
end

desc "Backup the database before running migrations"
task :before_migrate do
  backup
end

The backup recipe is adapted from ones presented by Caboo.se and Bojan Mihelac. I modified it to use my local database.yml. Thanks to Daniel Morrison, who came up with the idea of running the backup task before migrating.

This content is open source. Suggest Improvements.

@bkeepers

avatar of Brandon Keepers I am Brandon Keepers, and I work at GitHub on making Open Source more approachable, effective, and ubiquitous. I tend to think like an engineer, work like an artist, dream like an astronaut, love like a human, and sleep like a baby.