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.