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