postgresql 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、Twitter、Facebook等
• 更多类型参考：Foreign data wrappers – PostgreSQL wiki

二、 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;

查看插件

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 '');

对应视图

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 ''); 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。

• 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