Migrating Postgresql server to SQL Cloud and SQL_ASCII encoding

[Brenden Cruikshank]

Hi All,

I am attempting to migrate a Postgres database server from our datacentre that's been around since the 90s (its now running  8.1.21) however the encoding type is "SQL_ASCII".

The error I get is:

postgres=>  CREATE DATABASE dbtest  ENCODING = 'SQL_ASCII';

ERROR:  encoding "SQL_ASCII" does not match locale "en_US.UTF8"

DETAIL:  The chosen LC_CTYPE setting requires encoding "UTF8".

Is there anything I can do to enable SQL_ASCII? 

I thought that ASCII was  subset of UTF8 so I was thinking this should be compatible.

I've dumped the DB, and changing the encoding to UTF8 (using sed -i -e 's/SQL_ASCII/UTF8/g' db-prod.sql) as the file is too big to open. 

However this resulted in import errors when copying our production database dump into a development project with things like.

ERROR:  invalid byte sequence for encoding "UTF8": 0x96

Has anyone done this before? 

[Katayoon (Cloud Platform Support)]

Hi Brenden,

There is a likelihood that you have some Windows-1252-encoded text in your old database (0x96 encodes “dash” in that encoding). This issue is already reported to the PostgreSQL Issue tracker and it is recommended that you edit dump file there.

So, I recommend that you change your database encoding first and then follow the steps on Importing Data into Cloud SQL. As noted in your duplicate post, you may find many discussion threads like this one on Stack Overflow [4] on how to change the database encoding.

If the issue persists, you may post your full detailed question to Stack Overflow, using the supported Cloud tags.