postgresql FDW概念、用法与原理小结

postgresql FDW概念、用法与原理小结FDW foreign datawrapper 外部数据包装器 可以让我们在 PG 中使用 SQL 查询极为丰富的外部数据 fdw

大家好,欢迎来到IT知识分享网。

最近突然遇到了一批使用fdw的场景,整理记录一把。

 

一、 强大的FDW

       FDW (foreign-data wrapper,外部数据包装器),可以让我们在PG中使用SQL查询极为丰富的外部数据:

  • 本实例和其他pg实例中的pg库
  • 主流关系型数据库:Oracle、MySQL、SQL Server等
  • NoSQL数据库:ClickHouse、MongoDB、Redis、Neo4j等
  • 外部文件:csv、josn、pg_dump、xml
  • Web文件:S3、TwitterFacebook
  • 更多类型参考:Foreign data wrappers – PostgreSQL wiki

二、 FDW四件套

postgresql FDW概念、用法与原理小结

下面的例子希望从本实例的postgres库访问clair库的layer表

首先在目标库创建一个用户,并授权它可以查询layer表

psql -dclair create user clair_r with password ''; grant select on public.layer to clair_r;

按照四部曲:

1. create extension

处理外部数据源的插件,每类数据库各有不同,需要分别安装。

创建语句

create extension postgres_fdw;
  • 使用yum安装的,需要执行yum install postgresql-contrib安装对应版本包
  • 使用源码安装的,需要在源码解压目录编译插件包,否则会有报错
clair=# create extension postgres_fdw; ERROR:  could not open extension control file "/…/base/share/extension/postgres_fdw.control": No such file or directory

解决方法

  • cd /源码安装目录/contrib/postgres_fdw,源码安装目录即./configure –prefix 指定的位置
  • make & make install
  • 不需重启,再次执行create extension postgres_fdw;

对应视图

select * from pg_foreign_data_wrapper;

postgresql FDW概念、用法与原理小结

查看插件

select * from pg_extension ; 或 \dx

postgresql FDW概念、用法与原理小结

删除语句

drop extension postgres_fdw;

2. create server

目标库连接串,要访问哪个ip、端口、db名

创建语句

CREATE SERVER clair_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.83.123.89', port '5432', dbname 'clair');

对应视图

select * from pg_foreign_server; 或 \des

postgresql FDW概念、用法与原理小结

删除语句

drop SERVER foreign_server;

3. create user mapping

用户映射:目标库使用哪个用户、密码,可以单独创建一个用户也可以用现有的

CREATE USER MAPPING FOR postgres SERVER clair_server OPTIONS (user 'clair_r', password '');

对应视图

select * from pg_user_mappings; 或 \deu+

postgresql FDW概念、用法与原理小结

删除语句

DROP USER MAPPING for user_name SERVER server_name;

4. create foreign table

本地外部表对应目标库哪张表或视图,外部表字段可以少于目标表和视图,只取自己需要的

CREATE FOREIGN TABLE fdw_layer( id integer NOT NULL, name character varying(128) ) SERVER clair_server OPTIONS (schema_name 'public', table_name 'layer');

对应视图 

select * from pg_foreign_table;

postgresql FDW概念、用法与原理小结

删除语句

drop FOREIGN TABLE foreign_table;
pg 11开始,也可以用下面语句导入表定义
IMPORT FOREIGN SCHEMA foreign_films FROM SERVER film_server INTO films;
也可以只导入部分字段
IMPORT FOREIGN SCHEMA foreign_films LIMIT TO (actors, directors) FROM SERVER film_server INTO films;

5. 查询外部表

      pg 14中,如果外部用户有权限,现在可以对外部表执行INSERTUPDATEDELETECOPY以及 TRUNCATE操作。对于insert,目前不支持ON CONFLICT DO UPDATE子句,但支持了CONFLICT DO NOTHING子句。

\d fdw_layer select * from fdw_layer;

postgresql FDW概念、用法与原理小结

postgresql FDW概念、用法与原理小结

