云贝教育 |【技术文章】POSTGRESQL FDW应用

Wrapper（FDW）是一项关键特性，它赋予数据库用户直接通过SQL语句访问存储于外部数据源的能力。遵循SQL/MED标准设计，FDW使PostgreSQL能够无缝对接多种异构数据库系统以及非数据库类数据源。

FDW机制由四个核心组件构成：

ForeignDataWrapper：特定于各数据源的库，定义了如何建立与外部数据源的连接、执行查询及处理其他操作。例如，“postgres_fdw”用于连接其他PostgreSQL服务器，“mysql_fdw”则专门连接MySQL数据库。

ForeignServer：在本地PostgreSQL中定义一个外部服务器对象，对应实际的远程或非本地数据存储实例。

UserMapping：为每个外部服务器设置用户映射，明确哪些本地用户有权访问，并提供相应的认证信息，如用户名和密码。

ForeignTable：在本地数据库创建表结构，作为外部数据源中表的映射。对这些“外表”发起的SQL查询将被转换并传递给相应的FDW，在外部数据源上执行。

随着FDW生态的日益繁荣，各类针对不同数据源的FDW插件层出不穷，如postgres_fdw实现PostgreSQL间的互连互通，mysql_fdw、oracle_fdw分别用于连接MySQL和Oracle数据库，还有file_fdw可用于读取文本文件或CSV等非结构化数据。

接下来，我们重点介绍FDW功能使用。

使用PostgreSQL的file_fdw扩展，可以创建一个外部表来读取和查询文件系统中的文本或CSV文件。以下是如何通过file_fdw实现外部表的详细步骤：

使用场景：当有个1G表数据，CSV格式，需要和本地表运算出报表结果

1.1准备csv数据

createtabletestpc(oidint,relnametext);insertintotestpcselectoid,relnamefrompg_classlimit100;COPY(SELECT*FROMtestpc)TO'/tmp/'WITHCSVDELIMITER',';

1.2创建外部表

1、--添加扩展CREATEEXTENSIONfile_fdw;2、--创建SERVERFORfilecreateserverpg_file_serverforeigndatawrapperfile_fdw;3、--创建外部表，与外部文件结构一致createforeigntabletestpc_file_fdw(oidint,relnametext)serverpg_file_serveroptions(filename'/tmp/',format'csv',header'true',delimiter',');

1.3与本地表进行运算

postgres=\des+ListofforeignserversName|Owner|Foreign-datawrapper|Accessprivileges|Type|Version|FDWoptions|Description----------+----------+----------------------+-------------------+------+---------+--------------------------------------------------------+-------------pgserver|postgres|postgres_fdw||||(host'192.168.2.104',dbname'postgres',port'5666')|(1row)

删除外部服务

ropSERVERpgservercascade;

2.3将外部服务授权给指定用户

grantusageonFOREIGNSERVERpgservertopostgres;

2.4创建外部服务的mapping

用于连接指向用户及指定数据库

CREATEUSERMAPPINGFORpostgresSERVERpgserverOPTIONS(user'postgres',password'postgres');

查看mapping关系

select*frominformation__mappings;authorization_identifier|foreign_server_catalog|foreign_server_name--------------------------+------------------------+---------------------postgres|postgres|pgserver(1row)postgres=createtabletest1(idint);CREATETABLEpostgres=select*fromtest1;id----1(1row)postgres=\detListofforeigntablesSchema|Table|Server--------+----------+----------public|localt1|pgserverpublic|localt11|pgserver(2rows)

删除外部表

dropFOREIGNtablelocalt11;

2.7访问自身集群中的pg库

实现过程和第二章节一样

实现目标：通过postgres数据库中的postgres用户访问appdb中appuser在schema:appuser中的表t1

\cpostgrespostgrespostgres=CREATEUSERMAPPINGFORpostgresSERVERappdbserverOPTIONS(user'appuser',password'1qaz@WSX');CREATEUSERMAPPING\cappdbappuserappdb=select*fromt1;id----1234(4rows)\cpostgrespostgresCREATEFOREIGNTABLElt2(idint)SERVERappdbserverOPTIONS(schema_name'appuser',table_name't1');postgres=cdoracle_fdw-master/makemakeinstall

3.6创建oracle_fdw扩展

[pg14@pg01~]$psqlpostgrespostgrespsql(13.8)Type"help"=CREATEEXTENSIONoracle_fdw;ERROR:couldnotloadlibrary"/pg14/soft/lib/postgresql/oracle_"::cannotopensharedobjectfile:Nosuchfileordirectory

先通过ldd命令跟踪库文件的调用过程，比如

[pg14@pg01~]$ldd/pg14/soft/lib/postgresql/oracle_=(0x00007ffefaf42000)=/pg14/soft/lib/(0x00007fdbd3063000)=/lib64/(0x00007fdbd2c95000)=/pg14/soft/lib/(0x00007fdbd254d000)=/lib64/(0x00007fdbd2349000)=/lib64/(0x00007fdbd2047000)=/lib64/(0x00007fdbd1e2b000)=/lib64/(0x00007fdbd1c11000)=/lib64/(0x00007fdbd1a09000)=/lib64/(0x00007fdbd1807000)=/lib64/(0x00007fdbd15ed000)/lib64/(0x00007fdbd7295000)==notfound

而这个包在oracle客户端的lib目录下，可以通过以下方式尝试解决：

1、通过软链接

示例ln-s/root/oracle/lib//pg14/soft/lib/

2、直接cp

示例/pg14/soft/lib/chownpg14:pg14/pg14/soft/lib/

3.7创建对oracle的外部表

postgres=grantusageonforeignserveroradb_testtopostgres;postgres=创建外部表postgres=select*fromorat1limit10;

随着时间推移，FDW的功能不断丰富和优化，包括增强性能、增加更多类型的FDW以及改善用户体验。如今，FDW已经成为PostgreSQL生态不可或缺的一部分，大大提高了其数据集成能力。

展望未来，FDW将继续成为实现跨多种数据库和非传统数据源交互的关键技术，尤其是在大数据时代背景下，对于构建复杂的企业级数据湖和数据仓库解决方案具有深远意义。同时，随着云原生和分布式计算范式的兴起，FDW也将顺应潮流，持续演进以适应更加多样化且充满挑战性的应用场景。