Postgres recovery from .dump file: Invalid byte sequence for "UTF8" encoding

Question

I downloaded the latest copy of my heroku database using

$ 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 am trying to import this into my local Postgres database (my_db) using psql. However, I get a lot 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 psql -f db/latest.dump my_db does not work the same way.

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

Answer 1

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