PostgreSQL – Change Default Encoding of New Databases To UTF-8 (Optional)

When creating a new database (e.g. with createdb blog) PostgreSQL actually copies a template database. There are two predefined templates: template0 is vanilla, while template1 is meant as an on-site template changeable by the administrator and is used by default. In order to change the encoding of new database, one of the options is to change on-site template1. To do this, log into PostgresSQL shell (psql) and execute the following:

1. First, we need to drop template1. Templates can’t be dropped, so we first modify it so it’s an ordinary database:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

2. Now we can drop it:

DROP DATABASE template1;

3. The next step is to create a new database from template0, with a new default encoding:

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

4. Now modify template1 so it’s actually a template:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

5. (RECOMMENDED) PostgreSQL documentation advises to VACUUM FREEZE the template:

\c template1
VACUUM FREEZE;

6. (OPTIONAL) If you don’t want anyone connecting to this template, set datallowconn to FALSE:

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Now you can create a new database by running from regular shell:

su -
su - postgres
createdb blog;

If you log in back to psql and check the databases, you should see the proper encoding of your new database:

\l

returns

                              List of databases
  Name    |  Owner   | Encoding  | Collation | Ctype |   Access privileges
-----------+----------+-----------+-----------+-------+----------------------
blog      | postgres | UTF8      | C         | C     |
postgres  | postgres | SQL_ASCII | C         | C     |
template0 | postgres | SQL_ASCII | C         | C     | =c/postgres
                                                     : postgres=CTc/postgres
template1 | postgres | UTF8      | C         | C     |

 

 

 

PostgreSQL – ArchWiki.

 

from: http://journal.tianhao.info/2010/12/postgresql-change-default-encoding-of-new-databases-to-utf-8-optional/#comment-1060