tokhi tokhi - 6 months ago 20
Ruby Question

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

I have installed

postgresql-9.2.4
from the source, now in rails app when I execute:

rake db:create
command I get:

$ 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'
/home/vagrant/my-project/.gems/ruby/1.9.1/gems/activerecord-3.2.12/lib/active_record/connection_adapters/postgresql_adapter.rb:652:in `exec
....
bin/rake:16:in `load'
bin/rake:16:in `<main>'
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", "database"=>"my_db", "host"=>"localhost", "pool"=>5, "username"=>"my_user", "password"=>"my_password"}


any idea?

Answer

Ok, below steps resolved the problem:

  1. 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';

  2. Now we can drop it:

    DROP DATABASE template1;

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

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

  4. Now modify template1 so it’s actually a template:

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

  5. Now switch to template1 and VACUUM FREEZE the template:

    \c template1

    VACUUM FREEZE;

Problem should be resolved.

Comments