rails tests postgres

gistfile1.md

I'm trying to run the rails test suite but I keep getting this error:

$ bundle exec rake postgresql:build_databases
createdb: database creation failed: ERROR:  encoding UTF8 does not match locale en_US
DETAIL:  The chosen LC_CTYPE setting requires encoding LATIN1.
createdb: database creation failed: ERROR:  encoding UTF8 does not match locale en_US
DETAIL:  The chosen LC_CTYPE setting requires encoding LATIN1.

Hi Guys, I ran into this problem now based on the way the vagrant precise64 box is setup.

If you run this command:

psql -t -c "\l"

You will see a table showing what encodings exist, in my case LATIN had been installed. To clear the DBs, run the following command:

pg_dropcluster --stop 9.1 main ; pg_createcluster --start --locale en_US.UTF-8 9.1 main

I figured this out from the following link: http://stackoverflow.com/questions/15949783/postgresql-is-being-installed-with-sql-ascii-using-puppet (see the puppet exec at the bottom)

@quintona Thanks for the writeup, was helpful for me when I was running Vagrant on precise64, as you say. The command you posted worked really well.

For future visitors, if you can't afford to re-install postgres, another fix is to add the following to your database.yml:

• encoding: utf8
• ctype: en_US.utf8
• collation: en_US.utf8
• template: template0

OR... to stick with the latin1 encoding (not internationalization friendly):

• encoding: latin1

Explanation of why this works and also what the original error is about:

First thing to know is that the encoding utf8 and the locale en_US don't play nicely together. That's just the way it is.

By default, rails asks postgres to use utf8 as the encoding when doing the "rake db:create" step (which is one of the sub-steps of db:setup) which creates the new database(s). When postgres creates a new db, it actually copies an existing database called "template1", along with it's locale, encoding, and other settings. If you had en_US as your locale when you installed postgres, template1 uses en_US as well.

Remember that rails asks postgres to use utf8, but doesn't say anything about changing the locale. So now what's happening is Rails is asking the en_US locale database to use the utf8 encoding. Again, en_US and utf8 just don't work together. So now the cryptic error is explained. You can't make a database with utf8 encoding and en_US locale. Postgres even tries to be nice by saying "no no... you can't use utf8 with en_US, why don't you use latin1?" The tricky part is knowing that the LC_CTYPE is being copied from this template1, which was "chosen" when postgresql was installed and looked at your current LC_CTYPE environment variable.

The last pieces of the puzzle: LC_CTYPE and LC_COLLATE make up the locale. To override LC_CTYPE and LC_COLLATE, you need to instruct postgres to copy from template0, rather than template1, and supply the desired LC_CTYPE and LC_COLLATE. So how do you do this through rails? If you look deep enough into rails' code, you'll find the actual create_database method (http://bit.ly/19HOob1), where you can find ctype and collation nestled nicely between the other database.yml config options you're used to.

@jeffbmartinez thanks for the detailed explanation!