After installing Odoo 8 (also known as OpenERP), and other web apps which runs on PortgreSQL databases, you’ll be asked to create a new database for your company. However, when creating the database, Odoo returns the following error message:

DataError: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT: Use the same encoding as in the template database, or use template0 as template.

PostgreSQL UTF8 Encoding Incompatible with template1

The issue occurred as Odoo is attempting to create a PostgreSQL database with UTF-8 encoding. When creating new database, PostgreSQL database system works by copying an existing standard system database, namely template1 or template0. Thus template1 and/or template0 database is the “template” from which new databases are made, including the character set encoding, LC_CTYPE (character classification) and LC_COLLATE (collation string sort order) locale settings.

To fix the issue, convert the character set encoding and locale of the standard template databases to UTF-8. There are several ways to do so, as list below. Run the SQL queries in psql, the interactive terminal-based front-end to PostgreSQL.

To run psql, uses the following command if you have root access:

# su - postgres
$ psql

Else, run this:

$ sudo -u postgres psql

Or this:

$ sudo su - postgres 
psql
Recreate template1 with UTF-8 Encoding from template0
update pg_database set datallowconn = TRUE where datname = 'template0';
\c template0
update pg_database set datistemplate = FALSE where datname = 'template1';
drop database template1;
create database template1 with encoding = 'UTF-8' lc_collate = 'en_US.UTF8' lc_ctype = 'en_US.UTF8' template = template0;
update pg_database set datistemplate = TRUE where datname = 'template1';
\c template1
update pg_database set datallowconn = FALSE where datname = 'template0';
\q
Update template0 and template1 Encoding to UTF8
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template0';
update pg_database set encoding = 6, datcollate = 'en_US.UTF8', datctype = 'en_US.UTF8' where datname = 'template1';
Backup and Switch to UTF-8 Encoding While Restore
Note: This method may not work on all system. Run the following commands in postgres user environment, before entering psql. If you’re in psql, type \q and hit Enter to exit from psql.

$ sudo -u postgres pg_dumpall > /tmp/postgres.sql
$ sudo -u postgres pg_dropcluster --stop cluster_version cluster_name
$ sudo -u postgres pg_createcluster --locale en_US.UTF-8 --start cluster_version cluster_name
$ sudo -u postgres psql -f /tmp/postgres.sql

Replace cluster_version cluster_name with actual cluster version and name. For example, ‘pg_dropcluster –stop 9.4 main’ and ‘pg_createcluster –locale en_US.UTF-8 –start 9.4 main’. You can check your clusters’ version and name with pg_lsclusters command.

You May Also Interested In: