Re: Invalid byte sequence errors on DB restore

On 3/15/20 11: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?
> 
> Regards,
> Samuel Smith
> 
> 

Our issue actually turned out to be that a couple of our production 
database had the encoding set to SQL_ASCII while all of our development 
servers had UTF-8. This meant in some cases where we would restore a 
production database into development (for testing or bug hunting), there 
would be a failure to parse the backup file. A similar issue to this 
blog post: 
https://www.endpoint.com/blog/2017/07/21/postgres-migrating-sqlascii-to-utf-8

So our fix was to dump the affected production databases using the 
LATIN1 encoding for pg_dump followed by destroying and recreating the 
database and setting its new encoding to UTF-8. Then we could restore 
the data using the pg_dump file with LATIN1 encoding.

Regards,
Samuel Smith