大家好，我是考100分的小小码 ，祝大家学习进步，加薪顺利呀。今天说一说PostgreSQL通过oracle_fdw访问Oracle数据「建议收藏」,希望您对编程的造诣更进一步.
背景：
同一个项目两个系统分别使用了PG库和Oracle库，Oracle是生产库，数据动态更新，现在在PG库中需要实时的获取到更新的数据进行统计，基于此种方式，可以通过ETL的工具实现，但是需要定期进行维护等，于是想着是否可以通过类似于Oracle数据库DBLINK的方式去实现，经过网上查找相关资料，发现可以通过oracle_fdw实现。
测试环境：
本地搭建测试环境，基础配置如下：
Oracle数据库测试服务器（IP:192.168.1.110）：WIN10操作系统，Oracle数据库版本为11.2.0.4，实例名为orcl，安装有32位客户端；
PG库测试服务器（虚拟机，IP:192.168.30.128，NAT模式）：WIN10操作系统，PG数据库版本为11.11.1；
实现步骤：
1、首先确定网络通常，在PG库服务器可以访问到Oracle库服务器。
2、安装PG库（步骤略）。这里需要注意，安装完成的PG库没有开启远程访问，如果需要远程访问，需要先修改pg_hba.conf文件，添加以下内容即可。
host all all 0.0.0.0/0 md5
3、下载oracle_fdw，注意下载时候需要匹配PG库的版本。
下载地址：Releases · laurenz/oracle_fdw · GitHub
我这里下载的是匹配PG11，选择Windows64位置操作系统的。
注意：fdw版本必须和PG库版本以及操作系统版本相对应，否则后面会出问题。
3、解压oracle_fdw，将【lib】和【share/extension】文件夹中文件拷贝到PG库安装路径下对应的【lib】和【share/extension】文件夹中。
拷贝之后，通过sql语句可以查询到oracle_fdw，说明文件拷贝放置成功，但是尚未安装（isstalled_version为空）。
select * from pg_available_extensions;
4、安装Oracle客户端（步骤略）
先不用急着安装oracle_fdw（安装也不会成功），因为还需要Oracle客户端支持。如果不安装Oracle客户端，会有下面的错误提示。
Oracle客户端建议和连接的Oracle服务端采用相同版本（测试有小版本差别也不影响，大版本未测试），另外看网上资料也可以按照轻量级的oracle instant client替代，这里我没有试过，有兴趣的可以尝试一下。
安装完成后注意先进行连接测试，确保连接正常。
注意：客户端的版本必须和PG库的一致，例如我安装的是64位的PG库，那么一定要安装64位的oracle客户端，之前习惯安装了32位的客户端，在创建外部表后没法打开，提示下面错误。
如果还是有问题，可以检查安装路径是否已经写入Path变量中，将其移动至最上面。
5、创建安装oracle_fdw
-- 创建oracle_fdw create extension oracle_fdw;
安装成功后通过下面之前的语句进行验证。
select * from pg_available_extensions;
可以看到installed_version已经显示安装版本了，验证表示安装成功。
注意：如果多次安装失败，建议可以重启一下PG服务或者服务器后重试。
6、Oracle库中制作测试数据
数据库连接信息如下：192.168.1.110/orcl 用户名/密码：GIS/GIS
-- Create test table create table ORACLEDATA_TEST ( ID NUMBER(10) not null, XZQMC NVARCHAR2(50), XZQDM NVARCHAR2(30) )
-- insert test data insert into oracledata_test values(1,"市南区","370202"); insert into oracledata_test values(2,"市北区","370203");
增加测试数据后注意进行提交操作。
7、PG库创建Oracle连接
--创建Oracle外部连接，其中oradb_110为连接名称 create server oradb_110 foreign data wrapper oracle_fdw options(dbserver "192.168.1.110/orcl");
创建后可以通过连接获取Oracle数据库数据。
8、PG库进行用户授权
--授权 grant usage on foreign server oradb_110 to postgres;
授权根据实际需要进行。
9、创建到Oracle的映射
--创建到oracle的映射 create user mapping for postgres server oradb_110 options(user "GIS",password "GIS");
其中oradb_110是之前创建的数据库连接名称，GIS为连接Oracle的用户名和密码。
10、创建需要访问Oracle的对应表
注意这里创建的时候要注意字段类型的转换，Oracle和PG库在字段类型上还是有所差别的。其中oradb_110是我们上面创建的数据库连接名称，GIS是连接。
--创建需要访问的oracle中对应表的结构 create foreign table ORACLEDATA_TEST_PG ( ID numeric(10) not null, XZQMC VARCHAR(50), XZQDM VARCHAR(30) ) server oradb_110 options(schema "GIS",table "ORACLEDATA_TEST");
注意：这里建立的表并不像是视图那样获取oracle指定表中的字段，而是通过顺序映射的方式，后面会进行测试说明。
11、现在通过外部表即可查看Oracle过来的数据。
如果需要对创建的内容进行删除，可以使用下面语句：
DROP FOREIGN TABLE table_name; DROP USER MAPPING FOR user_name SERVER server_name; DROP SERVER server_name;
11、数据同步测试。
在oracle数据库中实时插入一条记录
-- insert test data insert into oracledata_test values(3,"李沧区","370203");
插入数据后注意提交，然后查询确认。
在PG库中进行查询确认：
可以看到，数据可以实时的同步过去。
12、表映射测试。
例如现在的测试表中有三个字段，我在PG库中如果只用到第一个和第三个字段，那我的外部表这样去构建：
--创建需要访问的oracle中对应表的结构 create foreign table ORACLEDATA_TEST_PG_2 ( ID numeric(10) not null, XZQDM VARCHAR(30) ) server oradb_110 options(schema "GIS",table "ORACLEDATA_TEST");
然后查询数据：
从结果中可以看出，我们选择的xzqdm获取到的并非是xzqdm的值，而是xzqmc的值，其为根据顺序映射的，并非是通过字段名称映射。
13、性能方面
初步测试了一下，对于大数据量性能还是比较低的，这块没有进行严格的测试，后面有机会可以再补充。
参考资料：
详解PostgreSQL成功安装oracle_fdw方法，解决the specified procedure could not be found错误_ljinxin的博客-CSDN博客
PostgreSQL之oracle_fdw安装与使用 – Kevin_zheng – 思创斯聊编程 (cnblogs.com)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/6173.html
