Re: Invalid byte sequence errors on DB restore

On 3/16/20 9:33 AM, Adrian Klaver wrote:
> On 3/15/20 9:18 PM, Samuel Smith wrote:
>> My current DB backup routine is just to call pg_dump and pipe to gzip.
>> We recently started to get a failure to restore (which is basically
>> just using psql -f on the pg_dump file) with the following errors:
>>
>> invalid byte sequence for encoding "UTF8": 0xa0
>>   and
>> invalid byte sequence for encoding "UTF8": 0xd7 0x20
>>
>>
>> This is on a pg 9.2.24 instance. Any tips to troubleshoot?
>
> What are the locale and encodings set to for the instance and databases
> in it?
>
>>
>> Regards,
>> Samuel Smith
>>
>>
>
>

The server is in UTF8. The file made with pg_dump used 'SQL_ASCII', but
setting it to UTF8 (via SET client_encoding ) did not help either.
Having the pg_dump encoding set to 'latin1' seems to allow the file
created it by it to be loaded via psql -f and everything seems to work.
Is there any bad side to setting the encoding on pg_dump to latin1?

For the record, the problem characters are:
https://www.htmlsymbols.xyz/unicode/U+00D7
and
https://www.htmlsymbols.xyz/unicode/U+00A0

But those characters were in many places and not all were issues. They
only fail depending on the characters that precede it which makes it
complicated.

Thanks,
Samuel Smith