Change Database Collation, Ctype in Postgresql

Question

how do I change Collation, cType to - en_IN from en_US.UTF-8

                              List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres

my current postgresversion is 8.4 ive installed it using

sudo apt-get install postgresql-8.4 postgresql-contrib-8.4

im doing this in my ubuntu amazon server ec2

Answer 1

My recommendation:

1. take a pg_dumpall

2. re-initialize the db cluster, making sure the locale information is correct

3. restore your dump.

I have found that sometimes it is possible that one may have to create a db with template template0 (-T template0 from bash or WITH TEMPLATE template0 from psql) to use a non-init-db locale.