PGTOOracle增量同步外部表

  • Z时代
  • 2024-01-10
  • 分类：综合

database

背景

　　最近在负责公司数据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.html
2、 安装步骤
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 oracle
2.3 配置网络文件
[root@Postgres201 app]# cd oracle
[root@Postgres201 oracle]# mkdir -p network/admin
[root@Postgres201 oracle]# cd network/admin/
[root@Postgres201 admin]# vi tnsnames.ora
ora221 =
  (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_PATH
export  PATH=$ORACLE_HOME:$PATH

二、安装oracle_fdw

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

1、下载地址
http://pgxn.org/dist/oracle_fdw/
2、安装步骤
[root@Postgres201 opt]# unzip oracle_fdw-2.0.0.zip
[root@Postgres201 opt]# cd oracle_fdw-2.0.0
#加载环境变量后执行 pg_config是否在对应PGHOME/bin目录下。编译后会在对应的目录下面
[root@Postgres201 oracle_fdw-2.0.0]# source /home/postgres/.bashrc 
[root@Postgres201 oracle_fdw-2.0.0]# pg_config
[root@Postgres201 oracle_fdw-2.0.0]# make
[root@Postgres201 oracle_fdw-2.0.0]# make install
FAQ：执行make若出现“/usr/bin/ld: cannot find -lclntsh”；原因是找不到库liblclntsh文件；
解决方案：
1.    检查环境变量，看ORACLE有关的环境变量是否设置正确
2.    是否文件名字后有oracle版本信息；需要改名字
该文件在oracle安装目录下；本例是需要改名字即可
[root@Postgres201 oracle]# ln -sv libclntsh.so.11.1 libclntsh.so
[root@Postgres201 oracle_fdw-2.0.0]# make
[root@Postgres201 oracle_fdw-2.0.0]# make install
3、创建外部扩展
adsas=# create extension oracle_fdw;
ERROR:  could not load library "/app/pg12/lib/postgresql/oracle_fdw.so": libclntsh.so.11.1: cannot open shared object file: No such file or directory
解决方案：
1、创建文件/etc/ld.so.conf.d/oracle.conf
2、添加内容；libclntsh.so.11.1文件所在的路径
/app/oracle
3、执行加载ldconfig
4、再登录psql执行
adsas=# create extension oracle_fdw;
CREATE EXTENSION

三、创建外部表

CREATEFOREIGNTABLE fdw_game_pay_log (
ID int8 OPTIONS(key"true"),
PAY_TIME timestampNOTNULL,
APPID int2 NOTNULL,
QN varchar(20) NOTNULL,
ACCOUNTID varchar(64) NOTNULL,
AMOUNT decimal(6,2),
ORDER_NO textNOTNULL,
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=>insertinto fdw_game_pay_log select*from tbl_game_pay_log;
ERROR:  error executing query: OCIStmtExecute failed toexecute remote query
DETAIL:  ORA-08177: can"t serialize access for this transaction

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

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

adsas=>begin;
BEGIN
adsas=>insertinto fdw_game_pay_log select*from tbl_game_pay_log;
INSERT01
adsas=>end;
COMMIT

五、同步脚本

CREATEORREPLACEPROCEDURE "easou"."P_SYNC_TABLE_DATA" ()
AS $BODY$
/***********************************************************
     将表tbl_game_pay_log增量复制到fdw_game_pay_log；
     达到PG-Oracle数据增量同步
     ***********************************************************/
DECLARE
    vn_old_id int8;
    vn_new_id int8;
BEGIN
    BGEIN
-- 获取上次提取的id
SELECT
        lid INTO vn_old_id
FROM
        easou.tbl_sync_record
WHERE
        tbl_name ="tbl_game_pay_log";
-- 截取本次提取的最大id
SELECT
COALESCE(max(id), 0) INTO vn_new_id
FROM
        easou.tbl_game_pay_log;
-- 将本次提取的记录插入外部表
INSERTINTO easou.fdw_game_pay_log (id, PAY_TIME, APPID, QN, ACCOUNTID, AMOUNT, ORDER_NO, CP_ORDER_NO)
SELECT
        id,
        PAY_TIME,
        APPID,
        QN,
        ACCOUNTID,
        AMOUNT,
        ORDER_NO,
        CP_ORDER_NO
FROM
        easou.tbl_game_pay_log
WHERE
        id > vn_old_id;
-- 更新本次提取的id
UPDATE
        easou.tbl_sync_record
SET
        lid = vn_new_id
WHERE
        tbl_name ="tbl_game_pay_log";
END;
EXCEPTION
WHEN OTHERS THEN
INSERTINTO 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添加一个任务。就可以完成准实时的增量同步

以上是 PGTOOracle增量同步外部表 的全部内容， 来源链接： utcz.com/z/533379.html

回到顶部