In this mini post I’ll show you how to solve this Ruby on Rails error message “ActiveRecord::StatementInvalid (PG::CharacterNotInRepertoire: ERROR: invalid byte sequence for encoding “UTF8”: 0xc2” which may appears in the logs of rails applications connected to postgresql server. When we run certain url on our rails applications it gives: {“status”:”500″,”error”:”Internal Server Error”}, in the logs I found the above error. It seems that the postgresql server not using UTF8 as it’s default encoding. So, I did check on the encoding used in my existing databases using the below command:
$ psql -Upostgres -W -h localhost
Password for user postgres:
psql (9.5.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------------+----------+-----------+---------+-------+-----------------------
mimastech | postgres | SQL_ASCII | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| postgres=CTc/postgres
(4 rows)
As you see the default encoding for template0, template1, and mimastech is SQL_ASCII
Now, We need to change the encoding of our database to UTF8 to fix our rails app.
Solution
In short, you cannot do this with only phpPgAdmin or SQL without risking existing data corruption. You have to export all data, create database with correct encoding and restore exported data.
This is how you should proceed from your Linux terminal:
- create database dump:
$ pg_dump -U postgres -d your_database > your_database.pgsql
this will save your database in pgsql format, in encoding you currently have.
- delete database (or rename it):
$ psql -h localhost -U postgres -W -c 'DROP DATABASE your_database;'
if you have enough storage to do it I recommend leaving old database until you make sure everything is OK with new one, rename it:
$ psql -h localhost -U postgres -W -c 'ALTER DATABASE your_database RENAME TO your_database_backup;'
- create database with new encoding:
$ psql -h localhost -U postgres -W -c "CREATE DATABASE your_database ENCODING 'UTF8' TEMPLATE template0;"
- import data from dump created before:
$ psql -U postgres -d your_database < your_database.pgsql
you need to set psql client encoding to one you had in old database.
Changing encoding on-the-fly isn’t possible as it would require rewriting most of internal database data which is almost equal to recreating db way I described.
It is possible to just alter internal postgres information about database and any new data after this alteration will be saved correctly, however your existing data might get corrupted.
We finished here, but we need check on our newly created database’s encoding, run the following command:
$ psql -Upostgres -W -h localhost
Password for user postgres:
psql (9.5.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------------+----------+-----------+---------+-------+-----------------------
mimastech | postgres | UTF8 | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| postgres=CTc/postgres
(4 rows)
Everything is just perfect.
For more info about Backup and restore Postgresql database check our previous posts: Backup and Restore PostgreSQL Databases
I hope this article is good enough for you.
See you in other articles.
If You Appreciate What We Do Here On Mimastech, You Should Consider:
- Stay Connected to: Facebook | Twitter | Google+
- Support us via PayPal Donation
- Subscribe to our email newsletters.
- Tell other sysadmins / friends about Us - Share and Like our posts and services
We are thankful for your never ending support.
