Re: client_encoding issue with SQL_ASCII on 8.3 to 10 upgrade

On 04/16/2018 08:16 AM, Keith Fiske 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.

If I remember correctly:

https://www.postgresql.org/docs/8.3/static/app-pgcontroldata.html
> 
> -- 
> Keith Fiske
> Senior Database Engineer
> Crunchy Data - http://crunchydata.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com