最近在做 oracle 转 PostgreSQL 项目调研,部分业务需要由 oracle 数据库迁移到 PostgreSQL 平台,这几天在做这方面的调研,暂时有几分心得。
Oracle 转 pg 显然是一个比较复杂的工程,需要考虑的事情很多,如果是比较繁忙且数据量大的系统,这个工作量和难度是很大的。不管是大库还是小库,总有些理论是相同的。
前期调研了解业务:在项目开始前,需要和开发人员,项目经理进行充分的沟通,了解业务,了解原系统,了解数据分布;
表定义需改写: oracle 和 pg 数据类型不同,所以表定义脚本需要写;
哪些表需要迁数据?哪些表只需迁结构?
存储过程,函数需改写
是否使用 job?
数据库用户权限如何迁移?
大表如何迁移?
备注:前期需要调研的内容很多,这里只列出了想到的一部分,在真正实施过程中肯定会遇到更多的问题。
数据库方案调研是否需要做高可用?
日志表是否需要分区?
是否需要做连接池?
根据业务特点进行个性化 postgresql 参数设置。
硬件资源需求(CPU, 内存,硬盘或存储)
数据类型对比
Oracle 和 PG 数据类型对照表,如下:
数据库表迁移测试
接下来做个测试:是从oracle 库到 pg 库的数据迁移, 仅以迁移一张表为例。目标将 oracle 库中的表 sup.TBL_SUP_WEATHER_UPDATE_LOG 迁移到 pg 库。这里 pg 库已创建。
oracle 库中的表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34SQL> desc sup.TBL_SUP_WEATHER_UPDATE_LOG;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(11)
APP_ID NOT NULL NUMBER(10)
APP_VER NUMBER(10)
SHORT_NAME VARCHAR2(255)
HSMAN VARCHAR2(100)
HSTYPE VARCHAR2(100)
IMEI VARCHAR2(100)
IMSI VARCHAR2(100)
PLAT VARCHAR2(30)
REQ_TYPE NUMBER(1)
FEE_TYPE NUMBER(1)
WIDTH NUMBER(4)
HEIGHT NUMBER(4)
PROVIDER NUMBER(1)
MSG_CEN VARCHAR2(20)
PORTV NUMBER(10)
VMV NUMBER(10)
IP_ADDR VARCHAR2(15)
LOCAL_ADDRESS VARCHAR2(15)
LOCAL_PORT NUMBER(5)
RESPONSE_CODE NUMBER(3)
FILE_LEN NUMBER(10)
REQ_MD5 VARCHAR2(32)
RESP_MD5 VARCHAR2(32)
REQ_START_POS NUMBER(10)
RESP_START_POS NUMBER(10)
CHECK_INTERVAL NUMBER(10)
CHECK_AFTER_TIMES NUMBER(5)
RES_POLICY NUMBER(1)
RES_MD5 VARCHAR2(32)
CREATE_TIME DATE
在pg库中建表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34create table TBL_SUP_WEATHER_UPDATE_LOG
(
ID integer NOT NULL,
APP_ID integer NOT NULL ,
APP_VER integer,
SHORT_NAME VARCHAR(255),
HSMAN VARCHAR(100),
HSTYPE VARCHAR(100),
IMEI VARCHAR(100),
IMSI VARCHAR(100),
PLAT VARCHAR(30) ,
REQ_TYPE integer,
FEE_TYPE integer,
WIDTH integer,
HEIGHT integer,
PROVIDER integer,
MSG_CEN VARCHAR(20),
PORTV integer,
VMV integer,
IP_ADDR VARCHAR(15),
LOCAL_ADDRESS VARCHAR(15),
LOCAL_PORT integer,
RESPONSE_CODE integer,
FILE_LEN integer,
REQ_MD5 VARCHAR(32),
RESP_MD5 VARCHAR(32),
REQ_START_POS integer,
RESP_START_POS integer,
CHECK_INTERVAL integer,
CHECK_AFTER_TIMES integer,
RES_POLICY integer,
RES_MD5 VARCHAR(32),
CREATE_TIME timestamp without time zone
);
备注:根据 oracle 和 pg 数据类型对应表,修改表定义中的数据类型即可得到相应的 pg 建表脚本。接下来几个脚本是用来取 oracle 数据的,并将数据导入到 pg 库中。
TBL_SUP_WEATHER_UPDATE_LOG.sql 脚本
1
2
3
4
5
6
7
8
9
10
11set termout off
set feedback off
set verify off
set echo off
set pagesize 0
set trims on
set linesize 1000
spool "TBL_SUP_WEATHER_UPDATE_LOG.log"
@ "SELECT_TBL_SUP_WEATHER_UPDATE_LOG.sql"
spool off
exit
备注:这个脚本功能是将 oracle 数据导入到文本文件,为了使得 spool 出来的文本仅包含数据,脚本做了格式化;
SELECT_TBL_SUP_WEATHER_UPDATE_LOG.sql 脚本
1select /*+ FULL(A) PARALLEL(A 8) */ nvl(to_char(ID ), 'N') || chr(9)||nvl(to_char(APP_ID ), 'N') || chr(9)|| nvl(to_char(APP_VER ), 'N') || chr(9)|| nvl(to_char(SHORT_NAME ), 'N') || chr(9)|| nvl(to_char(HSMAN ), 'N') || chr(9)|| nvl(to_char(HSTYPE ), 'N') || chr(9)|| nvl(to_char(IMEI ), 'N') || chr(9)|| nvl(to_char(IMSI ), 'N') || chr(9)|| nvl(to_char(PLAT ), 'N') || chr(9)|| nvl(to_char(REQ_TYPE ), 'N') || chr(9)|| nvl(to_char(FEE_TYPE ), 'N') || chr(9)|| nvl(to_char(WIDTH ), 'N') || chr(9)|| nvl(to_char(HEIGHT ), 'N') || chr(9)|| nvl(to_char(PROVIDER ), 'N') || chr(9)|| nvl(to_char(MSG_CEN ), 'N') || chr(9)|| nvl(to_char(PORTV ), 'N') || chr(9)|| nvl(to_char(VMV ), 'N') || chr(9)|| nvl(to_char(IP_ADDR ), 'N') || chr(9)|| nvl(to_char(LOCAL_ADDRESS ), 'N') || chr(9)|| nvl(to_char(LOCAL_PORT ), 'N') || chr(9)|| nvl(to_char(RESPONSE_CODE ), 'N') || chr(9)|| nvl(to_char(FILE_LEN ), 'N') || chr(9)|| nvl(to_char(REQ_MD5 ), 'N') || chr(9)|| nvl(to_char(RESP_MD5 ), 'N') || chr(9)|| nvl(to_char(REQ_START_POS ), 'N') || chr(9)|| nvl(to_char(RESP_START_POS ), 'N') || chr(9)|| nvl(to_char(CHECK_INTERVAL ), 'N') || chr(9)|| nvl(to_char(CHECK_AFTER_TIMES), 'N') || chr(9)|| nvl(to_char(RES_POLICY ), 'N') || chr(9)|| nvl(to_char(RES_MD5 ), 'N') || chr(9)|| nvl(to_char(CREATE_TIME ), 'N') from sup.TBL_SUP_WEATHER_UPDATE_LOG A;
备注:这里需要注意两点:
字段为空的用 “N” 填充,否则,在导入到 pg库中会报错;
在使用 nvl 参数时,date, NUMBER 字段需要转换成字符类型,为了方便,在使用 nvl 函数时, 建议所有字段都先转换成字符类型。
为了将 oracle 数据导出到 pg 能读取的格式,花了一番功夫才整出以上取数据 SQL。
执行脚本 TBL_SUP_WEATHER_UPDATE_LOG.sql 取数据
1
2
3
4
5
6
7
8
9[oracle@db_sup](mailto:oracle@db_sup)-> sqlplus " /as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 25 10:43:36 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>@"TBL_SUP_WEATHER_UPDATE_LOG.sql"Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
备注:这里产生数据文件 TBL_SUP_WEATHER_UPDATE_LOG.log , 大小为 340M, 接下来将这个数据文件传到 pg 主机上。
文件列表
1
2
3
4
5[oracle@db_sup](mailto:oracle@db_sup)-> ll
total 340M
-rw-r--r-- 1 oracle oinstall 1.6K Oct 25 10:43 SELECT_TBL_SUP_WEATHER_UPDATE_LOG.sql
-rw-r--r-- 1 oracle oinstall 340M Oct 25 10:47 TBL_SUP_WEATHER_UPDATE_LOG.log
-rw-r--r-- 1 oracle oinstall 207 Oct 25 10:26 TBL_SUP_WEATHER_UPDATE_LOG.sql
将数据导入到 PG库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28[postgres@db]$ psql -h 127.0.0.1 skytf_bak postgres
psql (9.0.1)
Type "help" for help.
skytf_bak=# select count(*) from skytf.tbl_sup_weather_update_log;
count
-------
0
(1 row)
skytf_bak=# copy skytf.tbl_sup_weather_update_log from '/home/postgres/script/tf/TBL_SUP_WEATHER_UPDATE_LOG.log';
COPY 1479485skytf_bak=# select count(*) from skytf.tbl_sup_weather_update_log;
count
---------
1479485
(1 row)
skytf_bak=# select * From skytf.tbl_sup_weather_update_log limit 1;
id |app_id|app_ver|short_name|hsman|hstype|imei|imsi|plat|req_type|fee_t
ype|width|height|provider|msg_cen|portv|vmv|ip_addr|local_address|local_port|response_code|
file_len|req_md5|resp_md5|req_start_pos|resp_start_pos|check_interval|check_after_times|res_policy|res_
md5|create_time
-------+--------+---------+------------+----------+----------+----------------------+----------------------+------+----------+------
-----------------+---------------------
86712|2914|9000|tianqi|b~1&qqM=|zdzFow==|(Bzzb^zyL5zyftyy%fzy|)dzyLlyy$tzy$dyy~fzy|MTK|0|
2|240|320||8613800775500|101081180|1964|211.138.250.103|192.168.171.39|6011|404|
|||0|0|10|10|1|683def7566d4099f
63589514dda23d9d|2011-09-11 00:52:26
备注:到了这步,表 tbl_sup_weather_update_log 已成功从 oracle 库迁移到 pg库中。
难点
如何将大表从 oracle 库中迁移到 PostgreSQL?
可以利用第三方工具,如 “ESF Database Migration Toolkit “, 也可以使用本文的方法,或许还有其它方法,但是我们需要最快的方法,尽可能的减少迁移时间。
备注:本文只是 Oracle 转 PostgreSQL 初始经验总结。
参考资料