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.

I haven't tried with rake db:setup on a rails app. This is a setup I'm trying in order to run all of the Rails test suite against itself. In order to get it to run the postregsql tests this database has to exist.

I'm running into the same problem. Did you ever find a fix?

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!