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!