CODEWITHSUNDEEP
sqldatabasepostgresqlencodingutf-8
user4600953
user4600953

Reputation:

PostgreSQL 9.5 ERROR: invalid byte sequence for encoding "UTF8": 0xc3 0xee

Recently we migrated all databases from PostgreSQL version 8.4 on Debian 5 to PostgreSQL version 9.5 on different server running CentOS 6.5 using pg_dump and pg_restore utilities. Database's encoding is SQL_ASCII Tables contain huge encoded data. Remaining databases seems to work normally, but only specific one.

When I try to fetch data from database on new server I receive:

megaart_www=# select * from data_tbl limit 10;
ERROR:  invalid byte sequence for encoding "UTF8": 0xc1 0xe0

To be sure configurations on both server are identical I run these commands. Results are identical.

postgres=# SHOW client_encoding;
 client_encoding 
-----------------
 UTF8
(1 row)
postgres=# SHOW server_encoding;
 server_encoding 
-----------------
 UTF8
(1 row)

Database's encoding:

megaart_www=# show server_encoding;
 server_encoding 
-----------------
 SQL_ASCII
(1 row)

Database server configurations are identical, but continue to receive errors.

Upvotes: 0

Views: 10885

Answers (1)

d1ll1nger
d1ll1nger

Reputation: 1701

So your server_encoding is SQL_ASCII on the new server, but your client_encoding is UTF8? Basically it's saying UTF8 doesn't know a translation for those bytes. I imagine if you make your client_encoding match: SET client_encoding = 'SQL_ASCII' and repeat the query, it will work fine. Whether you really want your data to be stored as SQL_ASCII, that's a question that you need to answer.

Server encoding is configured at the instance creation time, so if you need it to match your old server aka UTF8, you'll need to recreate the instance and reload the data. Client encoding can be changed at the session level. It's the translation of these two parameters which causes the errors.

Upvotes: 2

Related Questions