Creating a Latin1 PostgreSQL database in Ubuntu 10.04LTS

I've encountered this problem several times already and I've finally stumbled upon a solution.

The Problem:

We were tasked to move a postgresql database from an centos based server to a new ubuntu based server. The database was in latin1 encoding.

On the new ubuntu based server, creating a database with latin1 encoding results in an error.
Command: createdb -E LATIN1 --template template0 trial
Result:
createdb: database creation failed: ERROR:  encoding LATIN1 does not match locale en_PH.utf8
DETAIL: The chosen LC_CTYPE setting requires encoding UTF8

The Fix:

First we needed to modify /var/lib/locales/supported.d/local and add the line:
en_PH.ISO-8859-1 ISO-8859-1

Next we had to regenerate the locale files with:
sudo dpkg-reconfigure locales

Afterwards we were able to create a latin1 database with:
createdb -E LATIN1 --template template0 --locale en_PH.ISO-8859-1 trial

I hope you found the post useful. You can subscribe via email or subscribe via a feed reader to get relevant updates from this blog. Have a nice day.