Thread: pg_restore errors

Hi List;

Trying to restore a pg_dump I get this:

pg_restore: ERROR:  invalid byte sequence for encoding "UTF8": 0xae
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 imm_lookup, line 2
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  invalid byte
sequence for encoding "UTF8": 0xae
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 imm_lookup, line 2


This is a dump file that was handed to me from a client. I don't know anything
about what the dump flags were or what the db encoding was. Is there a way to
find out (I'm assuming this is an encoding mis-match) from the dump file.

I did do a pg_restore -l and got this:

<snip>
;
; Archive created at Wed Nov 28 21:04:49 2007
;     dbname: practice
;     TOC Entries: 867
;     Compression: -1
;     Dump Version: 1.10-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 4 bytes
;     Dumped from database version: 8.1.9
;     Dumped by pg_dump version: 8.1.4
;
;
; Selected TOC Entries:
;
5; 2615 2200 SCHEMA - public postgres
2911; 0 0 COMMENT - SCHEMA public postgres
2912; 0 0 ACL - public postgres
589; 2612 43134 PROCEDURAL LANGUAGE - plpgsql
14; 1255 43135 FUNCTION public dtos(date) postgres
27; 1255 43136 FUNCTION public get_session_alta_user() postgres
15; 1255 43137 FUNCTION public plpgsql_call_handler() postgres
16; 1255 43138 FUNCTION public plpgsql_validator(oid) postgres
23; 1255 43139 FUNCTION public process_appointment_audit() postgres
24; 1255 43140 FUNCTION public process_billing_audit() postgres
25; 1255 43141 FUNCTION public process_billing_detail_audit() postgres
26; 1255 43142 FUNCTION public process_patient_audit() postgres
1524; 1259 43146 TABLE public address postgres
2913; 0 0 SEQUENCE SET public address_rec_no_seq postgres
</snip>


Any thoughts?

Thanks in advance..

/Kevin

> Trying to restore a pg_dump I get this:
>
> pg_restore: ERROR:  invalid byte sequence for encoding "UTF8": 0xae
> 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 imm_lookup, line 2
> pg_restore: [archiver (db)] error returned by PQendcopy: ERROR:  invalid
> byte
> sequence for encoding "UTF8": 0xae
> 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 imm_lookup, line 2

I had a similar issue a week or 2 ago - turns out my original database was
using SQL_ASCII encoding, not UTF-8.

Can your client tell you what encoding they're using? Otherwise, just try a
few different ones. Someone else might have a tricky way to work out what
the encoding should be based on the dump (but I don't ;))

Cheers,
~p


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Kevin Kempter <kevin@kevinkempterllc.com> writes:
> Trying to restore a pg_dump I get this:
> pg_restore: ERROR:  invalid byte sequence for encoding "UTF8": 0xae
> 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".

> This is a dump file that was handed to me from a client. I don't know
> anything about what the dump flags were or what the db encoding
> was. Is there a way to find out (I'm assuming this is an encoding
> mis-match) from the dump file.

Well, the fact that you got that message says that the original database
was, or thought it was, in UTF8 encoding: pg_dump would have propagated
that fact into a "set client_encoding" command in the dump script.

I suppose that this data got loaded into the database while it was being
run by one of the early 8.1.x releases that didn't check encoding very
carefully :-(.  You're going to have to guess what the real encoding is;
querying the client about his typical locale settings might help.

When trying to fix up the data, keep in mind that what you have now is
likely a mix of real UTF8 and whatever-this-is.

            regards, tom lane