Change Database Collation, Ctype in Postgresql

It's not necessary to recreate the whole database cluster. You need however to recreate your database.

Run createdb with these options (man createdb):

   -E encoding, --encoding=encoding
       Specifies the character encoding scheme to be used in this
       database. The character sets supported by the PostgreSQL server
       are described in Section 22.3.1, “Supported Character Sets”, in
       the documentation.

   -l locale, --locale=locale
       Specifies the locale to be used in this database. This is
       equivalent to specifying both --lc-collate and --lc-ctype.

   --lc-collate=locale
       Specifies the LC_COLLATE setting to be used in this database.

   --lc-ctype=locale
       Specifies the LC_CTYPE setting to be used in this database.

It seems you really can't change the collation of an existing database:

=> ALTER DATABASE dbname SET "Collate" To Russian;
ERROR:  unrecognized configuration parameter "Collate"

Note that you can set collation for a table or a column, see a good tutorial on collations in PostgreSQL.

I had to change to POSIX.UTF-8. I managed that with the following commands:

su postgres
psql
\l
update pg_database set datcollate='POSIX.UTF-8', datctype='POSIX.UTF-8' where datname='databasename';
\l

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.