Postgres pg_restore error: invalid byte sequence for encoding "UTF8": 0xe05c62

Question

Lucky me, I get to play DBA today!

I am migrating a Postgres 8.4 DB from one server to another (both RHEL6). I backed up the schema from the old server and restored it to the new server. Then I did a full database backup and restored it to the new server, but I had two tables that were skipped during the restore. In both cases I used the format=c option with pg_dump and pg_restore.

I am now focusing on those two tables, and i need help! I do a backup on the old server:

pg_dump --format=c --file=mytable.sqlc -a --table public.mytable mydatabase

Now I go to the new server and attempt a restore:

pg_restore -d mydatabase -t mytable --format=c -a -c mytable.sqlc

I'm getting the following errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2241; 0 23420338 TABLE DATA mytable mydatabase_script
pg_restore: [archiver (db)] COPY failed for table "mytable": ERROR: invalid byte sequence for encoding "UTF8": 0xe05c62
HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT: COPY mytable, line 231993
WARNING: errors ignored on restore: 1

The error seems to imply that I have an encoding difference between the old db server and the new one (even though I backed up/restored the schema). I did a:

select * from pg_database where datname = "mydatabase"

And it shows that both databases are UTF-8 encoded. What do I need to check next? Thanks.

• Evan Carroll: 8.4 is unsupported. There are security vulnerabilities for that. It hasn't received a patch in over 3 years.
• Chris C: Yikes. Okay. One issue at a time and then i'll work on upgrading.

Answer 1

Your options are pretty simple, and the error message is explicit. This is the problem

ERROR: invalid byte sequence for encoding "UTF8": 0xe05c62

And this hint pretty much tells you what we would tell you,

HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".

So you have two options,

1. Remove the byte sequence (assumes you may not need it), there is no indicator that you know what it's actually supposed to be anyway.
2. Set the client_encoding to whatever encoding supports that sequence, as UTF-8 doesn't.

You may want to confirm the encoding of your database

SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = 'yourdb';

And confirm that the output encoding (--encoding to pg_dump) supports all of those characters (is the same or a superset). And, that also the new database is either the same encoding or a superset.

Try not to use --format=c, it only adds complexity. If you're having problems use plain, the default.

• Chris C: Thank you, that did the trick! I had looked at the pg_database table and seen that datcollate and datctype were both set to UTF-8, but I had neglected to notice that the encoding (which is displayed as an int, rather than a string) was set to 0 on the old database (ASCII) and 6 on the new database (UTF-8). I was able to fix everything by running the command: update pg_database set encoding = pg_char_to_encoding('SQL_ASCII') where datname = 'mytable' Thanks again!!
• Evan Carroll: You really need to run off of 8.4 and not mess around with it though. Spend the time now and get your self up to 10.x