Thu, 07 Jan 10

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.

  1. Your sales guy has lined up multiple demos for your app and he wants to tailor the data and demo aspects for each client.
  2. Your dev team is too busy to spend time crafting a whole new admin console just to make a whiz-bang demo.
  3. 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.

Thu, 07 Jan 10