UI-driven Database Snapshot & Restore
For most people, backing application databases is usually (touch wood) a one-way operation. You take your precautionary measures, employ some kind of hosted or scripted backup solution, and hope that you only infrequently, if ever, have to restore from an old database.
But, what if you want to take a database snapshot at any time, keep a list of such snapshots and restore at will from any of them at any time? And you want to do it from the UI? Sounds far-fetched?
Here is one scenario.
- Your sales guy has lined up multiple demos for your app and he wants to tailor the data and demo aspects for each client.
- Your dev team is too busy to spend time crafting a whole new admin console just to make a whiz-bang demo.
- ssh? mysqldump? redirect output? >, < #@*?
With Rails and Rake, here is how you can make your sales guy happy and
get him to buy you some Mai Tais.
Introduce two new rake tasks, db:snapshot and db:restore which take DIR and NAME from the environment (as well as the Rails environment).
# lib/tasks/db.rake
namespace :db do
desc 'Store a snapshot of the database with the given name'
task :snapshot => :environment do
path = ENV["DIR"] || "db/snapshots"
stamp = Time.now.strftime("%Y%m%d") + Time.now.tv_sec.to_s.slice(-6..-1)
file = ENV["NAME"] || "#{stamp}_snapshot.sql"
dbconf = ActiveRecord::Base.configurations[RAILS_ENV]
dbcmd = "mysqldump -u #{dbconf['username']} -p#{dbconf['password']} "
dbcmd += "#{dbconf['database']} > #{path}/#{file}"
sh dbcmd
end
desc 'Load database from a previously stored snapshot'
task :restore => :environment do
path = ENV["DIR"] || "db/snapshots"
if ENV["NAME"]
filename = "#{path}/#{ENV["NAME"]}"
dbconf = ActiveRecord::Base.configurations[RAILS_ENV]
dbcmd = "mysql -u #{dbconf['username']} -p#{dbconf['password']} "
dbcmd += "#{dbconf['database']} < #{filename}"
sh dbcmd
else
$stderr.puts "No snapshot name provided. Nothing to do."
end
end
end
Enable controllers to invoke these rake tasks in the background.
# application_controller.rb
protected
def invoke_rake(task, options = {})
options[:rails_env] ||= Rails.env
args = options.map { |k, v| "#{k.to_s.upcase}='#{v}'" }
rake = "rake #{task} #{args.join(' ')} --trace 2>&1 >> #{Rails.root}/log/rake.log &"
system rake
end
Wrap the snapshot storage and lookup in a pseudo-model.
# app/models/snapshot.rb
class Snapshot < ActiveRecord::Base
DIR = Demo.getval('snapshots_dir') || APP_CONFIG[:snapshots_dir]
SUFFIX = APP_CONFIG[:snapshot_suffix] || '.snap.sql'
def self.all
list = []
files = Dir.glob("#{DIR}/*#{SUFFIX}")
files.each do |snap|
list << [
snap.split('/').last.split(/#{SUFFIX}/).first,
File.stat(snap).ctime
]
end
list
end
def self.for(name)
name.gsub!(/[\s\&\@\!\#\$\%\^\*\(\)\+]/, '-')
{:dir => DIR, :name => name + SUFFIX}
end
end
And put the pieces together as you want through appropriate views and controllers. (Thanks, Railscasts and Craig Ambrose)
Happy New Year!
But, wait, you want some previous year back? Sorry, we can’t do that yet.