ruby on rails - 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.