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

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

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.

Heroku's PGBackups actually uses pg_dump behind the scenes, and the "custom format" is actually pg_dump's custom format (-Fc parameter), not Heroku's own custom format.

This means you can use pg_restore, which is part of Postgres, to restore your Heroku backup into another database directly:

pg_restore -d mydatabase my_dump_file.dump

In addition, if you call pg_restore without specifying a database to restore to, it'll print SQL statements to standard out, so you can turn your Heroku backup into a SQL file that way:

pg_restore my_dump_file.dump > sql_statements.sql

for people like me that stumble into this problem in 2020:

heroku pg:backups:capture -a app-name
heroku pg:backups:download -a app-name

the tool will actually tell what command to use after the capture. To get SQL from your latest.dump file:

pg_restore -f sqldump.sql latest.dump

and that's it.