Re: Upgrading from 7.1.2 to 7.2.1 on new server

On Sun, 2002-04-21 at 14:45, Jean Huveneers wrote:
> I've been using postgres 7.1.3 in production with a couple of unicode
> databases.
> Last week we bought a new server, i've installed RedHat Linux 7.2 on it
> with postgres SQL 7.2.1.
>
> Now, went i to move the data ('pg_dumpall > data_dump' on the old
> server, psql template1 < data_dump data on the new server) i get an
> error. psql continous but, same tables on the new server are not filled.
> the error has something to do with 'illage unicode character sequence'.
>
> Can somebody please give me some advise on how i fix these errors? or is
> there another way to upgrade than via pg_dump?

The problem is that 7.1.3 and earlier did not check for valid unicode
characters.  Somehow, single characters with the eighth bit set were
allowed into the data; these look like unicode leadins, and are rejected
because they seem to be unicode characters above 0x10000.

The solution is to set the client encoding to SQL-ASCII or maybe LATIN9
while reading them in; thus they get converted to unicode (UTF_8)
between frontend and backend.  I am not sure, however, whether this
adequately protects real UTF-8 characters that may be mingled in the
data.  I suppose the alternative is to run a Perl script to check for
high-bit characters that are not valid UTF-8 for characters below
0x10000.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "The sacrifices of God are a broken spirit; a broken
      and a contrite heart, O God, thou wilt not despise."
                Psalms 51:17