Deploying database contents using Capistrano

I run a pair of Ruby on Rails sites, http://janeallnatt.co.nz and http://postmoderncore.com. I use Capistrano to deploy updates to both of these sites.

These sites are somewhat unusual for Rails sites, as I consider the database for these sites to be part of what I test locally and deploy. There is no data captured from the production sites. Once I built these sites and got Capistrano working, I realised that the database should be deployed as part of the Capistrano deploy.

I decided to simply dump the entire development database and restore it into my production database, tables and all. This subverts way Rails migrations are normally used, but if I get my migrations wrong, the development database is what I test against, so that’s the state I want my production tables in.

I use mysqldump to get a dump of the data.

mysqldump --user=#{local_database_user} --password=#{local_database_password} #{local_database}

And to load this dump into the production database

mysql --user=#{remote_database_user} --password=#{remote_database_password} #{remote_database} < #{remote_path}

The only other thing I had to work out was how to get the locally dumped file onto my remote server – it proved to be pretty easy

	filename = "dump.#{Time.now.strftime '%Y%m%dT%H%M%S'}.sql"
	remote_path = "tmp/#{filename}"
	on_rollback { 
		delete remote_path
	}
	dumped_sql = `mysqldump --user=#{local_database_user} --password=#{local_database_password} #{local_database}`
	put dumped_sql, remote_path
	run "mysql --user=#{remote_database_user} --password=#{remote_database_password} #{remote_database} < #{remote_path}"

I hooked this in after the deploy:finalize_update Capistrano event. Making for the following additions to my deploy.rb file, including configuration.

#TODO: Should come from database.yml
set :local_database, "postmoderncore"
set :local_database_user, "railsuser"
set :local_database_password, "railsuser"
set :remote_database, "p182r822_pmc"
set :remote_database_user, "p182r822_user"
set :remote_database_password, "T673eoTc4SWb"

after "deploy:finalize_update", :update_database

desc "Upload the database to the server"
task :update_database, :roles => :db, :only => { :primary => true } do
	filename = "dump.#{Time.now.strftime '%Y%m%dT%H%M%S'}.sql"
	remote_path = "tmp/#{filename}"
	on_rollback { 
		delete remote_path
	}
	dumped_sql = `mysqldump --user=#{local_database_user} --password=#{local_database_password} #{local_database}`
	put dumped_sql, remote_path
	run "mysql --user=#{remote_database_user} --password=#{remote_database_password} #{remote_database} < #{remote_path}"
end

There is an issue where you have the new codebase pointing at the old database for a short period of time. For a high visibility site, I’d extend this approach to have multiple databases, so you load a different database for each version of the site. So when you upgrade the version of the site, it atomically switches from the old codebase pointing to the old database, to the new codebase pointing to the new database.

I’m sure that from this base, extension for more complex scenarios would be possible. For example, if you wanted some user generated content, you could restrict the database dump to only dump tables containing non-user generated data.

Published by

Sam Stephens

Experienced software development engineer, passionate about OO patterns, tidy modular code, and understanding the various tensions and contradictions that are involved in navigating life as a business developer, and delivering superior quality results. Contact me.

2 thoughts on “Deploying database contents using Capistrano”

    1. Nice to hear from you Nathan.
      I’ve updated the post with a bit of detail I missed. Capistrano has quite a nice approach to updating where you have a set of directories per version of your deployed site, and symlinks pointing at a particular version, which your web server daemon is pointed at. One of the final steps in the Capistrano deploy is to update the symlinks to point to your new version, resulting in a close to atomic switch to your new version. In theory this means zero downtime. You may need to add a post-update restart whatever web server you’re using to your Capistrano script, to ensure the modifications are picked up.
      A minor issue is that there is a period of time after the database is updated, but before the symlinks are updated when the old site is running with the new database, which of course could be a problem for certain updates. This is an issue you face with Capistrano even when you’re not using my customisations. My additions to the end of my post address this.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>