PostgreSQL-Oracle_FDW

原创 Oracle 2023-07-07

248

1、配置oracle客户端

unzip instantclient-basic-linux.x64-12.2.0.1.0.zip unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip unzip instantclient-sqlplus-linux.x64-12.2.0.1.0.zip ## 配置环境变量 export ORACLE_HOME=/home/postgres/instantclient_12_2 export TNS_ADMIN=/home/postgres/instantclient_12_2 export LD_LIBRARY_PATH=/home/postgres/instantclient_12_2:/home/postgres/pg13/lib export OCI_LIB_DIR=$ORACLE_HOME export OCI_INC_DIR=$ORACLE_HOME/sdk/include PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin:$ORACLE_HOME

2、编译/安装oracle_fdw

[postgres@devin-enmo ~]$ cd oracle_fdw-2.4.0 [postgres@devin-enmo oracle_fdw-2.4.0]$ ls CHANGELOG LICENSE META.json oracle_fdw--1.0--1.1.sql oracle_fdw--1.2.sql oracle_fdw.control oracle_fdw.o oracle_utils.c README.oracle_fdw TODO expected Makefile msvc oracle_fdw--1.1--1.2.sql oracle_fdw.c oracle_fdw.h oracle_gis.c README.md sql [postgres@devin-enmo oracle_fdw-2.4.0]$ make gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/home/postgres/instantclient_12_2/sdk/include" -I"/home/postgres/instantclient_12_2/oci/include" -I"/home/postgres/instantclient_12_2/rdbms/public" -I"/home/postgres/instantclient_12_2/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/home/postgres/pg13/include/server -I/home/postgres/pg13/include/internal -D_GNU_SOURCE -c -o oracle_utils.o oracle_utils.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I"/home/postgres/instantclient_12_2/sdk/include" -I"/home/postgres/instantclient_12_2/oci/include" -I"/home/postgres/instantclient_12_2/rdbms/public" -I"/home/postgres/instantclient_12_2/" -I/usr/include/oracle/21/client64 -I/usr/include/oracle/19.12/client64 -I/usr/include/oracle/19.12/client -I/usr/include/oracle/19.11/client64 -I/usr/include/oracle/19.11/client -I/usr/include/oracle/19.10/client64 -I/usr/include/oracle/19.10/client -I/usr/include/oracle/19.9/client -I/usr/include/oracle/19.9/client64 -I/usr/include/oracle/19.8/client -I/usr/include/oracle/19.8/client64 -I/usr/include/oracle/19.6/client -I/usr/include/oracle/19.6/client64 -I/usr/include/oracle/19.3/client -I/usr/include/oracle/19.3/client64 -I/usr/include/oracle/18.5/client -I/usr/include/oracle/18.5/client64 -I/usr/include/oracle/18.3/client -I/usr/include/oracle/18.3/client64 -I/usr/include/oracle/12.2/client -I/usr/include/oracle/12.2/client64 -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/home/postgres/pg13/include/server -I/home/postgres/pg13/include/internal -D_GNU_SOURCE -c -o oracle_gis.o oracle_gis.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/home/postgres/pg13/lib -Wl,--as-needed -Wl,-rpath,'/home/postgres/pg13/lib',--enable-new-dtags -L"/home/postgres/instantclient_12_2/" -L"/home/postgres/instantclient_12_2/bin" -L"/home/postgres/instantclient_12_2/lib" -L"/home/postgres/instantclient_12_2/lib/amd64" -lclntsh -L/usr/lib/oracle/21/client64/lib -L/usr/lib/oracle/19.12/client64/lib -L/usr/lib/oracle/19.12/client/lib -L/usr/lib/oracle/19.11/client64/lib -L/usr/lib/oracle/19.11/client/lib -L/usr/lib/oracle/19.10/client64/lib -L/usr/lib/oracle/19.10/client/lib -L/usr/lib/oracle/19.9/client/lib -L/usr/lib/oracle/19.9/client64/lib -L/usr/lib/oracle/19.8/client/lib -L/usr/lib/oracle/19.8/client64/lib -L/usr/lib/oracle/19.6/client/lib -L/usr/lib/oracle/19.6/client64/lib -L/usr/lib/oracle/19.3/client/lib -L/usr/lib/oracle/19.3/client64/lib -L/usr/lib/oracle/18.5/client/lib -L/usr/lib/oracle/18.5/client64/lib -L/usr/lib/oracle/18.3/client/lib -L/usr/lib/oracle/18.3/client64/lib -L/usr/lib/oracle/12.2/client/lib -L/usr/lib/oracle/12.2/client64/lib -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib [postgres@devin-enmo oracle_fdw-2.4.0]$ make install /bin/mkdir -p '/home/postgres/pg13/lib' /bin/mkdir -p '/home/postgres/pg13/share/extension' /bin/mkdir -p '/home/postgres/pg13/share/extension' /bin/mkdir -p '/home/postgres/pg13/share/doc/extension' /bin/install -c -m 755 oracle_fdw.so '/home/postgres/pg13/lib/oracle_fdw.so' /bin/install -c -m 644 .//oracle_fdw.control '/home/postgres/pg13/share/extension/' /bin/install -c -m 644 .//oracle_fdw--1.2.sql .//oracle_fdw--1.0--1.1.sql .//oracle_fdw--1.1--1.2.sql '/home/postgres/pg13/share/extension/' /bin/install -c -m 644 .//README.oracle_fdw '/home/postgres/pg13/share/doc/extension/'

