Tuesday, March 31, 2015

ADF and PostgreSQL: how to change database collation to non-english on debian

Few days ago I encountered following issue:
I have to install and run my ADF application based on PostgreSQL 9.4 on Debian Wheezy. After I installed Postgre I noticed I can't create database with collation different than en_US.UTF8.
"Unfortunatelly" my application needs Polish Collation pl_PL.UTF8.

After some googling I found solution, the steps of which I describe bellow:

1. Log in as root (or sudo) user and edit file /etc/locale.gen
 # nano /etc/locale.gen

2. Uncomment desired locale:

3. Run command  locale-gen
# locale-gen

Generating locales (this might take a while)...
  en_US.UTF-8... done
  pl_PL.UTF-8... done

Just now the new locale should be available in your PL/SQL.

But optionally you can change default locale of the all server (cluster). To do this run following steps:

4. Drop the PostgreSQL cluster (this will destroy all cluster data, so do this only on the fresh installation)

# pg_dropcluster --stop 9.4 main

5. Recreate the cluster with new default locale

# pg_createcluster --locale pl_PL.UTF-8 --start 9.4 main

In my case the new cluster is re-created with new configuration. The listen port was changed to 5433 and to correct this I must go to /etc/postgre/9.4/main/postgre.conf and set old port value. After service restrat all works perfect.

No comments:

Post a Comment