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

IT那活儿发布于2023-01-11 13:20 / 3360人阅读

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

插件安装

PG软件安装，一般选用源码进行编译安装，此处不对安装过程进行详细描述（相信大家都是老司机哈）。
下载精简版Oracle客户端instantclient-*.zip，包含basic、sdk和sqlplus三个文件并解压。
配置好用户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

插件编译安装

$ make

$ make install

验证是否安装成功

ORACLE_FDW配置和使用

创建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

创建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数据库的用户名和密码

创建外部表

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作为迁移方案。

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

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

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

云服务器 GPU云服务器 oracle数据库迁移到mysql oracle数据迁移到db2 PPAS oracle数据迁移到db2 oracle fdw

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130206.html

新书推荐 |《PostgreSQL实战》出版(提供样章下载)

摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

Martin91 2019-07-25 12:36 评论0 收藏0
PostgreSQL对接SequoiaDB

摘要：是一款开源的数据库，支持标准，用户可以通过驱动连接进行应用程序开发。本文就针对如何扩展功能，实现对接进行介绍。直接在中修改配置文件，只能在当前中生效，重新登录需要重新设置。 PostgreSQL是一款开源的SQL数据库，支持标准SQL，用户可以通过JDBC驱动连接PostgreSQL进行应用程序开发。用户通过扩展PostgreSQL功能，让开发者可以使用SQL语句访问SequoiaDB...

TZLLOG 2019-07-25 12:30 评论0 收藏0
阿里云如何打破Oracle迁移上云的壁垒

摘要：摘要第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破迁移上云的壁垒为题的演讲。于是，阿里云给出了上面的解决方案。摘要： 2018第九届中国数据库技术大会，阿里云数据库产品专家萧少聪带来以阿里云如何打破Oracle迁移上云的壁垒为题的演讲。Oracle是指数据库管理系统，面对Oracle迁移上云的壁垒，阿里云如何能够打破它呢？本文提出了Oracle 到云数据库P...

chavesgu 2019-06-25 18:39 评论0 收藏0