概要：本文解释了如何使用外部数据包装器访问远程 PostgreSQL 和 Oracle 数据库中的数据。
1. postgres_fdw
2. oracle_fdw
有许多方法可以访问远程表数据——存在于远程数据库中现有表中的数据——例如 PostgreSQL 的内置 dblink 模块。最流行的方法之一是在 PostgreSQL 外部数据包装器 (postgres_fdw) 的帮助下访问表。除了 postgres_fdw 之外，还有其他外部数据包装器，例如 mongo_fdw、hadoop_fdw 和 mysql_fdw，它们可分别用于访问 MongoDB 数据库、Hadoop 分布式文件系统和 MySQL 数据库中存在的数据。
postgres_fdw
使用postgres_fdw远程访问外部PostgreSQL服务器中的数据，请参考以下步骤：
第 1 步：创建扩展。
edb=# create extension postgres_fdw ;
CREATE EXTENSION
edb=#
第 2 步： 为用户要连接的每个远程数据库创建一个外部服务器。请确保远程数据库集群具有与必须为其创建外部服务器的数据库服务器相对应的 pg_hba.conf 条目。
edb=# CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xx.xx.xx.xx', dbname 'postgres', port '5444');
CREATE SERVER
注意：这里的 xx.xx.xx.xx是PostgreSQL数据库服务器的远程机器IP地址。
第 3 步：为可以访问外部服务器上的远程对象的特定用户创建用户映射。
例如：这里我们使用用户“enterprisedb”来访问外部对象。
edb=# CREATE USER MAPPING FOR enterprisedb SERVER myserver OPTIONS (user 'enterprisedb', password 'edb');
CREATE USER MAPPING
这个例子最初可能会让一些用户感到困惑，因为用户名和密码必须在小括号 () 内指示。它是用户映射语法的一部分：
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password of foreign_user');
要了解有关用户映射的更多信息，请参阅 PostgreSQL 文档：
https://www.postgresql.org/docs/11/sql-createusermapping.html。
第 4 步：既然外部数据包装器设置已经完成，我们就可以继续访问远程表了。
表“test”是远程数据库中存在的远程表（主机为“xx.xx.xx.xx”，数据库名称为“edb”，端口为“5444”，如上文第 2 步所述）：
REMOTE_TABLE:
==============
edb=# create table test(id int,name text);
CREATE TABLE
edb=# insert into test values (1,'piyushj');
INSERT 0 1
在本地数据库服务器中创建外表“test_foreign”。
edb=#
edb=# CREATE FOREIGN TABLE test_foreign ( id int, name text) SERVER myserver;
CREATE FOREIGN TABLE
edb=#
要了解有关 CREATE FOREIGN TABLE 语法的更多信息，请参阅 PostgreSQL 文档： https: //www.postgresql.org/docs/11/sql-createforeigntable.html。
第五步：我们可以查询外部表“test_foreign”来访问远程表test。
edb=# select * from test_foreign;
id | name
----+-------
1 | piyush
(1 row)
oracle_fdw
我们在上面讨论了访问远程 PostgreSQL 数据库中的表的步骤。要从本地 PostgreSQL 数据库访问远程 Oracle 数据库服务器中的表，我们可以使用 oracle_fdw 外部数据包装器，它包含在外部数据标准的 SQL 管理中。
让我们讨论构建和安装 oracle_fdw 包装器然后配置它以访问 Oracle 表的步骤。
第一步： 下载安装oracle_fdw的源码。
您可以从链接http://laurenz.github.io/oracle_fdw/下载 oracle_fdw 代码。
第 2 步：安装的先决条件。
本地环境需要安装PostgreSQL，用户至少需要安装Oracle instantclient-basic、instantclient-devel。注意：如果 Oracle 服务器和 PostgreSQL 服务器运行在同一台机器上，则不需要安装即时客户端——也就是说，您可以跳过这一步。
例子
[root@piyush Downloads]# yum localinstall oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Examining oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm: oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64
Marking oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm to be installed
Examining oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm: oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64
Marking oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package oracle-instantclient12.1-basic.x86_64 0:12.1.0.2.0-1 will be installed
---> Package oracle-instantclient12.1-devel.x86_64 0:12.1.0.2.0-1 will be installed
--> Finished Dependency Resolution
:
:
:
Dependencies Resolved
=========================================================================================================================================================================================
Package Arch Version Repository Size
=========================================================================================================================================================================================
Installing:
oracle-instantclient12.1-basic x86_64 12.1.0.2.0-1 /oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64 185 M
oracle-instantclient12.1-devel x86_64 12.1.0.2.0-1 /oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64 1.9 M
Transaction Summary
=========================================================================================================================================================================================
Install 2 Packages
Total size: 187 M
Installed size: 187 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64 1/2
Installing : oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64 2/2
Verifying : oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64 1/2
Verifying : oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64 2/2
Installed:
oracle-instantclient12.1-basic.x86_64 0:12.1.0.2.0-1 oracle-instantclient12.1-devel.x86_64 0:12.1.0.2.0-1
Complete!
第 3 步： 确保 pg_config 在您的路径中，并且配置了 ORACLE_HOME 和 LD_LIBRARY_PATH。
which pg_config
/opt/PostgreSQL/10/bin/pg_config . ------ We are using the PG-10 installation
-bash-4.2$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
-bash-4.2$
-bash-4.2$ export ORACLE_HOME=/usr/include/oracle/12.1/client64
-bash-4.2$ echo $ORACLE_HOME
/usr/include/oracle/12.1/client64
-bash-4.2$ echo $LD_LIBRARY_PATH
/usr/lib/oracle/12.1/client64/lib
我们下载了oracle_fdw-2.2.0版本进行安装。
cd /opt/oracle_fdw-2.2.0/
我们正在使用make和make install命令来安装 oracle_fdw。命令make 用于构建软件。这将运行Makefile中定义的一系列任务，以从其源代码构建完成的程序。make install命令会将构建的程序及其库和文档复制到正确的位置。
这通常意味着该程序的二进制文件将被复制到您的 PATH 上的一个目录，该程序的手册页将被复制到您的 MANPATH 上的一个目录，并且它所依赖的任何其他文件将被安全地存储在适当的位置。
bash-4.2$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/usr/include/oracle/12.1/client64/sdk/include -I/usr/include/oracle/12.1/client64/oci/include -I/usr/include/oracle/12.1/client64/rdbms/public -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/opt/PostgreSQL/10include/server -I/opt/PostgreSQL/10/include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/usr/include/oracle/12.1/client64/sdk/include -I/usr/include/oracle/12.1/client64/oci/include -I/usr/include/oracle/12.1/client64/rdbms/public -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/opt/PostgreSQL/10/include/server -I/opt/PostgreSQL/10//include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/usr/include/oracle/12.1/client64/sdk/include -I/usr/include/oracle/12.1/client64/oci/include -I/usr/include/oracle/12.1/client64/rdbms/public -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/opt/PostgreSQL/10/include/server -I/opt/PostgreSQL/10include/internal -I/opt/local/Current/include -D_GNU_SOURCE -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include/libxml2 -I/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/include -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include -I/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/include -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -DLINUX_OOM_ADJ=0 -O2 -DMAP_HUGETLB=0x40000 -fPIC -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/opt/PostgreSQL/10lib -L/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/lib -L/opt/local/20160428/649c6f94-f2c0-4703-b065-99d58ae4acc6/lib -L/opt/local/Current/lib -L/mnt/hgfs/edb-postgres.auto/server/source/libmm-edb.linux-x64/inst/lib -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/10/lib',--enable-new-dtags -L/usr/include/oracle/12.1/client64 -L/usr/include/oracle/12.1/client64/bin -L/usr/include/oracle/12.1/client64/lib -L/usr/include/oracle/12.1/client64/lib/amd64 -lclntsh -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
-bash-4.2$ make install
/bin/mkdir -p '/opt/PostgreSQL/10/lib'
/bin/mkdir -p '/opt/PostgreSQL/10/share/postgresql/extension'
/bin/mkdir -p '/opt/PostgreSQL/10/share/postgresql/extension'
/bin/mkdir -p '/opt/PostgreSQL/10/doc/postgresql/extension'
/usr/bin/install -c -m 755 oracle_fdw.so '/opt/PostgreSQL/10/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/opt/PostgreSQL/10/share/postgresql/extension'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql '/opt/PostgreSQL/10/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/opt/PostgreSQL/10/doc/postgresql/extension'
第 4 步： 创建扩展、服务器和用户映射。
创建扩展：
-bash-4.2$ ./psql -p 5490 -d postgres
Password:
psql.bin (10.7.15)
Type "help" for help.
edb=# create extension oracle_fdw;
CREATE EXTENSION
正如我们在 postgres_fdw 中定义了外部服务器和用户映射，这里我们还必须定义外部服务器和用户映射。
创建外部服务器：
edb=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//xx.xx.xx.xx/orcl' );
CREATE SERVER
注意：这里的xx.xx.xx.xx是运行Oracle的oracle_server机器IP地址。
句法
create server <server_name> foreign data wrapper oracle_fdw options (dbserver '//<oracle_servefr_IP>/<sid>' );
创建用户映射：
edb=# create user mapping for enterprisedb server oracle options (user 'system', password 'manager');
CREATE USER MAPPING句法
create user mapping for <local_postgres_user> server oracle options (user '<oracle_user>', password '<oracle_passwd>');
第 5 步：从要从 PostgreSQL 访问的 Oracle 数据库中选择一个表。
SQL> desc datawrap
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
NAME VARCHAR2(50)
SQL> select * from datawrap;
ID NAME
---------- --------------------------------------------------
1 piyush
2 sharma
第 6 步：在 PostgreSQL 中创建外表并访问数据。
db=# create foreign table datawrap_test2 (id int, name varchar(50)) server oracle options (schema 'SYSTEM', table 'DATAWRAP');
CREATE FOREIGN TABLE
注意： 我们必须提供大写的 Oracle 架构名称和表名称
edb=# select * from datawrap_test2;
id | name
----+--------
1 | piyush
2 | sharma
(2 rows)
我们可以看到 Oracle 表数据可以很容易地从 PostgreSQL 数据库访问。您还可以通过在外部表中本地更新、删除和插入数据来更新、删除和插入远程 Oracle 表中的数据。
例子
Oracle数据库中的数据：
SQL> select * from datawrap;
ID NAME
---------- --------------------------------------------------
1 piyush
2 sharma
PostgreSQL外表中的数据：
postgres=# select * from datawrap_test2;
id | name
----+--------
1 | piyush
2 | sharma
(2 rows)
我们将本地数据插入到 PostgreSQL 外部表中，该数据将插入到远程 Oracle 表中。
在 PostgreSQL 外表中插入数据：
postgres=# insert into public.datawrap_test2 values(3,'dba');
INSERT 0 1
postgres=#
postgres=#
postgres=# select * from datawrap_test2;
id | name
----+--------
1 | piyush
2 | sharma
3 | dba
(3 rows)
验证远程 Oracle 表中的行插入：
SQL> select * from datawrap;
ID NAME
---------- --------------------------------------------------
1 piyush
2 sharma
3 dba
以类似的方式，我们也可以删除和更新数据。但是，要使用 UPDATE 或 DELETE，至少一个外部表列必须被标记为主键列。
原文标题：Using Foreign Data Wrappers to access remote PostgreSQL and Oracle databases
原文链接：https://www.enterprisedb.com/postgres-tutorials/using-foreign-data-wrappers-access-remote-postgresql-and-oracle-databases
最后修改时间：2023-02-15 10:41:19
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
