How to import a Heroku PG dump into local machine

Question

I'm trying to import my production Heroku database into my development machine.

My local db is PostgreSQL.

First, I'm exporting the dump from Heroku to my machine

curl -o latest.dump `heroku pgbackups:url` 

Then, I try to drop the local db with rake db:drop and then I create the empty database again by using rake db:create.

The problem I'm getting is when actually trying to import the dump to the database

psql -d app_development -U myusername -f mydumpfile.sql 

I begin seeing errors like this

psql:latest.dump:24: ERROR:  syntax error at or near "PGDMP" LINE 1: PGDMP         ^ psql:latest.dump:28: ERROR:  syntax error at or near "" LINE 1:     INCREMENT BY 1         ^ psql:latest.dump:36: ERROR:  syntax error at or near "" LINE 1:     id integer NOT NULL,         ^ psql:latest.dump:40: ERROR:  syntax error at or near "" LINE 1:     INCREMENT BY 1         ^ psql:latest.dump:45: ERROR:  syntax error at or near "" LINE 1:     id integer NOT NULL,         ^ psql:latest.dump:49: ERROR:  syntax error at or near "" LINE 1:     INCREMENT BY 1  ...   psql:latest.dump:1601: invalid command \S4???(?̭?A?|c?e0<00K?A?}FϚ?????A(??~?t?I?????G(?    K???l??k"?H?ȁ?ͲS?,N*?[(@??a5J??j} psql:latest.dump:1602: invalid command \??k???|??w???h? psql:latest.dump:1603: invalid command \=??????o?h? psql:latest.dump:1609: invalid command \????^.?????????E???/-???+??>#?ؚE?.2)Ȯ&????    g????"7},_??]?:?f?Tr|o???)?p????h?KO?08[Rqu???|3?cW?ڮ?ahbm??H?H8??$???2?a?-أ psql:latest.dump:1613: invalid command \D!qVS???L??*??׬R??I!??? psql:latest.dump:1614: invalid command \??-?}Q psql:latest.dump:12565: ERROR:  invalid byte sequence for encoding "UTF8": 0xb0 

Any idea what is happening this and how to solve it?

Answer 1

You see errors because psql tries to interpret SQL queries when you're actually giving him a compressed dump (that's what heroku uses).

While you can't read the dump, pg_restore -O latest.dump gives you valid SQL you could pipe to psql but the easy solution is the following one :

pg_restore -O -d app_development latest.dump 

Notes :

• Use -O because you probably don't use the random username of your remote heroku postgres db.
• Heroku doesn't recommend to use taps but I don't know how really risky it is.

Answer 2

Follow these 4 simple steps in your terminal
(Heroku Dev Center):

1. Create a backup copy of your database:

   $ heroku pg:backups capture DATABASE_NAME 

2. Download the copy from Heroku (to your local machine) using curl:

   $ curl -o latest.dump `heroku pg:backups public-url` 

3. Load it*:

   $ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U YOUR_USERNAME -d DATABASE_NAME latest.dump 

   • get YOUR_USERNAME and choose the desired database from your config/database.yml file.
   • DATABASE_NAME can be your development/test/production db (Ex. mydb_development)

That's it!