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!
