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?

Tyler · Accepted Answer · 2015-04-29 22:29:57Z

17

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

answered Apr 29, 2015 at 22:29

Tyler

11.5k10 gold badges72 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

matt Over a year ago

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”.

Craig Ringer Over a year ago

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.

Tyler Over a year ago

@CraigRinger where would it have said that? I'm using 9.4 and didn't see it.

Craig Ringer Over a year ago

@tyler Ah, might only do it for psql -f dumpfile. I think the patch made it into 9.4...

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related