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 and should be running Postgres locally as well. If not, the Postgres.app project 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 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:firstname.lastname@example.org: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.
Somewhat un-intuitively for a Heroku CLI plugin, you can also use
pg:transfer to transfer data between two databases that are not associated with Heroku. Since the plugin accepts raw connection URLs for both the
--from and the
--to locations you're really not limited in how you use the feature.
As Postgres' fame grows more and more platforms are supporting the database. Transferring data between Engine Yard Postgres databases, between dbs on EY and Heroku, or from EY to your local database is simple. Though you need the
heroku command on your path it's really quite agnostic.
To transfer from EY PG to your local database at the env var
$ heroku pg:transfer --from "postgres://deploy:email@example.com:5433/dbname" --to $DATABASE_URL
To gain ingress to an EY Postgres database, follow the SSH tunnel instructions here. Hence the local
127.0.0.1 connection URL in this example.
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 to robustly manage data transfer between Postgres databases.