reli
12/6/2017 - 8:07 AM

encoding error fix

encoding error fix

RROR:  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.


    Solution

=================================================================================================================================

Ok, below steps resolved the problem:

First, we need to drop template1. Templates can’t be dropped, so we first modify it so t’s an ordinary database:

        UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Now we can drop it:

        DROP DATABASE template1;

Now its time to create database from template0, with a new default encoding:

        CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

Now modify template1 so it’s actually a template:

        UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Now switch to template1 and VACUUM FREEZE the template:

        \c template1

        VACUUM FREEZE;

Problem should be resolved.

=====================================

# convert createdb's template to UTF8
echo "UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';" | psql -U postgres
echo "drop database template1;" | psql -U postgres
echo "create database template1 with template = template0 encoding = 'UTF8';" | psql -U postgres
echo "update pg_database set datacl='{=c/postgres,postgres=CTc/postgres}' where datname='template1';" | psql -U postgres
echo "UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';" | psql -U postgres

# export and reimport as UTF8
pg_dump -U uniiverse --encoding utf8 mydatabase -f mydatabase_utf8.sql
createdb -U postgres -E utf8 mydatabase_utf8
psql -U postgres -f mydatabase_utf8.sql -d mydatabase_utf8
echo "ALTER DATABASE mydatabase RENAME TO mydatabase_ascii" | psql -U postgres
echo "ALTER DATABASE mydatabase_utf8 RENAME TO mydatabase" | psql -U postgres