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).


18 February 2013;

Comment article