Postgres – Encoding issues

Just lately, I’ve been dealing with more and more encoding issues with applications, and this one was a bit of a pain. I couldn’t find a decent solution online, so when I finally worked this out for myself, I decided to post the results in the hope it’ll help someone else later.

The problem occurred when trying to setup Postgres 9.1 for an application, but to have the cluster in UTF-8 rather than the standard LATIN1 that is used by the locale en_US. When trying to setup the cluster, I was getting this error message:

The encoding you selected (UTF8) and the encoding that the selected locale uses (LATIN1) do not match

So, here is my solution to getting a UTF-8 cluster up without mucking about with system locales, etc:

* PLEASE NOTE, IF YOU HAVE DATA IN THE DATABASE, BACK IT UP FIRST AND RESTORE IT, THIS PROCEDURE WILL DROP EVERYTHING *

sudo bash
apt-get install postgresql
su postgres
pg_dropcluster --stop 9.1 main
pg_createcluster --start -e UTF-8 --locale en_US.utf8 9.1 main
psql -U postgres
ALTER USER postgres WITH PASSWORD 'password';
\q
exit

I hope this helps someone and good luck!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.