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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings