syncing database content with mina

Imagine you are working on an application which is already in production, or being used by a lot of people.

While you’re working on a new version, problems occur in production, which are hard to reproduce for you because all you’ve got on your local machine is a static dataset.

Often you’ll find yourself creating database dumps from production and importing them on your local machine. A repetitive and somewhat time consuming task.

Now you might be using capistrano and there are already working solutions out there for this problem. STOP READING.

But in case you’re using mina (which you should ;)) here are two example tasks which can sync down the database content for a typical Ruby on Rails application using PostgreSQL or MySQL.

Note that these examples can be easily adjusted to work with non-rails setups. Really anything which stores the information about the database anywhere accessible is just fine.

Usage

The example snippets below can be used like this once you’ve added them to your deploy.rb:

bundle exec mina sync:db

PostgreSQL

RYAML = <<-BASH
function ryaml {
  ruby -ryaml -e 'puts ARGV[1..-1].inject(YAML.load(File.read(ARGV[0]))) {|acc, key| acc[key] }' "$@"
};
BASH

namespace :sync do
  task :db do
    isolate do
      invoke :environment

      queue RYAML
      queue "USERNAME=$(ryaml #{deploy_to}/shared/config/database.yml #{rails_env} username)"
      queue "PASSWORD=$(ryaml #{deploy_to}/shared/config/database.yml #{rails_env} password)"
      queue "DATABASE=$(ryaml #{deploy_to}/shared/config/database.yml #{rails_env} database)"
      queue "PGPASSWORD=$PASSWORD pg_dump -U $USERNAME $DATABASE -c -f #{deploy_to}/dump.sql"
      queue "gzip -f #{deploy_to}/dump.sql"

      mina_cleanup!
    end

    %x[scp #{user}@#{domain}:#{deploy_to}/dump.sql.gz .]
    %x[gunzip -f dump.sql.gz]
    %x[#{RYAML} psql -d $(ryaml config/database.yml development database) -f dump.sql]
    %x[rm dump.sql]
  end
end

Here’s a gist for the PostgreSQL example.

Note I’m using pg_dump to create a dump of the used PostgreSQL database. Also note the -c flag which makes pg_dump emit drop table statements to ease the import on your local machine.

MYSQL

RYAML = <<-BASH
function ryaml {
  ruby -ryaml -e 'puts ARGV[1..-1].inject(YAML.load(File.read(ARGV[0]))) {|acc, key| acc[key] }' "$@"
};
BASH

namespace :sync do
  task :db do
    isolate do
      invoke :environment

      queue RYAML
      queue "USERNAME=$(ryaml #{deploy_to}/shared/config/database.yml #{rails_env} username)"
      queue "PASSWORD=$(ryaml #{deploy_to}/shared/config/database.yml #{rails_env} password)"
      queue "DATABASE=$(ryaml #{deploy_to}/shared/config/database.yml #{rails_env} database)"
      queue "mysqldump $DATABASE --user=$USERNAME --password=$PASSWORD > #{deploy_to}/dump.sql"
      queue "gzip -f #{deploy_to}/dump.sql"

      mina_cleanup!
    end

    %x[scp #{user}@#{domain}:#{deploy_to}/dump.sql.gz .]
    %x[gunzip -f dump.sql.gz]
    %x[#{RYAML} mysql --verbose --user=$(ryaml config/database.yml development username) --password=$(ryaml config/database.yml development password) $(ryaml config/database.yml development database) < dump.sql]
    %x[rm dump.sql]
  end
end

Here’s a gist for the MySQL example.

Important notes

The above example assumes that you are able to ssh on your production/ staging system without the use of a password. I’ve also assumed that you are storing your database credentials in your shared directory, symlinking it to the current version upon deploy.
the ryaml snippet was taken from Toms Coderwall. Thanks, Tom!

That’s it!

If you’ve got questions or feedback I’d be glad to hear them.

May 6, 2013

syncing database content with mina

Usage

PostgreSQL

MYSQL

Important notes