View source for Installation Experiences/Jan 2011: Ubuntu 10.04 UTF8 create db errors (fixed)

I also had problems with UTF8 on Ubuntu with Postgres 8.4. I'm running Ubuntu 10.04 (server) on a UK-based VPS. I was migrating an existing installation from one server to another, upgrading my version of DAViCal in the process. On the old server I backed up the database:

 pg_dump -Fc davical >davical.pgdump

The dump file was copied to the new server.

 DAViCal was installed using aptitude.

I was getting errors when trying to create the database:

 $ /usr/share/davical/dba/create-database.sh
 createdb: database creation failed: ERROR:  encoding UTF8 does not match locale en_GB
 DETAIL:  The chosen LC_CTYPE setting requires encoding LATIN1.
 Unable to create database

To fix this, I removed the postgresql package (not sure of this was strictly required, but anyway...)
I then added the missing locale to my system:

 sudo locale-gen en_GB.UTF-8

US users can (probably) do this:

 sudo locale-gen en_US.UTF-8

and I expect it is similar for other locales. I read online that you should add the new locale to /etc/locale.gen before running locale-gen but on my server this made no difference so I expect that is for a different Linux distribution. locale-gen updates /var/lib/locales/supported.d/local (no need for you to change it but a useful check that it worked):

After re-installing Postgres I tried the create-database.sh script again:

 createdb: database creation failed: ERROR:  encoding UTF8 does not match locale en_GB
 DETAIL:  The chosen LC_CTYPE setting requires encoding LATIN1.
 Unable to create database

So I tried to create the database manually being explicit with the encoding and locale:

 su postgres -c "createdb --owner davical_dba --encoding UTF8 --lc-ctype en_GB.UTF8 --lc-collate en_GB.UTF8 --template template0 davical"

This worked. I could then re-import my data:

 pg_restore -Fc -d davical davical.pgdump

And finally run the database upgrade script. It worked!