6. 其他管理函数

  • 返回​​​​​​​postgres_fdw建立的外部连接及状态
SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
  • 断开指定连接
SELECT postgres_fdw_disconnect('loopback1');
  • 断开所有连接
​​​​​​​SELECT postgres_fdw_disconnect_all();

三、 其他常见fdw用法

1. mysql_fdw

按照四部曲

create extension mysql_fdw; CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.83.123.89', port '3306', dbname 'mydb'); CREATE USER MAPPING FOR pg_rw SERVER mysql_server OPTIONS (user 'mydb_r', password ''); CREATE FOREIGN TABLE fdw_mysql_mytab ( id int, name character varying(128) ) SERVER mysql_server OPTIONS ( dbname 'mydb', table_name 'mytab' );

查询外部表

psql -Upg_rw select count(*) from fdw_mysql_mytab;

2. file_fdw

将csv格式的pg错误日志创建为外部表。file_fdw不需要用户映射,因此只有3步

create extension file_fdw; CREATE SERVER log_server FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE postgres_log ( log_time timestamp, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint ) SERVER log_server OPTIONS (format 'csv', header 'false', filename '/data/postgresql-01.csv', delimiter ',', null'');

四、 fdw原理

以下内容来自:https://oyo-byte.github.io/2018/08/03/learn_about_pgfdw/

       实现FDW的核心是实现一组回调函数,其中最核心有7个。无论外部数据源自身能力如何, 这7个接口是实现通过外部表对象访问该数据源的必须接口,定义都位于fdwapi.h。

回调函数

PG中的调用时机

作用

GetForeignRelSize

优化器生成访问路径的过程中对外部表估算访问代价时

提供外部表对于计算访问代价所需的基础数据,如表的元组数以及元组的平均长度,并将这些数据保存在输入参数baserel的字段”rows”以及”width”中

GetForeignPaths

生成对外部表访问路径时

生成对目标外部表的访问路径(通过PG中的接口createforeignscanpath()生成)

GetForeignPlan

优化器生成扫描外部表的查询计划节点时

生成访问目标外部表的ForeignScan计划节点(通过PG中的接口make_foreignscan())

BeginForeignScan

执行器即将开始执行ForeignScan算子,进行该算子相关的初始化时

获取执行ForeignScan算子所需的信息,并将它们组织并保存在ForeignScanState中

IterateForeignScan

执行ForeignScan算子过程中需要获取下一元组时

读取外部数据源的一行数据,并将它组织为PG中的Tuple(即TupleTableSlot). 当该回调函数返回一个空的TupleTableSlot结构时, 迭代器停止迭代

ReScanForeignScan

执行Nested Loop过程中需要重置Inner Scan时(即Outter Scan需要向前推进一行时)

将外部数据源的读取位置重置回最初的起始位置

EndForeignScan

ForeignScan算子执行完成时

释放整个ForeignScan算子执行过程中占用的外部资源或FDW中的资源

postgresql FDW概念、用法与原理小结

  • Parser: 包含对SQL的语法解析,语义校验,查询重写
  • Optimizer:生成查询计划
  • Executor:按照火山模型执行查询计划的算子并向上返回数据

https://i-blog.csdnimg.cn/blog_migrate/18f70138aec399bffa1abbb7b16a4820.png

参考

PostgreSQL Insider – How to link to Oracle databases using oracle_fdw (part 1)

http://v0.pigsty.cc/pdf/fdw-pgconf-2019.pdf

学习PostgreSQL的FDW(#1) | oYo-Byte

https://oyo-byte.github.io/2018/10/07/how_to_write_a_pg_fdw/

PostgreSQL: Documentation: 14: F.35. postgres_fdw

https://www.postgresql.org/docs/14/sql-importforeignschema.html

PostgreSQL: Documentation: 14: F.14. file_fdw

免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://haidsoft.com/131762.html

(0)
上一篇 2025-08-05 18:20
下一篇 2025-08-05 18:26

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注微信