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

Question

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?

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