背景:
同一个项目两个系统分别使用了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)
到此这篇关于postgresql通过oracle_fdw访问oracle数据的实现步骤的文章就介绍到这了,更多相关postgresql访问oracle数据内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!