Martin Schurig
Application Engineer based in Barcelona, Spain.

During the last couple of months working a lot with Ruby on Rails, I have noticed that there are some great lines of code I am very happy to have spent time working on, because I always need them in new projects.

One example is a simple rake task that helps you pulling the production database to your local development database. My college Sascha and I were tired of manually dumping the production database - so we started writing the task for postgres. Because of an other friend of mine who uses the mysql adapter, I made it working for him as well.

hold on, it is “simple” you said? Yes, it is!

TL;DR

rake db:pull does do the magic.

So, what exactly does it do?

Basically, it connects to your production server and runs a script that dumps your database into a file on your local machine. In a second step it imports it.

The script uses the environment information provided in the config/database.yml file.

Since I wrote this post to share these lines of code with you, here they are:

lib/tasks/db_pull.rake

Doing it with Postgres

# lib/tasks/db_pull.rake
namespace :db do
  desc 'Pull production db to development'
  task :pull => [:dump, :restore]

  task :dump do
    dumpfile = "#{Rails.root}/tmp/latest.dump"
    puts 'PG_DUMP on production database...'
    production = Rails.application.config.database_configuration['production']
    system "ssh user@server.tld 'PGPASSWORD=\"#{production['password']}\" pg_dump -U postgres #{production['database']} -h #{production['host']} -F t' > #{dumpfile}"
    puts 'Done!'
  end

  task :restore do
    dev = Rails.application.config.database_configuration['development']
    dumpfile = "#{Rails.root}/tmp/latest.dump"
    puts 'PG_RESTORE on development database...'
    system "pg_restore --verbose --clean --no-acl --no-owner -h 127.0.0.1 -U #{dev['username']} -d #{dev['database']} #{dumpfile}"
    puts 'Done!'
  end
end

the same functionality with mysql

# lib/tasks/db_pull.rake
namespace :db do
  desc 'Pull production db to development'
  task :pull => [:dump, :restore]

  task :dump do
    dumpfile = "#{Rails.root}/tmp/latest.dump"
    production = Rails.application.config.database_configuration['production']
    puts 'mysqldump on production database...'
    system "ssh user@server.tld 'mysqldump -u #{production['username']} --password=#{production['password']} -h #{production['host']} --add-drop-table --skip-lock-tables --verbose #{production['database']}' > #{dumpfile}"
    puts 'Done!'
  end

  task :restore do
    dev = Rails.application.config.database_configuration['development']
    abort 'Live db is not mysql' unless dev['adapter'] =~ /mysql/
    abort 'Missing live db config' if dev.blank?
    dumpfile = "#{Rails.root}/tmp/latest.dump"
    puts 'importing production database to development database...'
    system "mysql -h #{dev['host']} -u root #{dev['database']} < #{dumpfile}"
    puts 'Done!'
  end
end

I hope this rake task also makes your life much easier as it did for me! :)

- Something to say? Feel free to tweet me @martinschurig!
Click here to read my other articles.