dbcp

Copy Postgres or MySQL databases between application environments.

Setting an employee up to work on a web application for the first time is time consuming. Tools like Vagrant have made it easy to get your environment setup, but you still need to get your relational database setup. In rails you can load your db/schema.rb and hope that db/seeds.rb is well curated, but seldom has enough to let a developer hit the ground running. Working with your production database while developing is extremely convenient. The parity helps preview database performance. It also makes investigating data-specific bugs much easier.

The goal of dbcp is to make copying databases between development, staging, and production environments as easy as copying a file on your local disk. It's an adapter for platform-specific utilities like pg_dump and mysqldump, simplifies lookup of credentials using storage mechanisms you're already using, and handles transfer of dump files between hosts.

A word of caution

Depending on your application, your production database may contain sensitive personal information like financial or health data. Give careful consideration to the risks of using production data on staging and development environments, and whether it's acceptable to use a tool like this in your application's workflow.

Treat your production database like a loaded gun. Consider employing some of these safe guards:

• Use a separate account that only has read access.
• Access a replication "follower" instead of the master.
• Access a completely separate database that is periodically populated from production, but is updated to use fake values for sensitive information.

Installation

dbcp is a stand-alone utility. To install, just run:

$ gem install dbcp

You should not need to include dbcp in your Gemfile.

Usage

To copy the production database to the development environment, simply run:

$ dbcp production development

Environment credentials can be defined in the following providers:

config/database.yml

Rails defines credentials for its database environments in a file at config/database.yml. By default this file is generated with only development and test environments, but any additional environments added will be leveraged by dbcp. Although this is a rails convention, dbcp parses this file outside of any framework, so it will work even if you're using this convention in another framework.

The database export or import can be executed on a remote host over ssh and then copied between environment hosts if you specify the remote host via an ssh_uri entry in the database.yml. This is helpful if the database host only allows connections from specific servers. If your ssh_uri optionally includes a path to your application root on the remote server, dbcp will load the database credentials from the remote server's config/database.yml.

Example config/database.yml:

# Local database
development:
  adapter:  postgresql
  encoding: unicode
  pool: 5
  database: development_database
  username: development_username
  password: development_password

# Remote database, credentials provided locally, executed from remote host over ssh
staging:
  adapter:  postgresql
  database: staging_database
  username: staging_username
  password: staging_password
  ssh_uri:  ssh://deploy@staging.example.com

# Remote database, credentials fetched over ssh, executed from remote host over ssh
production:
  ssh_uri:  ssh://deploy@production.example.com/www/production.example.com/current

$ dbcp staging development

URI

You can use a database URI in place of an environment name as follows:

$ dbcp postgres://my_username:my_pass@db.example.com/my_database development

Capistrano v3

If you deploy your application via Capistrano, dbcp will lookup and invoke for a matching task name in your capistrano configuration to read the remote ssh and path information for the primary :db server role. It will ssh to that server both to read the remote remote #{deploy_to}/current/config/database.yml and execute the database export from.

Example, with config/deploy/staging.rb

server 'staging.example.com', user: 'staging', roles: %w{web app db}
set :deploy_to, '/www/staging.example.com'

and separately defined development environment will allow you to run:

$ cap staging development

Roadmap

The following features are pending:

Providers:

• Heroku, environment name inferred from git remotes

Refactors:

• Handle pg_restore warnings
• Better logging
• Better help

Please open an issue or send a pull request if you see a weird error, a database or environment definition you want supported, or anything you want to see improved.