最近突然遇到了一批使用fdw的场景，整理记录一把。
一、 强大的FDW
FDW (foreign-data wrapper，外部数据包装器)，可以让我们在PG中使用SQL查询极为丰富的外部数据：
本实例和其他pg实例中的pg库主流关系型数据库：Oracle、MySQL、SQL Server等NoSQL数据库：ClickHouse、MongoDB、Redis、Neo4j等外部文件：csv、josn、pg_dump、xmlWeb文件：S3、Twitter、Facebook等更多类型参考：Foreign data wrappers - PostgreSQL wiki
二、 FDW四件套
下面的例子希望从本实例的postgres库访问clair库的layer表
首先在目标库创建一个用户，并授权它可以查询layer表
psql -dclair
create user clair_r with password '123456';
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;
查看插件
select * from pg_extension ;
或
\dx
删除语句
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
删除语句
drop SERVER foreign_server;
3. create user mapping
用户映射：目标库使用哪个用户、密码，可以单独创建一个用户也可以用现有的
CREATE USER MAPPING FOR postgres
SERVER clair_server
OPTIONS (user 'clair_r', password '123456');
对应视图
select * from pg_user_mappings;
或
\deu+
删除语句
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;
删除语句
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中，如果外部用户有权限，现在可以对外部表执行INSERT, UPDATE, DELETE, COPY以及 TRUNCATE操作。对于insert，目前不支持ON CONFLICT DO UPDATE子句，但支持了CONFLICT DO NOTHING子句。
\d fdw_layer
select * from fdw_layer;
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 '123456');
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中的资源
Parser: 包含对SQL的语法解析，语义校验，查询重写Optimizer：生成查询计划Executor：按照火山模型执行查询计划的算子并向上返回数据
参考
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
发表评论