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

Question

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 1

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

UPDATE: on more recent versions of postgres, the following command is required (thanks to comment from PatKilg)

pg_restore latest.dump -f - > sql_statements.sql 

Answer 2

Here's what worked for me:

heroku pg:backups:capture
heroku pg:backups:download
pg_restore latest.dump -f latest.sql
psql -f 'latest.sql' -d '<DEV_DB_NAME>'

Explanation:

1. First we create a snapshot of the database on Heroku
2. Then we download the snapshot as 'latest.dump' (the name can be changed using -o '<name>.dump')
3. Convert the binary dump into plain SQL, which can be imported without raising "pg_dump: error: aborting because of server version mismatch"
4. Import the file into the local database

Of course, if your running version of postgresql is compatible with Heroku's, heroku pg:pull DATABASE_URL <DEV_DB_NAME> is simpler to type and remember.

Answer 3

pg_dump accepts a connection string so you don't need to deconstruct it manually like mentioned here: https://stackoverflow.com/a/22896985/3163631.

Let's say your connection string looks like this (I randomized the username and pass and added fillers for the remaining. The "shape" of the connection string is correct):

postgres://Nb6n8BTA4rPK5m:DzEPtwZUkJfgbMSdYFUbqupvJeEekihiJNzqGXa3wN2pmYRGcLQ8Sa69ujGn2RSkb@ec2-00-000-000-000.compute-1.amazonaws.com:5432/j4aaaaaaaaaam1

Even though it is in the postgres://<username>:<password>@<host_name>:<port>/<dbname> format, you can use it directly like so:

pg_dump postgres://Nb6n8BTA4rPK5m:DzEPtwZUkJfgbMSdYFUbqupvJeEekihiJNzqGXa3wN2pmYRGcLQ8Sa69ujGn2RSkb@ec2-00-000-000-000.compute-1.amazonaws.com:5432/j4aaaaaaaaaam1 > output.sql

Maybe this was not possible with pg_dump at the time Alex(https://stackoverflow.com/users/3457661/alex) answered in 2014.

Answer 4

You could just download the Heroku dump file and convert it into plain text format.

In newer versions, directly redirecting the output of pg_restore to an SQL file won't work. Doing so will produce an error:

pg_restore my_dump_file.dump > my_dump_file.sql
pg_restore: error: one of -d/--dbname and -f/--file must be specified

Instead, to output the result in plain text format, -f should be used:

pg_restore my_dump_file.dump -f my_dump_file.sql

This will convert the heroku "custom database dump format" to "plain text format".

Then import this file:

psql -d my_local_database -f my_dump_file.sql

Answer 5

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.

Answer 6

Heroku pg:backups:capture
Heroku pg:backups:download

Taken from https://devcenter.heroku.com/articles/heroku-postgres-import-export. Now you have a binary file. To obtain the file in plain text format, the following worked for me. Note: You will need to install PostgreSQL.

pg_restore latest.dump > latest.sql

Answer 7

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.

Answer 8

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:passkja83kd8@ec2-117-21-174-214.compute-1.amazonaws.com: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