Today I learn how to fix the encoding problem in postgresql.
During setting up my machine with postgresql and sinatra app, when I try to create Database
using rake db:create I got this error.
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"System_test", "pool"=>5, "username"=>"forellana", "password"=>nil}
PGError: ERROR: 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.
: CREATE DATABASE "System_development" ENCODING = 'unicode'
After a long search I am able to resolve the issue. While resolving the issue I follow the given steps:
Login to the postgresql CLI
psql databasename; (ie: psql postgressql)
Try to drop template1 but templates can’t be dropped, so I modify it to ordinary database
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
Then dropped the database
DROP DATABASE template1;
Next created a new 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';
After preparing a template database, or making any changes to one, it is a good idea to perform VACUUM FREEZE in that database. If this is done when there are no other open transactions in the same database, then it is guaranteed that all rows in the database are “frozen” and will not be subject to transaction ID wraparound problems. This is particularly important for a database that will have datallowconn set to false, since it will be impossible to do routine maintenance VACUUM in such a database.
\c template1
VACUUM FREEZE;
(OPTIONAL) If you don’t want anyone connecting to this template, set datallowconn to FALSE:
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';
Hope this will help you. Happy learning.