PostgreSQL Upgrade to 8.3 on Debian Encoding Issues

First check locale settings:

$ locale -a
C
en_US.utf8
POSIX

I'm assuming the locale settings match this (the default Debian install for en_US); for other locales you will need to change things as needed for your location. If upgrading from an older version of Debian using en_US these settings can be modified using dpkg-reconfigure locales and probably should be set to this as it is the current Debian default.

Once the locale is set to the settings shown above, re-init the db:

/usr/lib/postgresql/8.3/bin/initdb -D <postgres data directory>

Check the output to confirm UTF8 is being set correctly:

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

PostgreSQL has automatic client-server encoding conversion for some locales, including LATIN1 to UTF8. Here is an example of database creation in a pg_dump file which needs modification:

--
-- Database creation
--

CREATE DATABASE mydb WITH TEMPLATE = template0 OWNER = me ENCODING = 'LATIN1';
. . . all databases in dump file . . .

Because of this automatic encoding, simply change any CREATE DATABASE lines with ENCODING = 'LATIN1' to no longer specify the encoding:

CREATE DATABASE mydb WITH TEMPLATE = template0 OWNER = me;

Because the dump file includes client encoding specifications with each database, i.e. SET client_encoding = 'LATIN1'; PostgreSQL will auto-convert the data in each database into UTF8 encoding when it notices the created databases do not match the client encoding. For this reason, DO NOT just search and replace LATIN1 in your dump file and replace it with UTF8, as this will prevent proper encoding changes from occurring.