When struggling with postgresql and utf8/latin

This is just a little information dump for me, you probably won’t have these issues.

Lately I tried to switch the database in a Rails app from SQLite to PostgreSQL.
However, after installing postgres and setting up my database.yml file on my vagrant ubuntu(precise32) box, I fire

1
rake db:create

and it greets me with

1
2
3
4
5
Couldn't create database for {"adapter"=>"postgresql", "encoding"=>"unicode", 
"database"=>"stuff_development", "pool"=>5, "username"=>"stuffer", "password"=>nil}
PG::Error: ERROR: encoding "UTF8" does not match locale "en_US"
DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1".
: CREATE DATABASE "stuff_test" ENCODING = 'unicode'

There are some encoding issues, yeah!
After some googeling I tried the solution provided by this post which unfortunatly ended up with this:

1
2
3
postgres=# create database template1 with owner=postgres template=template0 encoding='UTF8';
ERROR: encoding "UTF8" does not match locale "en_US"
DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1".

But adding some additional options did the trick

1
2
create database template1 with owner postgres encoding='UTF-8' 
lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;

In summary, when you get such errors try these steps

1
2
sudo su postgres
psql
1
2
3
4
5
update pg_database set datistemplate=false where datname='template1';
drop database Template1;
create database template1 with owner=postgres encoding='UTF-8'
lc_collate='en_US.utf8' lc_ctype='en_US.utf8' template template0;

update pg_database set datistemplate=true where datname='template1';