Rake DB:Create & Postgres encoding error

While experimenting with Vagrant and VirtualBox in my local environment I ran into a nasty little error: "PG::Error: ERROR: encoding UTF8 does not match locale en_US DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1." For me, this occurred while attempting to run "rake db:create." In case you ever run into this error, I'll walk through my steps for solving it.

Disclaimer: At the time of writing this post, I'm very new to using PostgreSQL. Please back-up your system before running any of the commands shown in this post, I'm not a database or Postgres expert but you can break your system by performing any of the tasks below incorrectly. Proceed with caution.

To understand the error message, you first need to understand a little about how Postgres works. When creating new databases Postgres references a template database to fill in settings that weren't specified by the command that was issued. By default, your installation of Postgres will have two template databases, named "template0" and "template1."

"Template0" is both a backup template, in case the other templates have been corrupted, and a magic template that doesn't have to obey all of the assumptions of the other templates. In fact, if you were to create your database using "template0" you would completely avoid the error (e.g. "$ createdb --locale=en_US.utf8 --encoding=UTF8 --template=template0 test_db").

Unfortunately for us, Rails does not allow you to specify the template for Postgres (as of version 3.2.11).

For Rails we'll need to update "template1" with the correct LC_CTYPE, locale, and encoding. Since we can't update those attributes, we'll have to drop "template1" and create a new one by cloning "template0."

First you'll need to switch to your database user and login. Since this the db user name is typically "postgres" try "$ sudo su postgres" on the command line. Then open "template1" in Postgres using "$ psql template1".

Next we'll issue a set of commands that will allow us to connect to "template0," drop "template1," recreate "template1" with our new settings, and return everything to normal.

Now use "\q" to exit Postgres and "$ exit" to logoff of the "postgres" user and switch back to your user. Finally, issue the "$ rake db:create" command and everything should be working.

Special thanks to Kevin Murphy's post in the PostgreSQL forums for helping me find the solution.