Re: problem converting database to UTF-8

On 2009-01-22, David Goodenough <david(dot)goodenough(at)btconnect(dot)com> wrote:
> I have a database which was created as LATIN1 (the machine has the
> wrong locales installed when I set up PG). It is running 8.3.
>
> So I found various places which said the way to do this was to do
> a pg_dumpall -f dump_file, get rid of the entire database, init_db -E UTF-8,
> and then psql -f dumpfile.
>
> But the psql fails saying:-
>
> psql:dumpfile:49: ERROR: encoding LATIN1 does not match server's locale
> en_GB.UTF-8
> DETAIL: The server's LC_CTYPE setting requires encoding UTF8.
>
> I have en_GB.UTF-8 now as my primary locale, and en_GB.ISO8859-1 is
> also generated.
>
> So I looked around again and found people saying I needed to use iconv,
> but that does not help, I get the same error.

> Is there a definative HOWTO that I can follow, if not does someone
> have a set of instructions that will work?

simplest best way is to dump the individual databases and then restore
them individually.

else you need to go theough the dump file and doctor the
CREATE DATABASE lines to use UTF-8 (or not specify an encoding)
whilst making sure that CLIENT_ENCODING remains set to LATIN1
(so that the data from the dump file remains intelligible)

alternately you may be able to use iconv to convert the whole file to
UTF8 and also all ocurrances of LATIN1 in commands with UTF8

The frst thing I'd do is look in the dump file and see what's on line
49.