视频库的数据从ORACLE迁移至PG

[lisiliang]

数据迁移 ，采用开源软件ora2pg 实施迁移

期间遭遇了 字符编码问题，

经过联系软件开发者

已经成功的解决了这个问题，

数据转换完成。

[lisiliang]

Hi,

Assuming that you have initialized your PostgreSQL cluster with UTF8 (initdb -E UTF8) you may set the following configuration directives into ora2pg.conf:

NLS_LANG CHINESE_CHINA.ZHS16GBK
CLIENT ENCODING GBK

If you already have initialized your cluster with GBK encoding, you can comment the CLIENT_ENCODING configuration directive. If your cluster is not UTF8 by default, you can create your database using :

createdb -E UTF8 sns_pclady

Let me know.

Regards,
--
Gilles

Le 26/12/2011 02:08, lsliang a écrit :

hi , gilles


I ran the commande and it returns :


[oracle@test740_5 games]$ ora2pg -t SHOW_ENCODING
Trying to connect to database: dbi:Oracle:host=192.168.74.5;sid=test745;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Force Oracle to compile schema before code extraction
Showing Oracle encoding...
NLS_LANG SIMPLIFIED CHINESE_CHINA.ZHS16GBK
NLS_LANG2 SIMPLIFIED CHINESE_CHINA.ZHS16GBK
CLIENT ENCODING GBK
[oracle@test740_5 games]$


and pg database server's ENV

sns_pclady=> \set
AUTOCOMMIT = 'on'
PROMPT1 = '%/%R%# '
PROMPT2 = '%/%R%# '
PROMPT3 = '>> '
VERBOSITY = 'default'
VERSION = 'PostgreSQL 9.1.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit'
DBNAME = 'sns_pclady'
USER = 'sns_pclady'
PORT = '5432'
ENCODING = 'GBK'
LASTOID = '0'
sns_pclady=> \encoding
GBK

and when i set the client_encoding = GBK , it show the same error.

and the bash shell's ENV

[postgres@test740-10 ora2pg]$ locale
LANG=zh_CN.gbk
LC_CTYPE="zh_CN.gbk"
LC_NUMERIC="zh_CN.gbk"
LC_TIME="zh_CN.gbk"
LC_COLLATE="zh_CN.gbk"
LC_MONETARY="zh_CN.gbk"
LC_MESSAGES="zh_CN.gbk"
LC_PAPER="zh_CN.gbk"
LC_NAME="zh_CN.gbk"
LC_ADDRESS="zh_CN.gbk"
LC_TELEPHONE="zh_CN.gbk"
LC_MEASUREMENT="zh_CN.gbk"
LC_IDENTIFICATION="zh_CN.gbk"
LC_ALL=



regards

---

发件人： Gilles Darold
发送时间： 2011-12-24 05:28:29
收件人： lsliang
抄送：
主题： Re: ora2pg can not convert gbk to UTF8
Hello,

Thanks a lot for your interest in this project. You need to find the correct encoding to fix your issue. Could you please send me the value given to the NLS_LANG and CLIENT_ENCODING configuration directive into your ora2pg.conf. What could also help me is the result of the command:

ora2pg -t SHOW_ENCODING

With these informations we should be able to find the good encoding values for you data migration.

Regards,
--
Gilles

Le 23/12/2011 04:46, lsliang a écrit :
GILLES,


when i use ora2pg 8.9 to transfer data from oracle to pg 9.1


i get some problems:


like :

when run this sql in psql got an error:


sns_pclady=> \i lsl.sql
psql:lsl.sql:1: ERROR: character 0xfe52 of encoding "GBK" has no equivalent in "UTF8"
sns_pclady=>

because some character is in GBK but not in UTF8 like ，

i think whether can modify the ora2pg.pm ,

when some character in GBK and not in UTF8 then covert them as '?' or some others ?


thank you!