PG::Error: ERROR: new encoding (UTF8) is incompatible

FixEncodingUTF8.md

Some time we got error like this:

$ bin/rake db:create RAILS_ENV="test" PG::Error: 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 "verticals_test" ENCODING = 'unicode'

This how we can resolved

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

sudo -u postgres psql
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;