PG TO Oracle 增量同步-外部表

17 12月

作者:admin|分类:DBA运维

背景

　　最近在负责公司数据Oracle转PG；老平台数据库：Oracle11g；新平台数据库：PostgreSQL12。由于平台统计规则有变动；所以正在推广的游戏数据无法全部迁移过来；只能在老平台上运行。而支付数据接口升级；统一进入新平台数据PG。需要将部分支付数据由PostgreSQL同步到Oracle。

　　简而言之：PostgreSQL增量同步表到Oracle。首先声明我不是反“去IOE”潮流。我想到两种方案

采用OGG 可以参考 OGG For Oracle To PostgreSQL
采用外部表+脚本/存储过程

一、安装Oracle客户端

1、下载地址
http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html2、 安装步骤2.1 创建安装目录
[root@Postgres201 ~]# mkdir -p /u01/app/[root@Postgres201 ~]# cd /u01/app/2.2 上传软件包并解压
[root@Postgres201 app]# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip [root@Postgres201 app]# unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip [root@Postgres201 app]# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip [root@Postgres201 app]# mv instantclient_11_2/ oracle[root@Postgres201 app]# cd oracle2.3 配置网络文件
[root@Postgres201 app]# cd oracle[root@Postgres201 oracle]# mkdir -p network/admin[root@Postgres201 oracle]# cd network/admin/[root@Postgres201 admin]# vi tnsnames.oraora221 =
  (DESCRIPTION =(ADDRESS_LIST =  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.221)(PORT = 1521))
    )
    (CONNECT_DATA =  (SERVICE_NAME = orcl)
    )
  )2.4 配置用户环境变量
export  ORACLE_HOME=/u01/app/oracle
export  TNS_ADMIN=$ORACLE_HOME/network/admin
export  LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATHexport  PATH=$ORACLE_HOME:$PATH

二、安装oracle_fdw

　　从PostgreSQL9.3开始；Oracle_fdw支持在外部表执行 INSERT, UPDATE 和 DELETE等操作；正好符合我们PostgresQL TO Oracle的方案

1//pgxn.org/dist/oracle_fdw/
2@Postgres201 opt][root@Postgres201 opt][root@Postgres201 oracle_fdw-2.0.0][root@Postgres201 oracle_fdw-2.0.0][root@Postgres201 oracle_fdw-2.0.0][root@Postgres201 oracle_fdw-2.0.0]FAQ：执行make若出现“/usr/bin/ld: cannot find -12@Postgres201 oracle][root@Postgres201 oracle_fdw-2.0.0][root@Postgres201 oracle_fdw-2.0.0]3=ERROR:  could not load library : libclntsh.so.11.11、创建文件/etc/ld.so.conf.d/2、添加内容；libclntsh.so.11/app/34=

三、创建外部表

CREATE FOREIGN TABLE fdw_game_pay_log (
ID int8 OPTIONS(key 'true'),
PAY_TIME timestamp NOT NULL,
APPID int2 NOT NULL,
QN varchar(20) NOT NULL,
ACCOUNTID varchar(64) NOT NULL,
AMOUNT decimal(6,2),
ORDER_NO text NOT NULL,
CP_ORDER_NO text) server oradb OPTIONS (schema 'ADSAS', table 'TBL_GAME_PAY_LOG');

注意的是：

其中schemaname，tablename；需要大写
需要指定在postgres9.3版本以后oracle_fdw支持对外部表的 Insert ,delete ,update ;增加表操作项 options(key 'true') (当值设置为 true|on|yes 表示不可以做增删改操作)

四、限制

1、不支持直接 insert ... select

adsas=> insert into fdw_game_pay_log select * from tbl_game_pay_log;
ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-08177: can't serialize access for this transaction

这是远程oracle默认的事务隔离级别是repeatable read；不支持“can't serialize access for this transaction”

解决方法：将语句添加到事务中

adsas=> begin;BEGINadsas=> insert into fdw_game_pay_log select * from tbl_game_pay_log;INSERT 0 1adsas=> end;COMMIT

五、同步脚本

CREATE OR REPLACE PROCEDURE "easou"."P_SYNC_TABLE_DATA" ()AS $BODY$/***********************************************************
     将表tbl_game_pay_log增量复制到fdw_game_pay_log；
     达到PG-Oracle数据增量同步
     ***********************************************************/DECLAREvn_old_id int8;
    vn_new_id int8;BEGINBGEIN-- 获取上次提取的idSELECTlid INTO vn_old_idFROMeasou.tbl_sync_recordWHEREtbl_name = 'tbl_game_pay_log';-- 截取本次提取的最大idSELECTCOALESCE(max(id), 0) INTO vn_new_idFROMeasou.tbl_game_pay_log;-- 将本次提取的记录插入外部表INSERT INTO easou.fdw_game_pay_log (id, PAY_TIME, APPID, QN, ACCOUNTID, AMOUNT, ORDER_NO, CP_ORDER_NO)SELECTid,
        PAY_TIME,
        APPID,
        QN,
        ACCOUNTID,
        AMOUNT,
        ORDER_NO,
        CP_ORDER_NOFROMeasou.tbl_game_pay_logWHEREid > vn_old_id;-- 更新本次提取的idUPDATEeasou.tbl_sync_recordSETlid = vn_new_idWHEREtbl_name = 'tbl_game_pay_log';END;
EXCEPTIONWHEN OTHERS THENINSERT INTO tbl_error_log (error_time, error_desc, proc_name, deal_status)VALUES (now()::timestamp, SQLERRM, 'P_SYNC_TABLE_DATA', 0);END;

$BODY$
LANGUAGE plpgsql

最后通过使用pg_cron或者go_cron添加一个任务。就可以完成准实时的增量同步