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

由小麦苗 · 发布日期 2022年4月13日 · 已更新 2023年1月30日

0 588 3

Tags： DBLink oracle_fdw PG 插件

👉 本文共约2612个字，系统预计阅读时间或需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(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-basic-21.5.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-devel-21.5.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-sqlplus-21.5.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-tools-21.5.0.0.0-1.x86_64.rpm

rpm -ivh *.rpm

mkdir -p /usr/lib/oracle/21/client64/network/admin

cat >> /root/.bashrc << "EOF"
export ORACLE_HOME=/usr/lib/oracle/21/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
EOF

source  /root/.bashrc

cat >> /etc/profile << "EOF"
export ORACLE_HOME=/usr/lib/oracle/21/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
EOF

source  /etc/profile

wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-basic-21.5.0.0.0-1.x86_64.rpm

wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-devel-21.5.0.0.0-1.x86_64.rpm

wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-sqlplus-21.5.0.0.0-1.x86_64.rpm

wget https://download.oracle.com/otn_software/linux/instantclient/215000/oracle-instantclient-tools-21.5.0.0.0-1.x86_64.rpm

rpm -ivh *.rpm

mkdir -p /usr/lib/oracle/21/client64/network/admin

cat >> /root/.bashrc << "EOF"

export ORACLE_HOME=/usr/lib/oracle/21/client64

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

EOF

source /root/.bashrc

cat >> /etc/profile << "EOF"

export ORACLE_HOME=/usr/lib/oracle/21/client64

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

export PATH=$ORACLE_HOME/bin:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

EOF

source /etc/profile

oracle_fdw下载安装

可以github（https://github.com/laurenz/oracle_fdw），也可以：https://pgxn.org/dist/oracle_fdw/

wget https://codeload.github.com/laurenz/oracle_fdw/zip/refs/tags/ORACLE_FDW_2_4_0 -O oracle_fdw-2_4_0.zip
wget https://api.pgxn.org/dist/oracle_fdw/2.4.0/oracle_fdw-2.4.0.zip
unzip oracle_fdw-2_4_0.zip
cd ORACLE_FDW_2_4_0
make && make install

wget https://codeload.github.com/laurenz/oracle_fdw/zip/refs/tags/ORACLE_FDW_2_4_0 -O oracle_fdw-2_4_0.zip

wget https://api.pgxn.org/dist/oracle_fdw/2.4.0/oracle_fdw-2.4.0.zip

unzip oracle_fdw-2_4_0.zip

cd ORACLE_FDW_2_4_0

make && make install

操作步骤

新建oracle_fdw插件。命令如下：

CREATE EXTENSION oracle_fdw;
postgres=# select oracle_diag();
                        oracle_diag                         
-------------------------------------------------------------
oracle_fdw 2.4.0, PostgreSQL 12.9, Oracle client 21.5.0.0.0
(1 row)

CREATE EXTENSION oracle_fdw;

postgres=# select oracle_diag();

oracle_diag

-------------------------------------------------------------

oracle_fdw 2.4.0, PostgreSQL 12.9, Oracle client 21.5.0.0.0

(1 row)

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

CREATE SERVER <SERVER名称>
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//<Oracle数据库的内网连接地址>:<Oracle数据库的内网连接端口>/<数据库名>');

CREATE SERVER oradb
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (host '<Oracle数据库的内网连接地址>', port '<Oracle数据库的内网连接端口>', dbname '<数据库名>');

create server ora21c 
foreign data wrapper oracle_fdw 
options(dbserver '//172.17.0.4:1521/LHRCDB');

CREATE SERVER <SERVER名称>

FOREIGN DATA WRAPPER oracle_fdw

OPTIONS (dbserver '//<Oracle数据库的内网连接地址>:<Oracle数据库的内网连接端口>/<数据库名>');

CREATE SERVER oradb

FOREIGN DATA WRAPPER oracle_fdw

OPTIONS (host '<Oracle数据库的内网连接地址>', port '<Oracle数据库的内网连接端口>', dbname '<数据库名>');

create server ora21c

foreign data wrapper oracle_fdw

options(dbserver '//172.17.0.4:1521/LHRCDB');

创建用户映射。命令如下：

CREATE USER MAPPING
FOR <PostgreSQL用户名> SERVER <映射名>
OPTIONS (user '<Oracle数据库用户名>', password '<Oracle数据库用户密码>');

CREATE USER MAPPING

FOR <PostgreSQL用户名> SERVER <映射名>

OPTIONS (user '<Oracle数据库用户名>', password '<Oracle数据库用户密码>');

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

CREATE USER MAPPING
FOR pguser SERVER oradb
OPTIONS (user 'orauser', password 'orapwd');

sqlplus system/lhr@172.17.0.4:1521/LHRCDB
CREATE TABLE SYSTEM.EMP(
       EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2) );
INSERT INTO SYSTEM.EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO SYSTEM.EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO SYSTEM.EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO SYSTEM.EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO SYSTEM.EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO SYSTEM.EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO SYSTEM.EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO SYSTEM.EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO SYSTEM.EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO SYSTEM.EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO SYSTEM.EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO SYSTEM.EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO SYSTEM.EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO SYSTEM.EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

create user mapping for postgres server ora21c options (user 'system', password 'lhr');

CREATE USER MAPPING

