99

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?

tokhi
  • 21,044
  • 23
  • 95
  • 105

6 Answers6

260

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.

tokhi
  • 21,044
  • 23
  • 95
  • 105
  • 2
    @tokhi This is working solution. I'm not to much familiar for posgresql but my only small step 0:`sudo -u postgres psql postgres` for debian or similar OS. – croonx Aug 23 '16 at 19:58
  • I prefer the locales solution below – Hackeron May 24 '22 at 00:16
49

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
  ...
mihai
  • 37,072
  • 9
  • 60
  • 86
  • 1
    I didn't have `template0` in my config, if I place it I get: `PG::Error: ERROR: permission denied to copy database "template0" : CREATE DATABASE "my_database_test" ENCODING = 'unicode' TEMPLATE = "template0"` – tokhi May 24 '13 at 14:19
  • that's strange...does it behave the same for the `development` env ? – mihai May 24 '13 at 14:30
  • 3
    Could anyone explain the difference between template0 and template1 and why this works? – Jake Jan 15 '16 at 04:17
  • @Jake see https://www.postgresql.org/docs/10/static/manage-ag-templatedbs.html – everything makes sense after reading this page :-) – Markus Jul 24 '18 at 11:30
14

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.

Pablo Bianchi
  • 1,824
  • 1
  • 26
  • 30
lee
  • 417
  • 3
  • 13
7

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/--encoding 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.)

Pablo Bianchi
  • 1,824
  • 1
  • 26
  • 30
littleforest
  • 2,057
  • 21
  • 29
4

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

encoding: unicode

to:

encoding: SQL_ASCII

just that and it all works.

Yakob Ubaidi
  • 1,846
  • 2
  • 20
  • 23
  • 2
    This is better than the accepted solution but I'm new to this stuff so I might be not aware of the affects of changing unicode to SQL_ASCII. – indieNik Mar 27 '18 at 20:08
  • 2
    Using SQL_ASCII is not a great default. – Doug Apr 04 '18 at 04:26
  • 2
    This means any special characters used by non English languages, French accents, even including the degree C symbol will not be able to be stored. Not a good idea in this day and age. – blissweb Feb 12 '21 at 06:53
2

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

Hussain Niazi
  • 579
  • 1
  • 3
  • 21