Postgres Pro
Downloads
Home   >   mailing lists

Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade - Mailing list pgsql-general

From Vick Khera
Subject Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade
Date
Msg-id CALd+dceYJ9R65w1gorj3joAMo1=hc4+1TmH3EXvyN3q-c+BobQ@mail.gmail.com
Whole thread Raw
In response to client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade  (Keith Fiske <keith.fiske@crunchydata.com>)
Responses Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade
List pgsql-general
Tree view
Hi Keith, 

Not sure if this will help but a couple of years ago I migrated from an SQL_ASCII encoding to UTF8. The data was primarily ASCII with some windows garbage, and a little bit of UTF8 from customers filling out forms that were not specifically encoded anything.

I wrote a utility that in-place scans and updates the tables in your SQL_ASCII-encoded database and ensures that everything is 100% UTF8 NFC at the end. For us, there were some characters in some bizarre local encodings, and we had to either toss or make educated guesses for them.

After the cleaning, you dump with client encoding UTF8, then restore into the final database with UTF8 encoding.

You can find it on my github along with documentation and tests to verify it works: https://github.com/khera/utf8-inline-cleaner

On Mon, Apr 16, 2018 at 11:16 AM, Keith Fiske <keith.fiske@crunchydata.com> wrote:
Running into an issue with helping a client upgrade from 8.3 to 10 (yes, I know, please keep the out of support comments to a minimum, thanks :). 

The old database was in SQL_ASCII and it needs to stay that way for now unfortunately. The dump and restore itself works fine, but we're now running into issues with some data returning encoding errors unless we specifically set the client_encoding value to SQL_ASCII. 

Looking at the 8.3 database, it has the client_encoding value set to UTF8 and queries seem to work fine. Is this just a bug in the old 8.3 not enforcing encoding properly?

The other thing I noticed on the 10 instance was that, while the LOCALE was set to SQL_ASCII, the COLLATE and CTYPE values for the restored databases were en_US.UTF-8. Could this be having an affect? Is there any way to see what these values were on the old 8.3 database? The pg_database catalog does not have these values stored back then.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade
Next
From: Keith Fiske
Date:
Subject: Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade