How can I get a plain text postgres database dump on heroku?

Due to version incompatibilities of my postgres database on heroku (9.1) and my local installation (8.4) I need a plain text sql database dump file so I can put a copy of my production data on my local testing environment.

It seems on heroku I can’t make a dump using pg_dump but can instead only do this:

$ heroku pgbackups:capture
$ curl -o my_dump_file.dump `heroku pgbackups:url`

…and this gives me the “custom database dump format” and not “plain text format” so I am not able to do this:

$ psql -d my_local_database -f my_dump_file.sql

Answer

You could just make your own pg_dump directly from your Heroku database.

First, get your postgres string using heroku config:get DATABASE_URL.

Look for the Heroku Postgres url (example: HEROKU_POSTGRESQL_RED_URL: postgres://user3123:[e-mail]:6212/db982398), which format is postgres://<username>:<password>@<host_name>:<port>/<dbname>.

Next, run this on your command line:

pg_dump --host=<host_name> --port=<port> --username=<username> --password --dbname=<dbname> > output.sql

The terminal will ask for your password then run it and dump it into output.sql.

Then import it:

psql -d my_local_database -f output.sql