PG插件之oracle_fdw可以让PG直接访问Oracle

0    505    3

Tags:

👉 本文共约2572个字,系统预计阅读时间或需10分钟。

简介

PG中的file_fdw、postgres_fdw和dblink

github:https://github.com/laurenz/oracle_fdw

基于FDW(Foreign Data Wrappers)插件可以如本地表一般在PG里访问其他异构数据库的表,下面再次记录并分享下oracle_fdw的配置使用过程,其他fdw如tds_fdw(访问sqlserver),mysql_fdw(访问mysql)类似。

oracle_fdw是PostgreSQL外部表插件,可以读取Oracle数据库的数据,也非常方便地实现PostgreSQL与Oracle数据同步。Oracle_fdw是PG的一个外部数据接口,可以使PostgreSQL轻松跨库操作Oracle。

Oracle_fdw的作用有以下两点:

  • PG可以跨库增删改查Oracle中的表,可以查询Oracle的视图,可以使PG中的表和Oracle中表/视图作Join查询,类似dblink的功能。
  • 快速将Oralce表迁移进入PostgreSQL。

安装

oracle_fdw 的编译依赖系统中需要有pg_config和Oracle客户端的环境。

Oracle客户端环境

参考:https://www.xmmup.com/oraclekehuduananzhuang.html

从oracle官网下载 ‘Basic’ and ‘SDK’,sqlplus和tools可选:
下载网址:https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

oracle_fdw下载安装

可以github(https://github.com/laurenz/oracle_fdw),也可以:https://pgxn.org/dist/oracle_fdw/

操作步骤

  1. 新建oracle_fdw插件。命令如下:

  2. 创建Oracle数据库映射。有如下两种命令:

  3. 创建用户映射。命令如下:

    说明 如果不在PostgreSQL数据库中存储Oracle用户凭证,可以设置user为空字符串,然后提供必须的外部授权。

  4. 创建Oracle的外部表。示例如下:

    本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

    说明:

    1、外部表的结构需要和Oracle中的映射表结构保持一致。

    2、oracle端表字段发生变化,需要重建外部表

    OPTIONS内的参数说明如下。

    参数说明
    key是否设置对应的列为主键,取值为true或false,默认值为false。如果要执行UPDATE和DELETE操作,必须将所有主键列设置为true。
    table表名,一般是大写,必填参数。可以使用Oracle的SQL来定义table变量的值,例如:OPTIONS (table '(SELECT col FROM tab WHERE val = ''string'')'),此时不要使用schema参数。
    schema一般是Oracle用户名,保持大写,用来访问不属于当前连接用户的表。
    max_long限制Oracle表中LONG、LONG RAW、XMLTYPE类型列的最大长度,取值范围是1~1073741823,默认值是32767。
    readonly限制Oracle表为只读,不允许INSERT、UPDATE、DELETE操作。
    sample_percent设置随机选择Oracle表数据的比例,用于PostgreSQL表统计信息,取值范围是0.000001~100,默认值是100。
    prefetch外表扫描时,PostgreSQL和Oracle数据表之间一次性传输的行数,取值范围是0~1024,默认值是200,0代表取消prefetch功能。

完成以上步骤即可通过操作外表来实现对Oracle表的操作。支持DELETE、INSERT、UPDATE、SELECT等基本操作。

一键导入所有表、视图和物化视图结构

IMPORT FOREIGN SCHEMA will create foreign tables for all objects found in ALL_TAB_COLUMNS. That includes tables, views and materialized views, but not synonyms.

支持导入外部表定义的操作,命令如下:

说明 case取值如下:

  • keep:表示保留Oracle上的对象名,通常是大写。
  • lower:表示转换所有的对象名为小写。
  • smart:表示只将对象名中都是大写字母的替换为小写。

卸载插件

卸载插件命令如下:

相关视图

注意事项

  • 如果需要执行UPDATE和DELETE操作,需要在创建外部表时为主键列设置key参数,详情请参见创建外部表
  • 外部表定义的列数据类型必须是oracle_fdw可以识别并可以转换的,oracle_fdw插件对于数据类型的转换规则请参见Data types
  • WHERE子句和ORDER BY子句支持计算下推,即oracle_fdw会将子句发送给Oracle进行计算。
  • JOIN操作支持计算下推,但是有以下注意事项:
    • 两个表必须被定义在相同的映射中。
    • 三个及以上的JOIN操作不支持下推。
    • JOIN操作必须包含在SELECT操作中。
    • 没有JOIN条件的CROSS JOIN操作不支持下推。
    • 如果JOIN子句被下推,ORDER BY子句将不会被下推。
  • oracle_fdw支持postgis插件,安装postgis插件后支持空间数据类型,包括:
    • POINT
    • LINE
    • POLYGON
    • MULTIPOINT
    • MULTILINE
    • MULTIPOLYGON

参考

https://help.aliyun.com/document_detail/169143.html

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

14 − 13 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部