3、创建扩展

[postgres@devin-enmo ~]$ psql
psql (13.2)
Type "help" for help.

postgres=# create extension oracle_fdw;
CREATE EXTENSION
postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 oracle_fdw         | 1.2     | public     | foreign data wrapper for Oracle access
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(4 rows)

4、创建外部服务器

postgres=# create server oradb foreign data wrapper oracle_fdw options(dbserver '//192.168.56.102:1521/hkora');
CREATE SERVER

5、创建用户映射

postgres=# create user mapping for devin server oradb options(user 'devin',password 'xjlove1224');
CREATE USER MAPPING

6、创建外部表

postgres=# create foreign table FDW_TEST
postgres-# (
postgres(#   owner             varchar(128),
postgres(#   object_name       varchar(128),
postgres(#   subobject_name    varchar(128),
postgres(#   object_id         int options(key 'true') not null,
postgres(#   data_object_id    int,
postgres(#   object_type       varchar(23),
postgres(#   created           DATE,
postgres(#   last_ddl_time     DATE,
postgres(#   timestamp         varchar(19),
postgres(#   status            varchar(7),
postgres(#   temporary         varchar(1),
postgres(#   generated         varchar(1),
postgres(#   secondary         varchar(1),
postgres(#   namespace         int,
postgres(#   edition_name      varchar(128),
postgres(#   sharing           varchar(18),
postgres(#   editionable       varchar(1),
postgres(#   oracle_maintained varchar(1),
postgres(#   application       varchar(1),
postgres(#   default_collation varchar(100),
postgres(#   duplicated        varchar(1),
postgres(#   sharded           varchar(1),
postgres(#   created_appid     int,
postgres(#   created_vsnid     int,
postgres(#   modified_appid    int,
postgres(#   modified_vsnid    int
postgres(# ) server oradb options(schema 'DEVIN',table 'FDW_TEST');
CREATE FOREIGN TABLE

    
## 1，如果需要insert，delete，update外部表，需要指定主键，options(key 'true')
## 2，映射表字段需要保持一致
## 3，oracle端表字段发生变化，需要重建外部表

7、测试

## select
postgres=# select count(*) from fdw_test;
 count
-------
 72871
(1 row)

## insert 
略

## update
SQL> select owner,object_Name from fdw_test where object_id=9;

OWNER       OBJECT_NAME
---------   ----------
SYS         I_FILE#_BLOCK#

postgres=# update fdw_test set owner='devin' where object_id=9;
UPDATE 1

SQL> select owner,object_Name from fdw_test where object_id=9;

OWNER      OBJECT_NAME
--------   ------------
devin      I_FILE#_BLOCK#

## delete 
SQL> select owner,object_Name from fdw_test where object_id=10;

OWNER      OBJECT_NAME
---------  --------------
SYS        C_USER#

postgres=# delete from fdw_test where object_id=10;
DELETE 1

SQL> select owner,object_Name from fdw_test where object_id=10;

no rows selected

postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者