FOR pguser SERVER oradb

OPTIONS (user 'orauser', password 'orapwd');

sqlplus system/lhr@172.17.0.4:1521/LHRCDB

CREATE TABLE SYSTEM.EMP(

EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2) );

INSERT INTO SYSTEM.EMP VALUES

(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO SYSTEM.EMP VALUES

(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

INSERT INTO SYSTEM.EMP VALUES

(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

INSERT INTO SYSTEM.EMP VALUES

(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

INSERT INTO SYSTEM.EMP VALUES

(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

INSERT INTO SYSTEM.EMP VALUES

(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

INSERT INTO SYSTEM.EMP VALUES

(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

INSERT INTO SYSTEM.EMP VALUES

(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);

INSERT INTO SYSTEM.EMP VALUES

(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

INSERT INTO SYSTEM.EMP VALUES

(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

INSERT INTO SYSTEM.EMP VALUES

(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);

INSERT INTO SYSTEM.EMP VALUES

(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

INSERT INTO SYSTEM.EMP VALUES

(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

INSERT INTO SYSTEM.EMP VALUES

(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

COMMIT;

create user mapping for postgres server ora21c options (user 'system', password 'lhr');

创建Oracle的外部表。示例如下：


CREATE FOREIGN TABLE oraemp (
         empno      integer OPTIONS (key 'true')  NOT NULL,
         ename      character varying(30),
         job        character varying(9),
         mgr        smallint,
         hiredate   timestamp(0) without time zone,
         sal        numeric(7,2),
         comm       numeric(7,2),
         deptno     smallint
      )
SERVER ora21c OPTIONS (schema 'SYSTEM',table 'EMP');

CREATE FOREIGN TABLE oratab (
         id        integer OPTIONS (key 'true')  NOT NULL,
         text      character varying(30),
         floating  double precision  NOT NULL
      )
SERVER ora21c OPTIONS (table 'ORATAB',
                              schema 'SYSTEM',
                              max_long '32767',
                              readonly 'false',
                              sample_percent, '100',
                              prefetch, '200');

lhrdb=# select * from oraemp;
empno | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno 
-------+--------+-----------+------+---------------------+---------+---------+--------
 7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20
 7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
 7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
 7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20
 7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
 7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30
 7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10
 7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20
 7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10
 7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
 7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20
 7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30
 7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20
 7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10
(14 rows)

CREATE FOREIGN TABLE oraemp (

empno integer OPTIONS (key 'true') NOT NULL,

ename character varying(30),

job character varying(9),

mgr smallint,

hiredate timestamp(0) without time zone,

sal numeric(7,2),

comm numeric(7,2),

deptno smallint

)

SERVER ora21c OPTIONS (schema 'SYSTEM',table 'EMP');

CREATE FOREIGN TABLE oratab (

id integer OPTIONS (key 'true') NOT NULL,

text character varying(30),

floating double precision NOT NULL

)

SERVER ora21c OPTIONS (table 'ORATAB',

schema 'SYSTEM',

max_long '32767',

readonly 'false',

sample_percent, '100',

prefetch, '200');

lhrdb=# select * from oraemp;

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

7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | | 20

7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30

7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30

7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | | 20

7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30

7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | | 30

7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | | 10

7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | | 20

7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000.00 | | 10

7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30

7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | | 20

7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | | 30

7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | | 20

7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | | 10

(14 rows)

说明：

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.

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

-- 导入命令
IMPORT FOREIGN SCHEMA <ora_schema_name>
FROM SERVER <server_name>
INTO <schema_name>
OPTIONS (case 'lower');

-- 示例：会把所有的SYSTEM用户下的表结构导入到PostgreSQL下
IMPORT FOREIGN SCHEMA "SYSTEM"
FROM SERVER ora21c
INTO public
OPTIONS (case 'lower');

postgres=# select * from emp;
 empno | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno 
-------+--------+-----------+------+---------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10
(14 rows)
lhrdb=# \d
                        List of relations
 Schema |            Name             |     Type      |  Owner   
--------+-----------------------------+---------------+----------
 public | aq$_internet_agent_privs    | foreign table | postgres
 public | aq$_internet_agents         | foreign table | postgres
。。。。。。。。
 public | logmnr_age_spill$           | foreign table | postgres
 public | logmnr_attrcol$             | foreign table | postgres
 public | logmnr_attribute$           | foreign table | postgres
 public | logmnr_ccol$                | foreign table | postgres
 public | logmnr_cdef$                | foreign table | postgres
 public | logmnr_col$                 | foreign table | postgres
。。。。。。。。
 public | scheduler_job_args          | foreign table | postgres
 public | scheduler_job_args_tbl      | foreign table | postgres
 public | scheduler_program_args      | foreign table | postgres
 public | scheduler_program_args_tbl  | foreign table | postgres
 public | sqlplus_product_profile     | foreign table | postgres
(146 rows)

-- 导入命令

IMPORT FOREIGN SCHEMA <ora_schema_name>

FROM SERVER <server_name>

INTO <schema_name>

OPTIONS (case 'lower');

-- 示例：会把所有的SYSTEM用户下的表结构导入到PostgreSQL下

IMPORT FOREIGN SCHEMA "SYSTEM"

FROM SERVER ora21c

INTO public

OPTIONS (case 'lower');

postgres=# select * from emp;