58

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
user1187534
  • 789
  • 1
  • 8
  • 12

8 Answers8

81

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
Quentin
  • 1,085
  • 1
  • 11
  • 29
Alex
  • 8,321
  • 1
  • 34
  • 30
  • 3
    Another way: 1) `heroku pgbackups:capture` 2) copy this to the clipboard: `heroku pgbackups:url` 3) `curl -o latest.dump ` – Petrus Repo Jan 02 '15 at 10:07
  • The limitation of `heroku pgbackups` is that it will not run without user auth, even on Heroku within the app. It seems that pg_dump will run with only the DB auth. – B Seven Mar 10 '15 at 17:05
  • 2
    heroku config:get gave me error "Missing argument: KEY", but just heroku config gave that URL info – Straff Apr 15 '16 at 09:30
  • @Alex Does the Postgres url go into the ``? Or does part of it go into the ``? I.e. what do we do with `postgres://user3123...2938`? – Robert Jun 17 '16 at 00:44
  • Update: the format of the url string is: `scheme://username:password@host:port/database` – Robert Jun 17 '16 at 01:43
  • 1
    @Robert The string format is `postgres://:@:/`. To translate the example I gave into pg_dump, it would be `pg_dump --host=ec2-117-21-174-214.compute-1.amazonaws.com --port=6212 --username=user3123 --password --dbname=db982398`. Edit -- Yep, you got it! – Alex Jun 17 '16 at 01:44
  • @Alex i am having run this command pg_dump --host=ec2-54-225-103-167.compute-1.amazonaws.com --port=5432 --username=kelmrouuyipqym --34e96181c91451adbf073c1482c02a067afe6bf8f919d2919465410f4f63072b --dbname=de0pfea769s8q1 > output.sql it gives me error like pg_dump: unrecognized option '--34e96181c91451adbf073c1482c02a067afe6bf8f919d2919465410f4f63072b' – Chintan Mathukiya Aug 02 '18 at 06:54
37

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.

Community
  • 1
  • 1
tobias.mcnulty
  • 1,621
  • 14
  • 15
  • 2
    I would like to double upvote, since the answer solved all my problems including carriage returns. However, I would like to preprocess the original dump (without `--inserts`) to gain better performance on restore. Any ideas? – Stuck Jul 31 '18 at 15:33
  • This was extremely useful to me. Thank you! – ceejayoz Apr 28 '19 at 15:33
  • This is the only correct answer for incompatible PostgreSQL versions locally vs. remote, which is what the OP wanted. For local Windows environments, remember to use double quotes around the command: `heroku run "pg_dump $DATABASE_URL" --app yourapp > db.sql` – gwyn Mar 13 '20 at 15:06
31

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
Tom Carchrae
  • 6,398
  • 2
  • 37
  • 36
Daisy Leigh Brenecki
  • 7,571
  • 6
  • 28
  • 43
  • 3
    This answer should be the correct one. Using `pg_restore my_dump_file.dump` will give you plain text sql statements. Thanks Adam! – Alfredo Cavalcanti Oct 26 '16 at 01:05
  • 3
    the second part of this answer saved my ass! – Chris Scott Sep 18 '18 at 03:26
  • 1
    pg_restore: error: one of -d/--dbname and -f/--file must be specified – Anton Duzenko Jun 26 '20 at 12:55
  • `#!/bin/bash` `HEROKU_APP_NAME=app-name` `OUTPUT_SQL_FILENAME=~/Downloads/myDB.psql` `heroku pg:backups:capture --app $HEROKU_APP_NAME` `heroku pg:backups:download --app $HEROKU_APP_NAME` `pg_restore ./latest.dump -f $OUTPUT_SQL_FILENAME` @timbophillips – Tim Phillips Feb 21 '21 at 13:04
  • 3
    The correct command to generate sql now is `pg_restore my_dump_file.dump -f - > sql_statements.sql` – PatKilg Mar 17 '21 at 22:37
9

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.

Ilya Petrov
  • 131
  • 1
  • 5
1

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.

pdgonzalez872
  • 43
  • 7
1

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.

Goulven
  • 777
  • 9
  • 20
  • Is there a way to use INSERT instead of COPY? – siniradam Aug 26 '22 at 21:58
  • 1
    @siniradam `pg_dump` accepts the `--inserts` option, so change the 3rd line to `pg_restore latest.dump -f latest.sql --inserts`. The [`pg_dump` documentation](https://www.postgresql.org/docs/current/app-pgdump.html) says: _This will make restoration very slow. Any error during restoring will cause only rows that are part of the problematic INSERT to be lost, rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order, use the `--column-inserts` option in that case, though it will be even slower._ – Goulven Aug 28 '22 at 11:41
0
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
Edd
  • 61
  • 1
  • 3
0

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
Ashish Kumar
  • 129
  • 3
  • 8