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: INCREMEN…

Accepted Answer

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.

How to import a Heroku PG dump into local machine

Solution

Problem