Install/Change/Check locale for new or productive PostgreSQL Cluster

Introduction

Anyone who have ever installed a PostgreSQL Database Server ran into that issue. The locale is not correct. By default its "C" on OSX or "en_US.UTF-8" on most linux distributions.

You can do better and install PostgreSQL from scratch (the first time) with the correct locale or easily change the locale on your already productive cluster. Heres how you can do this.

New cluster installation

Example works for Debian and it's derivates (tested with Squeeze and Wheezy):

Execute the following statement. It first sets the desired locale and then installs PostgreSQL itself

export LC_ALL="de_DE.UTF-8" && apt-get install postgresql-9.1

Your fresh PG-Cluster will be installed with the locale you exported before. It's just magic!

Change locale from a running cluster

Quick `n Dirty Procedure

1. Stop everything using the database
2. pg_dumpall > backup_today.sql
3. pg_dropcluster 9.1 main
4. pg_createcluster --locale de_DE.UTF-8 9.1 main
5. service postgresql start
6. psql -f backup_today.sql postgres
7. Restart the services using the DB

Explained steps

1. First make sure, all services using the DB are stopped (Webservices for ex.)
2. Make a dump of the whole PG-Cluster with pg_dumpall > backup_today.sql. The easiest way is to use the postgres user, for ex. su - postgres from root
   2.1. If you want you can backup the old Database Files with mv /var/lib/postgresql/9.1 /var/lib/postgresql/9.1_old
3. Back in root drop the old cluster config with pg_dropcluster 9.1 main. Don't care if it complains about the missing cluster folder - you just moved it
4. Create the new Cluster config with the desired locale pg_createcluster --locale de_DE.UTF-8 9.1 main. For your convenience use old configuration names again (9.1 and main)
5. If everything went well, start the new cluster with service postgresql start
6. Go back into postgres user environment and restore all the data to the new cluster with psql -f backup_today.sql postgres
7. Restart all the stopped services from step 1 and check if everything still runs fine.

How can i check the locale?

1. Log into postgres user by su - postgres from root
2. Run psql to get into the Database
3. Type and run \l there to list all your databases
4. In the list you can see the locale and encoding of all the databases. Also your old/migrated should have the new one