current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 3 down vote favorite

I've downloaded the latest copy of my heroku database with

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

And this gives a file with the following type on my local machine:

$ file db/latest.dump
    db/latest.dump: PostgreSQL custom database dump - v1.12-0

I'm trying to import this into my local Postgres DB (my_db) using psql. However, I'm getting lots of errors:

$ psql my_db < db/latest.dump
    ...
    psql:db/latest.dump:119425: invalid command \?A~??ܣb?{#?+????LS??
    psql:db/latest.dump:119426: invalid command \D%!ѡ/F??g????A???dC????|2?M?$?8GTJ??c?E?;??֛Sh??S?[NP?f?2?+H?W????k
    ... [thousands of lines]
    psql:db/latest.dump:261719: ERROR:  invalid byte sequence for encoding "UTF8": 0xba

The command psql -f db/latest.dump my_db fails the same way.

What needs to be done in order to import this file locally into a new database with the same schema, etc?

share|improve this question
up vote 7 down vote accepted

I was able to use pg_restore to solve the problem:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump
share|improve this answer
2  
I don’t think you can use the backup file with psql: the Heroku “backup file uses the custom format option in pg_dump”, and the “custom-format dump is not a script for psql, but instead must be restored with pg_restore”. – matt Apr 29 at 22:37
    
BTW, if you were on a current PostgreSQL version, psql would've told you that you were trying to restore a custom-format dump and should use pg_restore. – Craig Ringer Apr 30 at 5:25
1  
@CraigRinger where would it have said that? I'm using 9.4 and didn't see it. – tyler Apr 30 at 14:40
    
@tyler Ah, might only do it for psql -f dumpfile. I think the patch made it into 9.4... – Craig Ringer May 1 at 0:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.