使用data X工具从Oracle到PG迁移表数据

参考文档

https://github.com/alibaba/DataX
https://github.com/alibaba/DataX/blob/master/userGuid.md
https://github.com/alibaba/DataX/blob/master/oraclereader/doc/oraclereader.md
https://github.com/alibaba/DataX/blob/master/postgresqlwriter/doc/postgresqlwriter.md

安装部署

• 1）系统先决条件

Linux
JDK(1.8以上，推荐1.8)
Python(推荐Python2.6.X)

• 查看当前系统环境信息，全部符合上述条件

[root@localhost ~]# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.5 (Santiago)

[root@localhost ~]# java -version
java version "1.8.0_231"
Java(TM) SE Runtime Environment (build 1.8.0_231-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.231-b11, mixed mode)

[root@localhost ~]# python -V
Python 2.6.6

• 2）下载后解压至本地某个目录，进入bin目录，即可运行同步作业
  http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz

[zxj@localhost ~]$ pwd
/home/zxj
[zxj@localhost ~]$ tar -xf datax.tar.gz 
[zxj@localhost ~]$ ll
total 809944
drwxr-xr-x 9 zxj  zxj       4096 Oct 12  2019 datax
-rw-r--r-- 1 root root 829372407 Jul 29 11:12 datax.tar.gz

Oracle 迁移到PG

• 1）环境信息

Oracle：192.168.56.77:1521/orcl
PG：192.168.56.77:10011/testdb

• 2）PG端表结构已创建

testdb=> \dt
           List of relations
 Schema  |   Name    | Type  |  Owner  
---------+-----------+-------+---------
 migrate | classinfo | table | migrate
 migrate | t1        | table | migrate
(2 rows)

• 3）使用脚本批量配置json文件

[zxj@localhost ~]$ bash table_datax.sh
[zxj@localhost ~]$ cd json
[zxj@localhost json]$ ll
total 8
-rw-rw-r-- 1 zxj zxj 1792 Jul 29 16:51 classinfo.json
-rw-rw-r-- 1 zxj zxj 1771 Jul 29 16:51 t1.json

• 脚本内容如下

#!/bin/bash

#set variables
json_dict=/home/zxj/json
ora_user=system
ora_passwd=XXXXXX
ora_schema=u1
ora_conn=jdbc:oracle:thin:@192.168.56.77:1521/orcl
pg_user=migrate
pg_passwd=xxxxxx
pg_schema=migrate
pg_conn=jdbc:postgresql://192.168.56.77:10011/testdb
table_list=(t1 classinfo)

for i in ${table_list[@]}
do
cat >> ${json_dict}/$i.json << EOF
{
    "job": {
        "setting": {
            "speed": {
                "channel": 1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "oraclereader",
                    "parameter": {
                        "username": "${ora_user}",
                        "password": "${ora_passwd}",
                        "column": [
                            "*"
                        ],
                        "connection": [
                            {
                                "table": [
                                    "${ora_schema}.$i"
                                ],
                                "jdbcUrl": [
                                    "${ora_conn}"
                                ]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "postgresqlwriter",
                    "parameter": {
                        "username": "${pg_user}",
                        "password": "${pg_passwd}",
                        "column": [
                            "*"
                        ],
                        "preSql": [
                            "truncate table ${pg_schema}.$i"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "${pg_conn}",
                                "table": [
                                    "${pg_schema}.$i"
                                ]
                            }
                        ],
                        "batchSize": 512
                    }
                }
            }
        ]
    }
}
EOF
done

• 4）启动DataX，迁移数据

[zxj@localhost ~]$ /home/zxj/datax/bin/datax.py /home/zxj/json/classinfo.json > /home/zxj/log/classinfo.log 2>&1

• 迁移完成，日志结果显示如下：

2022-07-29 16:57:04.492 [job-0] INFO  JobContainer - 
任务启动时刻                    : 2021-07-29 16:56:53
任务结束时刻                    : 2021-07-29 16:57:04
任务总计耗时                    :                 10s
任务平均流量                    :                9B/s
记录写入速度                    :              1rec/s
读出记录总数                    :                  12
读写失败总数                    :                   0

• 5）PG端查看表数据

testdb=> \c
You are now connected to database "testdb" as user "migrate".
testdb=> select * from classinfo;
 classid | classname 
---------+-----------
       1 | math
       2 | english
       3 | chinese
       4 | history
       5 | physics
       6 | chemistry
(6 rows)