Fixing PostgreSQL locale issues on Ubuntu/Debian

26 Jul 2014

PGError: ERROR: encoding UTF8 does not match locale en_US
DETAIL: The chosen LC_CTYPE setting requires encoding LATIN1.

You are here probably because you encountered an error like the one above. After a bit of searching I found and tried various different methods from random articles and gists. Although none worked for me as well as what I’m about to document. Which is why I’m blogging about this for future reference.

Although before we proceed, a word of caution - Backup your database. This has NOT been tried in production so use at your own risk!

1) Update the default locale on your system

Note: These commands are specific to Debian/Ubuntu.
Source: https://blog.lnx.cx/2009/08/13/fixing-my-missing-locales/

$ locale-gen en_US.UTF-8
$ update-locale LANG=en_US.UTF-8

2) Now let’s recreatetemplate1</source> based on the new locale

First we’ll need to switch to the database user. Typically that’ll be “postgres”

$ sudo su postgres
$ psql

OR

$ psql -U postgres

Next issue the following set of commands in order:

UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';

\c template0

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

DROP DATABASE template1;

CREATE DATABASE template1 ENCODING = 'utf8' TEMPLATE = template0 LC_CTYPE = 'en_US.utf8' LC_COLLATE = 'en_US.utf8';

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

\c template1

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template0';

\q

If you didn’t see any errors, you should now be all set. You can now go ahead and log out of the postgres user account:

$ exit