From: | Samuel Smith <pgsql(at)net153(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Invalid byte sequence errors on DB restore |
Date: | 2020-03-19 22:12:13 |
Message-ID: | 7813515f-0872-9cfa-7cbb-b6675610744e@net153.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | pabloa98 | 2020-03-19 22:13:53 | Re: Could postgres12 support millions of sequences? (like 10 million) |
Previous Message | Rob Sargent | 2020-03-19 21:50:38 | Re: Could postgres12 support millions of sequences? (like 10 million) |