注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

How to Migrate Oracle to PostgreSQL  

2011-10-25 15:55:57|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

    

           最近在做 oracle 转 PostgreSQL 项目调研,部分业务需要由 oracle 数据库迁移到 PostgreSQL 平台,这几天
在做这方面的调研,暂时有几分心得。
 
          Oracle 转 pg 显然是一个比较复杂的工程,需要考虑的事情很多,如果是比较繁忙且数据量大的系统,这个工作
 量和难度是很大的。不管是大库还是小库,总有些理论是相同的。
 
 
一 前期调研
1 了解业务:在项目开始前,需要和开发人员,项目经理进行充分的沟通,了解业务,了解原系统,了解数据分布;
2 表定义需改写: oracle 和 pg 数据类型不同,所以表定义脚本需要写;
3 哪些表需要迁数据?哪些表只需迁结构?
3 存储过程,函数需改写
4 是否使用 job?
5 数据库用户权限如何迁移?
6 大表如何迁移?
 
  备注:前期需要调研的东西很多,这里只列出了想到的一部分,以后在真正实施过程中肯定会遇到更多的问题。

 

二 数据库方案调研
1 是否需要做高可用?
2 日志表是否需要分区?
3 是否需要做连接池?
4 根据业务特点进行个性化 postgresql 参数设置。
6 硬件资源需求(CPU, 内存,硬盘或存储)

 

三 oracle 和 pg 数据类型对照表

How to  Migrate  Oracle  to  Postgres - francs - My DBA LIFE

 

数据库迁移测试

       接下来做个测试:是从oracle 库到 pg 库的数据迁移, 仅以迁移一张表为例。目标将 oracle 库中的表
sup.TBL_SUP_WEATHER_UPDATE_LOG 迁移到 pg 库。这里 pg 库已创建。

   
--4.1 oracle 库中的表结构   
 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
   
   
--4.2 在pg库中建表              
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 库中。
 
 
--4.3 TBL_SUP_WEATHER_UPDATE_LOG.sql 脚本
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 出来的文本仅包含
              数据,脚本做了格式化;
 
 
--4.4 SELECT_TBL_SUP_WEATHER_UPDATE_LOG.sql
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。                   
 
 
--4.5 执行脚本 TBL_SUP_WEATHER_UPDATE_LOG.sql 取数据
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 主机上。


--4.6 文件列表
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


--4.7 将数据导入到 PG库
[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 1479485
skytf_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库中。
 

--4.8 难点 
    1 如何将大表从 oracle 库中迁移到 PostgreSQL?
   
       可以利用第三方工具,如 "ESF Database Migration Toolkit ", 也可以使用本文的方法,
       或许还有其它方法,但是我们需要最快的方法,尽可能的减少迁移时间。
   
        备注:本文只是 Oracle 转 PostgreSQL 初始经验总结。

 

--4.9 参考资料
http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion
http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion

  评论这张
 
阅读(27879)| 评论(3)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016