Change PostgreSQL database encoding

First login to the PostgreSQL shell.

(env)[email protected]:/opt/canadalando_env/canadalando_django$ sudo -u postgres psql

Check the list of databases.

postgres=# \l

Here I could see my database had the wrong encoding, instead of SQL_ASCII I want UTF8. I dropped the database so I can re-create it with the right encoding. Note that I did NOT make a back-up, since my database was still empty.

postgres=# DROP DATABASE website_db;

In order to use UTF8 the template for the databases needs to be updated first.

Disable the template1.

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

Drop the database.

postgres=# DROP DATABASE template1;

Now re-create it with the right encoding.

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

Activate the template.

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

Now we can re-create the database that we dropped earlier.

postgres=# CREATE DATABASE website_db WITH ENCODING 'UNICODE';