heroku pg:transfer to Migrate Heroku Databases
Date: 18 September, 2012
Development of most applications takes place in several disparate environments with the most common pattern being dev-staging-production. While it's necessary for the source versions in each environment to differ it is quite useful to retain some level of data synchronicity between the environments (for example, to populate your local database with production data to diagnose a bug).
When managing environments on Heroku the recommendation has been to use Taps and
heroku db:push to transfer data to and from the remote Postgres database. While Taps aimed to be database-agnostic (allowing you to import/export between different database vendors) this came at the expense of robustness and maintainability. The fragility of the tool is evident on Stackoverflow.
pg:transfer CLI plugin is a more stable and predictable tool that automatically transfers data between two Postgres databases using native pg tools and protocols. You should consider
heroku pg:transfer an immediate replacement for
As more developers adopt the Twelve-Factor tenet of environment parity the need to perform data migrations across database vendors is eliminated. This allows the ability to use a database's native import/export tools, resulting in a much more predictable data migration process.
Most apps on Heroku are already using the incredible [Heroku Postgres service](http://postgres.heroku.com) and _should_ be running Postgres locally as well. If not, the [Postgres.app project](http://postgresapp.com/) will get you up and running on OSX in minutes.
Install the plugin by running the following from the terminal:
$ heroku plugins:install https://github.com/ddollar/heroku-pg-transfer
Confirm the plugin installation by running the
pg:transfer --help command:
$ heroku pg:transfer --help Usage: heroku pg:transfer transfer data between databases -f, --from DATABASE # source database, defaults to DATABASE_URL on the app -t, --to DATABASE # target database, defaults to local $DATABASE_URL
pg:transfer has a very simple purpose - to transfer data from one Postgres db to another. As such it only requires two arguments, the source and target database locations (in its vernacular the "to" and "from").
If you're in the root folder of an app already deployed to Heroku the "from" and "to" will assumed to be at the location specified by the
DATABASE_URL config var on the remote app and the local environment variable
DATABASE_URL for the local db, respectively. In other words, by default,
heroku pg:transfer will export from your Heroku database and import into your local development database.
$ heroku pg:transfer Source database: HEROKU_POSTGRESQL_BLACK (DATABASE_URL) on someapp.herokuapp.com Target database: someapp on localhost:5432 ! WARNING: Destructive Action ! This command will affect the app: someapp ! To proceed, type "someapp" or re-run this command with --confirm someapp > someapp pg_dump: reading schemas pg_dump: reading user-defined tables ...
If the local database you want to import to isn't set in your environment you can quickly do so just for the
pg:transfer command with the
$ env DATABASE_URL=postgres://localhost/someapp-dev heroku pg:transfer
Apps using [Foreman](http://ddollar.github.com/foreman/) for local process management can quickly provision the correct environment variables from the `.env` file with: `source .env && heroku pg:transfer`.
If you want to push data from your local environment to your Heroku database you'll need to specify the
from flags to reverse the default direction of the transfer. For added convenience
pg:transfer is aware of the Heroku Postgres
COLOR naming scheme.
$ heroku config | grep POSTGRES HEROKU_POSTGRESQL_JADE_URL: postgres://ads8a8d9asd:email@example.com:5432/resource123 $ heroku pg:transfer --from $DATABASE_URL --to jade --confirm someapp ...
While pushing data from a local db to a remote one is of limited usefulness a more common use-case is to transfer between two remote databases. For instance, populating a staging or test environment with production data.
To transfer data between databases on different applications specify the full connection info of the target database in the
$ heroku pg:transfer --to `heroku config:get DATABASE_URL -a app-staging` --confirm someapp Source database: HEROKU_POSTGRESQL_JADE on someapp.herokuapp.com Target database: kai89akdkaoa on ec2-23-21-45-234.compute-1.amazonaws.com:5742 pg_dump: reading schemas pg_dump: reading user-defined tables ...
heroku config:get command is used to fetch the full PG connection info for the target app.
pg:transfer CLI plugin is a much more stable and flexible tool for migrating data between Postgres databases than the Taps gem and the corresponding
db:push commands. Use it in concert with a local Postgres database to manage your data transfer needs.