Change cluster encoding

_$: psql -c "\l"
                                   List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 postgres    | postgres | LATIN1   | en_US       | en_US       | 
 template0   | postgres | LATIN1   | en_US       | en_US       | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres         +
             |          |          |             |             | postgres=CTc/postgres

If the encoding is not rigth, we can change the system encoding and then recreate the cluster, but we will lose any database already created.

1) Change the system encoding

_$: update-locale LANG="en_US.UTF-8"

2) Recreate the cluster

_$: service postgresql stop
_$: pg_dropcluster   --stop  9.3 main
_$: pg_createcluster --start 9.3 main
_$: psql -c "\l"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    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

Change template1’s encoding

_$: psql
postgres=#  UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';
postgres=#  \c template0
template0=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
template0=# DROP database template1;
template0=# CREATE database template1 WITH template = template0 encoding = 'UTF8';

Should the last command fail, we can try with this one:

template0=# CREATE database template1 WITH encoding = 'UTF-8' lc_ctype = 'en_US.utf8' lc_collate = 'en_US.utf8' template = template0;

And continue:

template0=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
template0=# \c template1
template1=# UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template0';

Here it is in a single file:

encoding.sql:
-------------
UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';
UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
DROP database template1;
CREATE database template1 WITH template = template0 encoding = 'UTF8';
-- Use only if the previous command fails:
-- CREATE database template1 WITH
-- template = template0
-- encoding = 'UTF8'
-- lc_ctype = 'en_US.UTF-8'
-- lc_collate = 'en_US.UTF-8'
-- ;
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template0';