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?

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.

Make sure you have the correct setup in your database.yml file. You should use template0, as the error suggests:

test:
  adapter: postgresql
  encoding: unicode
  database: your_db_name
  template: template0
  host: localhost
  ...

If you use Debian, when you install the postgresql package it will use your default locale to create the template1 database. If you have not configured your OS to use UTF-8 as a default locale, you will encounter this error.

In addition to the above solutions, if you are on a new installation and have no active databases, you can remove the postgresql package and set your default locale to UTF-8. The advantage of this method is you can omit locale information when creating databases in the future.

dpkg-reconfigure locales

If you don't see the desired locale, install the locales-all package

apt-get install locales-all

Then remove postgresql

apt-get remove --purge postgresql-<version>

Then reinstall or better yet upgrade to a recent release that isn't in Debian stable.

If your postgres installation is new and you haven't populated any databases yet, then you can remove your data directory and re-run the initdb command with the flag to create databases using UTF-8.

Modify this command to match your postgres installation. The -E flag tells what character encoding should be the default. Other character encodings are listed here.

/usr/local/pgsql/bin/initdb -E UTF8 -D /usr/local/pgsql/data -U postgres

It should error and tell you that the data directory is not empty. Follow the instructions and remove the directory, then re-run the command. (Or, remove the data directory before you start, but it's always nice to see the instructions for yourself.)

As for me, I just change the line from database.yml:

encoding: unicode

to:

encoding: SQL_ASCII

just that and it all works.

I had a similar issue. My database.yml was like this:-

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: chatapp_development

test:
  <<: *default
  database: chatapp_test

production:
  <<: *default
  database: chatapp_production
  username: chatapp
  password: <%= ENV['CHATAPP_DATABASE_PASSWORD'] %>

I added template: template0 to the default settings

default: &default
  adapter: postgresql
  template: template0
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

And it worked