Template database is being created on SQL_ASCII and should be UTF8 (unicode)

[mergulhao]

It causes this error when using default rails rake db:create

Couldn't create database for {"adapter"=>"postgresql", "database"=>"finance_development", "pool"=>5, "username"=>"postgres", "password"=>"postgres", "host"=>"localhost"}
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 "finance_test" ENCODING = 'utf8'

[fabriziomello]

The problem is because you current cluster was created with SQL_ASCII encoding. This encoding is not a real encoding because a database with this property can store any kind of encoding (ISO-8859-1, UTF8, ...) without any check, and it's depends how the client is configured.

IMHO we must recreate the cluster inside the postgresql box and rebuild it.

Best regards,

[mergulhao]

Hi @fabriziomello thanks for the comment. This was the default setting after I installed postgresql through apt-get. Do you know how to recreate this cluster?

[fabriziomello]

Hi @mergulhao you're welcome. To recreate this cluster with UTF-8 encoding you must do that (as root):

1) Upgrade your OS

$ apt-get upgrade

2) Drop the old cluster (maybe you must do a backup of confs and databases). WARNING! This step will remove all of your databases and cannot be rolled back.

$ pg_dropcluster --stop 9.1 main

3) Create new cluster

$ export LC_ALL=en_US.UTF-8 $ pg_createcluster -E UTF-8 9.1 main

You must reconfigure your "pg_hba.conf" and "postgresql.conf" in this new cluster.

[RamonPage]

Hi, @fabriziomello.

What you mean with 'You must reconfigure your "pg_hba.conf" and "postgresql.conf" in this new cluster.'? What needs to change in these files?

[mergulhao]

Hi @RamonPage, I'll be working on this to fix the vagrant box and 'll update here as soon as possible.

[RamonPage]

:thumbsup:

[fabriziomello]

Hi @RamonPage, you must reconfigure your "pg_hba.conf" and "postgresql.conf" because when you recreate the cluster this config files will be different of your previous templates [1].

[1] https://github.com/mergulhao/vagrant-precise64-postgresql/tree/master/manifests/postgresql/templates