postgresql访问oracle数据
测试环境为CentOS Linux release 7.9，oracle11g,pg10
1.安装oracle客户端
https://www.oracle.com/database/technologies/instant-client.html
根据系统选择对应版本
分别下载如下安装包
使用rpm -ivh oracle-instantclient-basic-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-devel-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-jdbc-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-odbc-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-sqlplus-21.6.0.0.0-1.x86_64.rpm oracle-instantclient-tools-21.6.0.0.0-1.x86_64.rpm
mkdir -p /usr/lib/oracle/21/client64/network/admin
cat /root/.bashrc
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
cat /etc/.bash_profile
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
设置完成后source下生效
2.下载oracle_fdw插件
https://github.com/laurenz/oracle_fdw/releases/tag/ORACLE_FDW_2_4_0
下载源码，解压
然后切换到解压后的目录下
cd /tmp/oracle_fdw-ORACLE_FDW_2_4_0
make && make install
3.连接pg数据库
create extension oracle_fdw;
postgres=# select oracle_diag()
postgres-# ;
oracle_diag
--------------------------------------------------------------
oracle_fdw 2.4.0, PostgreSQL 10.20, Oracle client 21.6.0.0.0
4.创建数据库映射
create server oracledb foreign data wrapper oracle_fdw options(dbserver '//172.17.0.4:1521/LHRCDB');
create server oracledb foreign data wrapper oracle_fdw options(dbserver '//10.9.24.12:1521/orcl');
CREATE SERVER
5.创建用户映射
create user mapping for user01(pg中的用户名) server oracledb OPTIONS (user 'oracle1'（oracle用户名）,password 'oracle'（oracle用户名对应的密码）);
postgres=# create user mapping for user01 server oracledb OPTIONS (user 'oracle1',password 'oracle');
CREATE USER MAPPING
6.创建表映射
create foreign table fdw01_ora(id int,name varchar(10)) SERVER oracledb OPTIONS (schema 'ORACLE1',table 'FDW01');//schema和table名一定要大写
注意权限
postgres=> create foreign table fdw01_ora(id int,name varchar(10)) SERVER oracledb OPTIONS (schema 'ORACLE1',table 'FDW01');]
ERROR: permission denied for foreign server oracledb]()
postgres=>
postgres=>
postgres=> GRANT USAGE ON FOREIGN SERVER oracledb TO user01;
注意
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功能。
7.验证查询
postgres=> select * from fdw01_ora;
id | name
----+------
1 | aa
2 | bb
3 | cc
(3 rows)
8.import一键导入所有表
IMPORT FOREIGN SCHEMA FROM SERVER INTO OPTIONS (case 'lower'); -- 示例：会把所有的SYSTEM用户下的表结构导入到PostgreSQL下 IMPORT FOREIGN SCHEMA "ORACLE1" FROM SERVER oracledb INTO public OPTIONS (case 'lower');
case取值如下：
keep：表示保留Oracle上的对象名，通常是大写。
lower：表示转换所有的对象名为小写。
smart：表示只将对象名中都是大写字母的替换为小写。
SQL> select * from fdw01;
ID NAME
---------- ----------
1 aa
2 bb
3 cc
SQL> create table fdw02 as select * from fdw01;
Table created.
//为了区分fdw01删除fdw02数据重新插入
SQL> truncate table fdw02;
Table truncated.
SQL> insert into fdw02 values(11,'ddd');
1 row created.
SQL> insert into fdw02 values(22,'eee');
1 row created.
SQL> insert into fdw02 values(33,'fff');
1 row created.
SQL> select * from fdw02;
ID NAME
---------- ----------
11 ddd
22 eee
33 fff
SQL> show autocommit;
autocommit OFF
SQL> commit;
Commit complete.
IMPORT 导入oracle中schema为ORACLE1的所有表
postgres=> IMPORT FOREIGN SCHEMA "ORACLE1" FROM SERVER oracledb INTO public OPTIONS (case 'lower');
IMPORT FOREIGN SCHEMA
postgres=>
postgres=> \d
List of relations
Schema | Name | Type | Owner
--------+-----------+---------------+----------
public | FDW01 | foreign table | user01
public | FDW02 | foreign table | user01
public | fdw01 | foreign table | user01
public | fdw01_ora | foreign table | user01
public | fdw02 | foreign table | user01
public | rsl | table | postgres
public | t2 | table | postgres
public | test | table | postgres
(8 rows)
postgres=> select * from fdw01;
id | name
----+------
1 | aa
2 | bb
3 | cc
(3 rows)
postgres=> select * from fdw01_ora;
id | name
----+------
1 | aa
2 | bb
3 | cc
(3 rows)
postgres=> select * from fdw02;
id | name
----+------
11 | ddd
22 | eee
33 | fff
(3 rows)
postgres=>
注意事项
如果需要执行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子句将不会被下推。
安装postgis插件后，oracle_fdw插件支持以下空间数据类型：
POINT
LINE
POLYGON
MULTIPOINT
MULTILINE
MULTIPOLYGON
常用命令
postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# select * from pg_user_mapping; 查看用户映射表
umuser | umserver | umoptions
--------+----------+--------------------------------
17163 | 17279 | {user=oracle1,password=oracle}
(1 row)
postgres=# select * from pg_foreign_table; 查看外部表
ftrelid | ftserver | ftoptions
---------+----------+------------------------------
17296 | 17279 | {schema=ORACLE1,table=FDW01}
17299 | 17279 | {schema=ORACLE1,table=FDW01}
17302 | 17279 | {schema=ORACLE1,table=FDW02}
17305 | 17279 | {schema=ORACLE1,table=FDW01}
17308 | 17279 | {schema=ORACLE1,table=FDW02}
(5 rows)
postgres=# select * from pg_foreign_server; 查看外部映射的服务信息
srvname | srvowner | srvfdw | srvtype | srvversion | srvacl |
srvoptions
----------+----------+--------+---------+------------+-----------------------------------------+----
-------------------------------
oracledb | 10 | 17278 | | | {postgres=U/postgres,user01=U/postgres} | {db
server=//10.9.24.12:1521/orcl}
(1 row)
postgres=# select * from pg_foreign_data_wrapper;
fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
------------+----------+------------+--------------+--------+------------
oracle_fdw | 10 | 17273 | 17274 | |
(1 row)
postgres=# select * from pg_extension; 查看插件
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
------------+----------+--------------+----------------+------------+-----------+--------------
plpgsql | 10 | 11 | f | 1.0 | |
oracle_fdw | 10 | 2200 | t | 1.2 | |
(2 rows)
本文参考小麦苗老师博客：https://www.xmmup.com/pgchajianzhioracle_fdwkeyirangpgzhijiefangwenoracle.html#oracle_fdw_xia_zai_an_zhuang
https://help.aliyun.com/document_detail/169143.html
