up vote 5 down vote favorite
1

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 14 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 '15 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 '15 at 5:25
1  
@CraigRinger where would it have said that? I'm using 9.4 and didn't see it. – tyler Apr 30 '15 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 '15 at 0:03
2  
thank you so much, this command is very helpful. – Wilker Lucio Jan 6 '16 at 17:21

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.