利用FDW进行ORACLE到Postgresql的数据迁移

随着开源数据库技术的发展和去“O”工作的推进,越来越多企业生产系统选择使用Postgresql数据库。Pgsql采用多进程结构,其存储过程、函数的支持好于mysql。个人认为pgsql是oracle的最佳替代产品。但是pgsql作为一款开源数据库,其计算能力和稳定性还是无法与Oracle相提并论,所有当企业要使用PG替代oracle时,还是得从数据库的拆分和架构上下功夫。使用PG去“O”的核心步骤之一是数据迁移,迁移方案有多种,如ETL、ORACLE_FDW等,本文将重点介绍ORACLE_FDW的迁移过程。FDW是PG的一个开源插件,可以通过在github上下载编译安装,以实现类似ORACLEDBLINK的功能。FDW优点是配置简单,使用方便,缺点是不能实现增量迁移,所以只适合数据量较小或者停机时间较长的情况。下面开始本次分享。




插  件  安  装


  1. PG软件安装,一般选用源码进行编译安装,此处不对安装过程进行详细描述(相信大家都是老司机哈)。


  2. 下载精简版Oracle客户端instantclient-*.zip,包含basic、sdk和sqlplus三个文件并解压。


  3. 配置好用户postgres的环境变量。

LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/home/postgres/instantclient_11_2:/usr/local/pgsql/lib

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/pgsql/bin

ORACLE_HOME=/home/postgres/instantclient_11_2

PGDATA=/data

export PATH ORACLE_HOME LD_LIBRARY_PATH PGDATA


  1. 插件编译安装

$ make

$ make install


  1. 验证是否安装成功

利用FDW进行ORACLE到Postgresql的数据迁移




ORACLE_FDW配置和使用

  1. 创建extension

create extension oracle_fdw;

postgres=# \dx

                        List of installed extensions

    Name    | Version |   Schema   |              Description

------------+---------+------------+----------------------------------------

 oracle_fdw | 1.1     | public     | foreign data wrapper for Oracle access  -->说明创建成功

 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language  


  1. 创建server,使pg通过创建外部表连接oracle数据库

CREATE SERVER spclora FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '10.26.5*.**:1521/spcltbk');

--创建server spclora 连接到oracle数据库

GRANT USAGE ON FOREIGN SERVER spclora TO userinfo_prod;

--将server授权给用户userinfo_prod

\c userinfodb userinfo_prod

--使用userinfo_prod切换到userinfodb

CREATE USER MAPPING FOR userinfo_prod SERVER spclora OPTIONS (user 'pgsync', password 'oracle');

--创建到oracle的映射,pgsync/oracle是oracle数据库的用户名和密码


  1. 创建外部表

create FOREIGN table t_***_userinfo_fdw

(

  phone***   VARCHAR(24) not null,

  ser***tus    int4 not null,

  createtime    timestamp with time zone default now() not null,

  ups***time  timestamp with time zone default now() not null,

  op**a*or      int4 not null,

  che**me     timestamp with time zone,

  lo**id       int4,

  modu**code    VARCHAR(20),

  modi***time  timestamp with time zone default now() not null

)SERVER spclora OPTIONS (schema 'SP***DP', table 'T_USERINFO_MV',prefetch '10240');


至此,通过访问外部表,即可访问ORACLE数据库对应表,上例建表语句中的options表示oracle的表信息SP***DP.T_USERINFO_MV。pretetch,表示从oracle预取的行数,默认是200,建议配置到最大10240,根据我们的实战经验,配置该参数后,数据迁移的速度至少提高50%。




数  据  迁  移


在PG侧创建表,然后使用insertinto pg_table select * from foreign_table即可实现数据迁移。



迁移案例


某运营商项目一重要系统原数据库使用Oracle11.2.0.4,架构为HA架构,数据量约1TB,进行去“O”改造。考虑到去O后的效率以及数据增长等问题,架构上做了数据“对症下药”。数据库层面进行了拆分,日志数据存放到ES上,分发数据存放到Mongodb上,核心交易数据存放到PG中,日志数据无需迁移,分发数据可以在业务重建之后,从其他业务系统请求重新分发,也无需迁移,只需要迁移核心交易数据到PG中,需迁移的数据量大大减少。为提升PG数据库效率,PG架构使用一主两从加pgpool的读写分离架构。


根据业务的特点以及确保迁移影响降到最低,迁移方案采用数据按省份进行割接的轮动方式。每个省份的数据迁移,只有15分钟的停机时间。停机时间短,我们考虑过使用OGG进行增量迁移,使用过OGG的同学都是,这玩意就像有钱人家的大小姐,性子琢磨不透,纯“根据心情”,经常出现莫名其妙的岔子。再加上异构环境下,估计使用OGG,岔子更多,所以我们选择ORACLE_FDW作为迁移方案。


为了在规定的时间内完成数据迁移,我们采取了以下手段:

  1. 由于在原表中以省份ID区分各省数据,列数据选择性较低,以省份过滤查询时,部分省份数据无法使用索引,全表扫描会导致迁移的时间延长;而且本次迁移,业务上也进行了更改,所以迁移数据时只需要原表的部分列。鉴于此情况,我们在源端使用了物化视图对原表数据进行裁剪,PG外部表与物化视图进行对应,这样迁移时可以减少无效的读取IO,缩短迁移时间。

  2. 修改外部表的prefetch参数为10240。

  3. 将多个表数据迁移编写脚本实现手工并行。

按照本篇介绍及手段方法,即可完成数据量1T左右的迁移工作,希望对你的工作有所启示和帮助,我们下回见。


上一篇:Java IO学习笔记二:DirectByteBuffer与HeapByteBuffer


下一篇:彻底理解ReentrantLock