pg14.8通过外部表访问oracle数据库

mahan

下面以oracle_fdw为例来说明：


没有说明，都是在pg服务器上操作，使用PG数据库的安装用户postgres进行操作。
环境：linux 8.6
          pg:14.8
          oracle:19.19

1.安装 oracle 客户端
   具体安装过程略

2.测试ORACLE客户端可以访问ORACLE数据库
  sql>conn MH/MH@192.168.51.92:1521/ORCL

3.安装 oracle_fdw
  软件源码：  http://pgxn.org/dist/oracle_fdw/  
  找对应PG版本的ORACLE_FDW版本，目前我们找到的是oracle_fdw 2.5.0
  使用源码安装
  编译前记得临时export ORACLE_HOME，不然make会失败
   export ORACLE_HOME='/app/oracle/product/19/db_1
   unzip oracle_fdw-ORACLE_FDW_2_5_0.zip
   cd /postgresql/pg14/soft/oracle_fdw-ORACLE_FDW_2_5_0/
   make
   make install

4.创建扩展
   create extension oracle_fdw;

--这个操作有时要报错：
ERROR:  could not load library "/postgresql/pg14/lib/oracle_fdw.so": libclntsh.so.19.1: 无法打开共享对象文件: 没有那个文件或目录


[postgres@nurse72 admin]$ ldd /postgresql/pg14/lib/oracle_fdw.so
        linux-vdso.so.1 (0x00007ffcc311c000)
        libclntsh.so.19.1 => not found
        libc.so.6 => /lib64/libc.so.6 (0x00007ff708565000)
        /lib64/ld-linux-x86-64.so.2 (0x00007ff708b50000)


可以通过软连接解决：
$ ln -s $ORACLE_HOME/libclntsh.so.19.1 /postgresql/pg14/lib/libclntsh.so.19.1

找不到libclntsh.so.19.1这个动态库  可以用find命令
find / -name libclntsh.so.19.1


创建扩展后查询：
   select * from pg_extension;
   oid   |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+--------------------+----------+--------------+----------------+------------+-----------+--------------
。。。
2409616 | oracle_fdw         |       10 |         2200 | t              | 1.2        |           |
。。。




5.创建外部数据源服务
create server oradb foreign data wrapper oracle_fdw options(dbserver '192.168.51.92/ORCL');
6.建用户映射
grant usage on foreign server oradb to postgres;
create user mapping for postgres server oradb options(user 'MH',password 'MH');


6.ORACLE库创建个测试表
create table ORACLEDATA_TEST
(
   ID   NUMBER(10) not null,
   XZQMC        NVARCHAR2(50),
   XZQDM        NVARCHAR2(30)
)

insert into oracledata_test values(1,'南区','370202');
insert into oracledata_test values(2,'北区','370203');

7.PG库创建需要访问oracle的对应表
create foreign  table ORACLEDATA_TEST_PG
(
   ID   numeric(10) not null,
   XZQMC        VARCHAR(50),
   XZQDM        VARCHAR(30)
) server oradb_110 options(schema 'MH',table 'ORACLEDATA_TEST');


8.在PG上面查询表
select * from ORACLEDATA_TEST_PG

查询表报错：
ERROR: error connecting to oracle: OCIEnvcreate failed to create environment handle
DETAIL:


systemctl list-unit-files |grep -i p*
取出pg库的服务名跟port是多少，我这台机器是pg14.service，port是默认的5432

然后修改服务配置文件，添加ORACLE_HOME参数
vim /usr/lib/systemd/system/PG14.service

[Service]

Environment=ORACLE_HOME=/app/oracle/product/19/db_1
。。。


重启服务
# systemctl daemon-reload
# systemctl stop pg14.service
# systemctl start pg14.service

不再报错。