My Notepad

On on of my linux box ( Centos 4) I was using PostgreSQL 8.4 for database. The database has been using for production DB for a webapp on that server, and i need to install a testing webapp against the same DB for some testing.


The new webapp requires a new database with UTF-8 encoding enabled. Normally this is done by using,
createdb mydb -E UNICODE -U xxx

But i got error message saying utf-8 was not supported by the LC_CTYPE value in use, which was en_US.iso885915 at that moment. It turned out that, when initdb was initially executed to create data directory for postgresql, the env LC_CTYPE will be checked and frozen within data structure of postgres. Only when the value is "C" or "POSTIX", any encoding format can be used, otherwise only very limited encoding can be used. In my case, since en_US.iso885915 has been selected by default therefore only encoding iso885915 was supported. More information can be found at :

http://www.postgresql.org/docs/8.0/interactive/multibyte.html

In order to enable utf-8. Backup all the existing data using pq_dumpall:
pg_dumpall > all.out

Then rename the "data" dir to "data-old", for example.

Type "locale" in your shell to check your current encoding format.

In order to let postgresql to support UNICODE, we need to change env LC_CTYPE and LC_COLLATE to "C" or "POSTIX" before use initdb to initialize database structure. Edit either /etc/profile or ~.bashrc to add lines like followings:

export LC_CTYPE=C
export LC_COLLATE=C

Now, if you type "locale" again, you should have something like this:

LANG=en_US.iso885915
LC_CTYPE=C
LC_NUMERIC="en_US.iso885915"
LC_TIME="en_US.iso885915"
LC_COLLATE=C
LC_MONETARY="en_US.iso885915"
LC_MESSAGES="en_US.iso885915"
LC_PAPER="en_US.iso885915"
LC_NAME="en_US.iso885915"
LC_ADDRESS="en_US.iso885915"
LC_TELEPHONE="en_US.iso885915"
LC_MEASUREMENT="en_US.iso885915"
LC_IDENTIFICATION="en_US.iso885915"
LC_ALL=

In order to create the new data structure, use

initdb -E UNICODE

to create a fresh data folder with all the default data structure. The -E parameter specify default encoding format for all future created databases. Obviously the configuration files will then need to be copied from "data-old" to the new created "data". This can be done easily by,

cp data-old/*.cfg data

Now it's time to reload the backup data, use,

pqsql -f all.out

To load all the old data.

Some error message might be thrown, e.g. user postgres alreayd existed. No need worry as long as all the data tables/records/sequences,etc. have been recovered.


Use "psql -l" to check the encoding format of all the tables. They should all have been set as UNICODE. With LC_CTYPE value set as "C", any kind of encoding can be specified to create a database.