Reputation:
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
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