1. 云图网首页
  2. 技术专区
  3. 数据库

How to Migrate Oracle to PostgreSQL

• 数据库

最近在做 oracle 转 PostgreSQL 项目调研，部分业务需要由 oracle 数据库迁移到 PostgreSQL 平台，这几天在做这方面的调研，暂时有几分心得。

Oracle 转 pg 显然是一个比较复杂的工程，需要考虑的事情很多，如果是比较繁忙且数据量大的系统，这个工作量和难度是很大的。不管是大库还是小库，总有些理论是相同的。

前期调研

  1. 了解业务：在项目开始前，需要和开发人员，项目经理进行充分的沟通，了解业务，了解原系统，了解数据分布；
  2. 表定义需改写: oracle 和 pg 数据类型不同，所以表定义脚本需要写；
  3. 哪些表需要迁数据？哪些表只需迁结构？
  4. 存储过程，函数需改写
  5. 是否使用 job?
  6. 数据库用户权限如何迁移？
  7. 大表如何迁移？

备注：前期需要调研的内容很多，这里只列出了想到的一部分，在真正实施过程中肯定会遇到更多的问题。

数据库方案调研

  1. 是否需要做高可用？
  2. 日志表是否需要分区？
  3. 是否需要做连接池？
  4. 根据业务特点进行个性化 postgresql 参数设置。
  5. 硬件资源需求（CPU, 内存，硬盘或存储）

数据类型对比

Oracle 和 PG 数据类型对照表，如下：
How to Migrate Oracle to PostgreSQL

数据库表迁移测试

接下来做个测试：是从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
34
 
SQL> 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
34
 
create 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
11
 
set 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 脚本

1
 
select /*+ 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;

备注：这里需要注意两点：

  1. 字段为空的用 “N” 填充，否则，在导入到 pg库中会报错；
  2. 在使用 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 初始经验总结。

参考资料

原创文章，作者：254126420，如若转载，请注明出处：https://blog.ytso.com/236437.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论