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

Assuming you have a DATABASE_URL configured in your environment, there is a far simpler method:

heroku run 'pg_dump $DATABASE_URL' > my_database.sql

This will run pg_dump in your container and pipe the contents to a local file, my_database.sql. The single quotes are important. If you use double quotes (or no quotes at all), DATABASE_URL will be evaluated locally rather than in your container.

If your whole purpose is to load the contents into a local database anyways, you might as well pipe it straight there:

createdb myapp_devel  # start with an empty database
heroku run 'pg_dump -xO $DATABASE_URL' | psql myapp_devel

The addition of -xO avoids dumping GRANT, REVOKE, and ALTER OWNER statements, which probably don't apply to your local database server. If any of your COPY commands fail with the error ERROR: literal carriage return found in data (mine did), see this answer.

It's quite possible this didn't work two and a half years ago when this question was originally asked, but for those looking for a way to easily get a dump of your Heroku Postgres database, this appears to be the simplest possible way to do this today.

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:[email protected]: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