postgresql oracle_fdw访问oracle数据
1、环境准备
--下载安装包：
https://github.com/laurenz/oracle_fdw/releases
--下载安装oracle client：
oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
oracle-instantclient12.2-odbc-12.2.0.1.0-2.x86_64.rpm
oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm
--编辑pg环境变量，添加oracle lib变量
postgres@pg117 ~]$ cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
export PGHOME=/postgresql/pg18
export PGDATA=/postgresql/pgdata
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib:/usr/lib/oracle/12.2/client64/lib:$LD_LIBRARY_PATH
2、编译安装oracle_fdw
[postgres@pg117 soft]$ tar zxvf oracle_fdw-ORACLE_FDW_2_8_0.tar.gz
oracle_fdw-ORACLE_FDW_2_8_0/
oracle_fdw-ORACLE_FDW_2_8_0/.gitignore
oracle_fdw-ORACLE_FDW_2_8_0/CHANGELOG
oracle_fdw-ORACLE_FDW_2_8_0/LICENSE
oracle_fdw-ORACLE_FDW_2_8_0/Makefile
oracle_fdw-ORACLE_FDW_2_8_0/README.md
oracle_fdw-ORACLE_FDW_2_8_0/README.oracle_fdw
oracle_fdw-ORACLE_FDW_2_8_0/TODO
oracle_fdw-ORACLE_FDW_2_8_0/expected/
oracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_fdw.out
oracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_gis.out
oracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_import.out
oracle_fdw-ORACLE_FDW_2_8_0/expected/oracle_join.out
oracle_fdw-ORACLE_FDW_2_8_0/msvc/
oracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw.props
oracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw.sln
oracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_fdw.vcxproj
oracle_fdw-ORACLE_FDW_2_8_0/msvc/oracle_msvc.c
oracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw--1.0--1.1.sql
oracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw--1.1--1.2.sql
oracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw--1.2.sql
oracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw.c
oracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw.control
oracle_fdw-ORACLE_FDW_2_8_0/oracle_fdw.h
oracle_fdw-ORACLE_FDW_2_8_0/oracle_gis.c
oracle_fdw-ORACLE_FDW_2_8_0/oracle_utils.c
oracle_fdw-ORACLE_FDW_2_8_0/sql/
oracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_fdw.sql
oracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_gis.sql
oracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_import.sql
oracle_fdw-ORACLE_FDW_2_8_0/sql/oracle_join.sql
[postgres@pg117 soft]$ cd oracle_fdw-ORACLE_FDW_2_8_0/
[postgres@pg117 oracle_fdw-ORACLE_FDW_2_8_0]$ ll
total 488
-rw-rw-r-- 1 postgres postgres 29202 May 10 2025 CHANGELOG
drwxrwxr-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 Makefile
drwxrwxr-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.c
lrwxrwxrwx 1 postgres postgres 17 May 10 2025 README.md -> README.oracle_fdw
-rw-rw-r-- 1 postgres postgres 44318 May 10 2025 README.oracle_fdw
drwxrwxr-x 2 postgres postgres 98 May 10 2025 sql
-rw-rw-r-- 1 postgres postgres 948 May 10 2025 TODO
[postgres@pg117 oracle_fdw-ORACLE_FDW_2_8_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 -fvisibility=hidden -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.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 -fvisibility=hidden -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.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 -fvisibility=hidden -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.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 -fvisibility=hidden -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 -fvisibility=hidden -L"/" -L"/bin" -L"/lib" -L"/lib/amd64" -L/usr/lib/oracle/12.2/client64/lib -lclntsh
[postgres@pg117 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数据库即可。
[postgres@pg117 oracle_fdw-ORACLE_FDW_2_8_0]$ psql
psql (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 directory
CONTEXT: SQL statement "CREATE FUNCTION oracle_fdw_handler() RETURNS fdw_handler
AS '$libdir/oracle_fdw'
LANGUAGE C STRICT"
extension script file "oracle_fdw--1.2.sql", near line 1
postgres=# \q
[postgres@pg117 oracle_fdw-ORACLE_FDW_2_8_0]$ pg_ctl restart
waiting for server to shut down..... done
server stopped
waiting for server to start....2026-05-26 09:49:00.682 CST [27552] LOG: redirecting log output to logging collector process
2026-05-26 09:49:00.682 CST [27552] HINT: Future log output will appear in directory "log".
done
server started
重新创建extension
[postgres@pg117 oracle_fdw-ORACLE_FDW_2_8_0]$ psql
psql (18.3)
Type "help" for help.
postgres=# create extension oracle_fdw ;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Default version | Schema | Description
--------------------+---------+-----------------+------------+------------------------------------------------------------------------
oracle_fdw | 1.2 | 1.2 | public | foreign data wrapper for Oracle access
pg_stat_statements | 1.12 | 1.12 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 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 TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+---------------+----------
public | fdw_t1 | foreign table | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
(3 rows)
postgres=# select * from fdw_t1;
name | amount | update_time
-------+--------+---------------------
B | 88888 | 2026-05-11 16:53:28
c | 222 | 2026-05-11 17:02:43
d | 777 | 2026-05-11 17:37:02
aaaaa | 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 SCHEMA
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+---------------+----------
public | all_case_info | foreign table | postgres
public | countries | foreign table | postgres
public | departments | foreign table | postgres
public | emp_details_view | foreign table | postgres
public | employees | foreign table | postgres
public | fdw_t1 | foreign table | postgres
public | job_history | foreign table | postgres
public | jobs | foreign table | postgres
public | locations | foreign table | postgres
public | pg_stat_statements | view | postgres
public | pg_stat_statements_info | view | postgres
public | regions | foreign table | postgres
public | t1 | foreign table | postgres
public | t11 | foreign table | postgres
public | t_time | foreign table | postgres
(15 rows)
#其他运维
--查看所有映射表
postgres=# select * from pg_foreign_table ;
ftrelid | ftserver | ftoptions
---------+----------+-------------------------------------
17232 | 17227 | {schema=HR,table=T1,readonly=false}
17286 | 17227 | {schema=HR,table=ALL_CASE_INFO}
17290 | 17227 | {schema=HR,table=COUNTRIES}
17294 | 17227 | {schema=HR,table=DEPARTMENTS}
17299 | 17227 | {schema=HR,table=EMPLOYEES}
17307 | 17227 | {schema=HR,table=EMP_DETAILS_VIEW}
17316 | 17227 | {schema=HR,table=JOBS}
17321 | 17227 | {schema=HR,table=JOB_HISTORY}
17328 | 17227 | {schema=HR,table=LOCATIONS}
17333 | 17227 | {schema=HR,table=REGIONS}
17337 | 17227 | {schema=HR,table=T1}
17340 | 17227 | {schema=HR,table=T11}
17343 | 17227 | {schema=HR,table=T_TIME}
(13 rows)
--查看已创建的oracle链接
postgres=# SELECT * from pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+-------------+--------+----------+-----------------------
17228 | 17227 | oratest_fdw | 10 | postgres | {user=hr,password=hr}
(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 | | | {postgres=U/postgres} | {dbserver=192.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'
);
汇聚全球AI编程工具，助力开发者即刻编程。
更多推荐
10
0- 0
已为社区贡献6条内容
所有评论(0)