Migration Problems 7.2.3 -> 7.3.4

From: James Cooper <james(dot)cooper(at)wificom(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Migration Problems 7.2.3 -> 7.3.4
Date: 2003-10-24 10:55:40
Message-ID: 3F99052C.7000905@wificom.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Good afternoon,
I searched the archives and since I'm a relatively noivce user I'll have
to run the risk of repeating already asked questions.

I'm having a problem on two counts with migrating our database.

Firstly the existing database is encoded as SQLASCII in which the
majority of characters are from the Latin1 character set. I would like
to migrate this table space to unicode. The problem is that when I
create a database in unicode and try insert the backed up data I
regulary get arbitrary error messages such as the following:

Invalid UNICODE character sequence found (0xe9726f)

After some examination I found that the characters essentially dropped
are \347 \350 \351 \352 etc.

I'm running 7.2.3 on Redhat 7.3

The steps I've followed are the following:
1. pgdump -dO -f /sql/SabServerBak.sql
2. CREATE DATABASE harrier WITH TEMPLATE = template0 ENCODING = 'unicode';
3. psql harrier
4. \encoding latin1
5. \i /sql/SabServerBak.sql

As a stop gap, I decided to persevere with SQLASCII.
I tried dumping using:
pgdump -dO -f /sql/SabServerBak.sql
and
pgdump -dO -Fc -f /sql/SabServerBak.sql

CREATE DATABASE harrier WITH TEMPLATE = template0 ENCODING = 'sqlascii';

and restoring using:
psql harrier
\encoding latin1
\i /sql/SabServerBak.sql

and
pg_restore /sql/SabServerBap.sql -d harrier

The problem is that when I look at the records via psql, they look fine.
When I retrieve records via the JDBC interface, Hôtel for instance is
rendered as H?l. The weird thing is that when I insert something like
James Cööpér, it works fine, but is when I read it from psql it looks
like garbage.

Any clarification would be of great help,
Thanks in advance.

--
James Cooper
Software Engineer, Wificom Technologies Ltd
Uudenmaankatu 23 A Tel. +358 9 5627 4600
00120 Helsinki, Finland Fax. +358 9 5627 4601

Browse pgsql-admin by date

  From Date Subject
Next Message Per Mnster 2003-10-24 11:08:28 Re: Access with a management tool
Previous Message Per Mnster 2003-10-24 10:32:10 Access with a management tool