postgresql oracle_fdw访问oracle数据

1、环境准备--下载安装包https://github.com/laurenz/oracle_fdw/releases--下载安装oracle clientoracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpmoracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpmoracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpmoracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm--编辑pg环境变量添加oracle lib变量postgrespg117 ~]$ cat ~/.bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH$PATH:$HOME/.local/bin:$HOME/binexport PATHexport PGHOME/postgresql/pg18export PGDATA/postgresql/pgdataexport PATH$PGHOME/bin:$PATHexport LD_LIBRARY_PATH$PGHOME/lib:/usr/lib/oracle/12.2/client64/lib:$LD_LIBRARY_PATH2、编译安装oracle_fdw[postgrespg117 soft]$ tar zxvf oracle_fdw-ORACLE_FDW_2_8_0.tar.gzoracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw-ORACLE_FDW_2_8_0/.gitignoreoracle_fdw-ORACLE_FDW_2_8_0/CHANGELOGoracle_fdw-ORACLE_FDW_2_8_0/LICENSEoracle_fdw-ORACLE_FDW_2_8_0/Makefileoracle_fdw-ORACLE_FDW_2_8_0/README.mdoracle_fdw-ORACLE_FDW_2_8_0/README.oracle_fdworacle_fdw-ORACLE_FDW_2_8_0/TODOoracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_fdw.outoracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_gis.outoracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_import.outoracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_join.outoracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw.propsoracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw.slnoracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw.vcxprojoracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_msvc.coracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw--1.0--1.1.sqloracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw--1.1--1.2.sqloracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw--1.2.sqloracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw.coracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw.controloracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw.horacle_fdw-ORACLE_FDW_2_8_0/oracle_gis.coracle_fdw-ORACLE_FDW_2_8_0/oracle_utils.coracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_fdw.sqloracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_gis.sqloracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_import.sqloracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_join.sql[postgrespg117 soft]$ cd oracle_fdw-ORACLE_FDW_2_8_0/[postgrespg117 oracle_fdw-ORACLE_FDW_2_8_0]$ lltotal 488-rw-rw-r-- 1 postgres postgres 29202 May 10 2025 CHANGELOGdrwxrwxr-x 2 postgres postgres 98 May 10 2025 expected-rw-rw-r-- 1 postgres postgres 1059 May 10 2025 LICENSE-rw-rw-r-- 1 postgres postgres 1475 May 10 2025 Makefiledrwxrwxr-x 2 postgres postgres 99 May 10 2025 msvc-rw-rw-r-- 1 postgres postgres 231 May 10 2025 oracle_fdw--1.0--1.1.sql-rw-rw-r-- 1 postgres postgres 240 May 10 2025 oracle_fdw--1.1--1.2.sql-rw-rw-r-- 1 postgres postgres 1244 May 10 2025 oracle_fdw--1.2.sql-rw-rw-r-- 1 postgres postgres 228036 May 10 2025 oracle_fdw.c-rw-rw-r-- 1 postgres postgres 133 May 10 2025 oracle_fdw.control-rw-rw-r-- 1 postgres postgres 9168 May 10 2025 oracle_fdw.h-rw-rw-r-- 1 postgres postgres 44511 May 10 2025 oracle_gis.c-rw-rw-r-- 1 postgres postgres 104895 May 10 2025 oracle_utils.clrwxrwxrwx 1 postgres postgres 17 May 10 2025 README.md - README.oracle_fdw-rw-rw-r-- 1 postgres postgres 44318 May 10 2025 README.oracle_fdwdrwxrwxr-x 2 postgres postgres 98 May 10 2025 sql-rw-rw-r-- 1 postgres postgres 948 May 10 2025 TODO[postgrespg117 oracle_fdw-ORACLE_FDW_2_8_0]$ makegcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -fPIC -fvisibilityhidden -I/sdk/include -I/oci/include -I/rdbms/public -I/ -I/usr/include/oracle/12.2/client64 -I. -I./ -I/postgresql/pg18/include/server -I/postgresql/pg18/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_fdw.o oracle_fdw.cgcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -fPIC -fvisibilityhidden -I/sdk/include -I/oci/include -I/rdbms/public -I/ -I/usr/include/oracle/12.2/client64 -I. -I./ -I/postgresql/pg18/include/server -I/postgresql/pg18/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_utils.o oracle_utils.cgcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -fPIC -fvisibilityhidden -I/sdk/include -I/oci/include -I/rdbms/public -I/ -I/usr/include/oracle/12.2/client64 -I. -I./ -I/postgresql/pg18/include/server -I/postgresql/pg18/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -c -o oracle_gis.o oracle_gis.cgcc -stdgnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werrorvla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precisionstandard -O2 -fPIC -fvisibilityhidden -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/postgresql/pg18/lib -Wl,--as-needed -Wl,-rpath,/postgresql/pg18/lib,--enable-new-dtags -fvisibilityhidden -L/ -L/bin -L/lib -L/lib/amd64 -L/usr/lib/oracle/12.2/client64/lib -lclntsh[postgrespg117 oracle_fdw-ORACLE_FDW_2_8_0]$ make install/bin/mkdir -p /postgresql/pg18/lib/bin/mkdir -p /postgresql/pg18/share/extension/bin/mkdir -p /postgresql/pg18/share/extension/bin/mkdir -p /postgresql/pg18/share/doc/extension/bin/install -c -m 755 oracle_fdw.so /postgresql/pg18/lib/oracle_fdw.so/bin/install -c -m 644 .//oracle_fdw.control /postgresql/pg18/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 /postgresql/pg18/share/extension//bin/install -c -m 644 .//README.oracle_fdw /postgresql/pg18/share/doc/extension/--编译完登录数据库安装extension可能会无法识别lib重启一下pg数据库即可。[postgrespg117 oracle_fdw-ORACLE_FDW_2_8_0]$ psqlpsql (18.3)Type help for help.postgres# create extension oracle_fdw;ERROR: could not load library /postgresql/pg18/lib/oracle_fdw.so: libclntsh.so.12.1: cannot open shared object file: No such file or directoryCONTEXT: SQL statement CREATE FUNCTION oracle_fdw_handler() RETURNS fdw_handlerAS $libdir/oracle_fdwLANGUAGE C STRICTextension script file oracle_fdw--1.2.sql, near line 1postgres# \q[postgrespg117 oracle_fdw-ORACLE_FDW_2_8_0]$ pg_ctl restartwaiting for server to shut down..... doneserver stoppedwaiting for server to start....2026-05-26 09:49:00.682 CST [27552] LOG: redirecting log output to logging collector process2026-05-26 09:49:00.682 CST [27552] HINT: Future log output will appear in directory log.doneserver started重新创建extension[postgrespg117 oracle_fdw-ORACLE_FDW_2_8_0]$ psqlpsql (18.3)Type help for help.postgres# create extension oracle_fdw ;CREATE EXTENSIONpostgres# \dxList of installed extensionsName | Version | Default version | Schema | Description----------------------------------------------------------------------------------------------------------------------------------oracle_fdw | 1.2 | 1.2 | public | foreign data wrapper for Oracle accesspg_stat_statements | 1.12 | 1.12 | public | track planning and execution statistics of all SQL statements executedplpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language(3 rows)postgres# SELECT oracle_diag();oracle_diag------------------------------------------------------------------------------------------------------------oracle_fdw 2.8.0, PostgreSQL 18.3, Oracle client 12.2.0.1.0, ORACLE_HOME/usr/lib/oracle/12.2/client64/lib(1 row)3、配置fdw--创建外部服务器postgres# create server oratest_fdw foreign data wrapper oracle_fdw options(dbserver 192.192.103.111:1521/oratest);CREATE SERVER--授权使用的用户postgres# grant usage on foreign server oratest_fdw to postgres;GRANT--创建用户映射,连接oracle的凭证postgres# create user mapping for postgres server oratest_fdw options (user hr,password hr);CREATE USER MAPPING--创建外部表postgres# create foreign table fdw_t1(name varchar(100),amount numeric(12),update_time timestamp(6) server oratest_fdw options (schema HR,table T1,readonly false);CREATE FOREIGN TABLEpostgres# \dList of relationsSchema | Name | Type | Owner----------------------------------------------------------public | fdw_t1 | foreign table | postgrespublic | pg_stat_statements | view | postgrespublic | pg_stat_statements_info | view | postgres(3 rows)postgres# select * from fdw_t1;name | amount | update_time------------------------------------B | 88888 | 2026-05-11 16:53:28c | 222 | 2026-05-11 17:02:43d | 777 | 2026-05-11 17:37:02aaaaa | 99 | 2026-05-12 09:27:24(4 rows)#支持一键导入所有表、视图和物化视图postgres# IMPORT FOREIGN SCHEMA HR FROM SERVER oratest_fdw INTO public OPTIONS (case lower);IMPORT FOREIGN SCHEMApostgres# \dList of relationsSchema | Name | Type | Owner----------------------------------------------------------public | all_case_info | foreign table | postgrespublic | countries | foreign table | postgrespublic | departments | foreign table | postgrespublic | emp_details_view | foreign table | postgrespublic | employees | foreign table | postgrespublic | fdw_t1 | foreign table | postgrespublic | job_history | foreign table | postgrespublic | jobs | foreign table | postgrespublic | locations | foreign table | postgrespublic | pg_stat_statements | view | postgrespublic | pg_stat_statements_info | view | postgrespublic | regions | foreign table | postgrespublic | t1 | foreign table | postgrespublic | t11 | foreign table | postgrespublic | t_time | foreign table | postgres(15 rows)#其他运维--查看所有映射表postgres# select * from pg_foreign_table ;ftrelid | ftserver | ftoptions--------------------------------------------------------17232 | 17227 | {schemaHR,tableT1,readonlyfalse}17286 | 17227 | {schemaHR,tableALL_CASE_INFO}17290 | 17227 | {schemaHR,tableCOUNTRIES}17294 | 17227 | {schemaHR,tableDEPARTMENTS}17299 | 17227 | {schemaHR,tableEMPLOYEES}17307 | 17227 | {schemaHR,tableEMP_DETAILS_VIEW}17316 | 17227 | {schemaHR,tableJOBS}17321 | 17227 | {schemaHR,tableJOB_HISTORY}17328 | 17227 | {schemaHR,tableLOCATIONS}17333 | 17227 | {schemaHR,tableREGIONS}17337 | 17227 | {schemaHR,tableT1}17340 | 17227 | {schemaHR,tableT11}17343 | 17227 | {schemaHR,tableT_TIME}(13 rows)--查看已创建的oracle链接postgres# SELECT * from pg_user_mappings;umid | srvid | srvname | umuser | usename | umoptions--------------------------------------------------------------------17228 | 17227 | oratest_fdw | 10 | postgres | {userhr,passwordhr}(1 row)-- 删除创建的对象drop foreign table fdw_t1;drop user mapping for postgres server oratest_fdw;drop server oratest_fdw;--查看server对象postgres# SELECT * FROM pg_foreign_server;oid | srvname | srvowner | srvfdw | srvtype | srvversion | srvacl | srvoptions---------------------------------------------------------------------------------------------------------------------------17227 | oratest_fdw | 10 | 17226 | | | {postgresU/postgres} | {dbserver192.192.103.111:1521/oratest}(1 row)--修改IP或端口alter server oratest_fdw options(set dbserver 192.168.6.10:1521/oradb);--修改连接用户密码alter user mapping for postgres server ora_fdw options (set user user,set password **);--创建外部表的三种映射方式--- 简单映射CREATE FOREIGN TABLE ora_employees (emp_id integer OPTIONS (key true),emp_name varchar(100),hire_date timestamp,salary numeric(10,2)) SERVER oratest_fdw OPTIONS (schema HR, table EMPLOYEES);--- 选择性列映射CREATE FOREIGN TABLE ora_dept (dept_id integer,dept_name varchar(50)) SERVER oratest_fdw OPTIONS (schema HR,table DEPARTMENTS,only_cols dept_id, dept_name);--- 表分区映射CREATE FOREIGN TABLE ora_sales_part (sale_id integer,sale_date date,amount numeric) SERVER oratest_fdw OPTIONS (schema SH,table SALES,partition SALES_Q1_2023);