Hardware, programming and astronomy tutorials and reviews.

Workaround for PostgreSQL SQL_ASCII template encoding

Fixing PostgreSQL encoding without dropping any databases.

On some Linux distribution installs there may be a problem with SQL_ASCII encoding set on postgres templates (I found it on the daily ISO Raring Ringtail install). When trying to create postgis template with UTF-8 encoding it will fail with an error:

createdb: database creation failed: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
In Django documentation there is a link to one of workarounds - recreating the whole cluster, which results in dropping all databases. There is however an less aggressive fix for this problem - drop template1 and recreate it from template0 using UTF-8 encoding and dropping template_postgis if it exists with the wrong encoding. When template1 is recreated the "create_template_postgis*.sh" script can be used.

To drop and create "template1" use the tutorial at journal.tianhao.info. If "template_postgis" exists drop it basing on the step 1 and 2 from the tutorial. After that you can create the postgis template with the Django bash script file (or manually using for example archlinux tutorial).


Django web framework tutorials, 18 February 2013,

Comment article