📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前服务于工业互联网
擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，
备份恢复，安装迁移，性能优化、故障应急处理等。
📣📣📣
文章目录
- 📣 1.什么是FDW？
- 📣 2.FDW工作流
- 📣 3.file_fdw
- ✨ 3.1 安装插件
- ✨ 3.2 生成file_fdw服务器
- ✨ 3.3 创建外部表
- 📣 4.postgres_fdw
- ✨ 4.1 创建安装插件
- ✨ 4.2 权限配置
- ✨ 4.3 创建外部服务
- ✨ 4.4 创建映射用户
- 4.5 创建外部表
- ✨ 4.5 其它特性
- 📣 5.总结
FDW是外部数据包装器,基于PG有这个功能，如果想要数据融合，很容易在PG上实现，只要开发对应的FDW功能即可.
📣 1.什么是FDW？
FDW (foreign-data wrapper，外部数据包装器)，PostgreSQL FDW 是一种外部访问接口，它可以被用来访问存储在外部的数据，这些数据可以是外部的pg数据库，也可以oracle、mysql等数据库，甚至可以是文件。
Specific SQL Database Wrappers
基本上每种数据库、数据类型都有各自的fdw插件，访问oracle库有oracle_fdw，
访问mysql库有mysql_fdw等等。fdw插件可以直接安装或者下载安装
可以让我们在PostgreSQL 中使用SQL查询极为丰富的外部数据：
主流关系型数据库：Oracle、MySQL、SQL Server等
NoSQL数据库：ClickHouse、MongoDB、Redis、Neo4j等
外部文件：csv、josn、pg_dump、xml
Web文件：S3、Twitter、Facebook…
📣 2.FDW工作流
1.词法分析器为输入的SQL语句创建查询树
2.SQL的执行器通过网络连接到源端的数据库服务器
3.如果启用了use_remote_estimate选项，执行器会执行explain命令评估执行计划
4.执行器根据最优的执行计划，重新创建SQL语句
📣 3.file_fdw
file_fdw插件为PG数据库提供了访问外部数据的能力，比如我们常见的文件csv,log等，该插件是内置在PG源码的contrib中。
✨ 3.1 安装插件
cd /pgccc/soft/postgresql-15.6/contrib
–编译安装
[root@centos79 contrib]# cd file_fdw
[root@centos79 file_fdw]# make install
–再次确认插件
[root@centos79 ~]# cd /pgccc/pgsql-15/share/extension
–安装插件
postgres=# CREATE EXTENSION file_fdw;
✨ 3.2 生成file_fdw服务器
–先生成一个file_fdw服务器。
CREATE SERVER file_fdw_server FOREIGN DATA WRAPPER file_fdw;
✨ 3.3 创建外部表
-- 创建一个名为"example"的新表
CREATE TABLE example (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
);
-- 插入数据到"example"表中
INSERT INTO example (name, age) VALUES
('Alice', 25),
('Bob', 30),
('Charlie', 35);
-- 查询"example"表中的所有数据
SELECT * FROM example;
--导出到文件
\copy example to '/home/postgres/example.csv' with csv header delimiter ','
--创建一个外部数据表
CREATE FOREIGN TABLE csvTable (
id SERIAL,
name VARCHAR(100),
age INT
) SERVER file_fdw_server
OPTIONS (format 'csv', header 'true', filename '/home/postgres/example.csv', delimiter ',', null'');
csv文件放在了根目录下面。
csv里面有4列，从左往右命令。还可以设置有没有列名选择
header 'true' 有列名，csv的第一行不作为表的数据 header 'false' 没有列名，csv的第一行也作为表的数据
–再次查询，完全和一般的表类似
postgres=# select * from csvTable;
📣 4.postgres_fdw
通过 postgres_fdw访问远程PostgreSQL数据库表。
步骤如下：
在源端（本地库）创建 postgres_fdw 插件
创建 foreign server 外部服务(即：指连接外部数据源的连接信息)
创建映射用户（映射用户指定了访问外部表的本地用户和远程用户信息）
创建外部表（外部表的表定义建议和远端表结构一致）
✨ 4.1 创建安装插件
本地库做如下的操作：
–编译安装
[postgres@centos79 ~]$ cd /pgccc/soft/postgresql-15.6/contrib
[postgres@centos79 contrib]$ cd postgres_fdw
[postgres@centos79 postgres_fdw]$ make install
–再次确认插件
[root@centos79 ~]# cd /pgccc/pgsql-15/share/extension
–postgres 超级用户登录 PostgreSQL
[postgres@centos79 ~]$ psql
postgres=# CREATE EXTENSION postgres_fdw;
postgres=# \dx
✨ 4.2 权限配置
本地库做如下的操作：
若使用超级用户使用postgres_fdw可以跳过
普通用户使用postgres_fdw需要单独授权
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO pgtest;
✨ 4.3 创建外部服务
本地库做如下的操作：
外部服务定义了远端PostgreSQL数据库的IP、端口、数据库连接信息
CREATE SERVER fdwpg2 FOREIGN DATA WRAPPER
postgres_fdw OPTIONS (host ‘pg2host’, port ‘5432’, dbname ‘devdb’);
✨ 4.4 创建映射用户
本地库做如下的操作：
映射用户指定了连接源端 PostgreSQL 数据库的用户名和密码信息
CREATE USER MAPPING FOR pgtest
SERVER fdwpg2 OPTIONS (user ‘pg2user’, password ‘pg2user’);
FOR:配置的用户为本地的数据库用户
OPTIONS ：配置的是远端PostgreSQL数据库的用户和密码
4.5 创建外部表
--远端数据库创建测试表
CREATE TABLE pg2_fdw (id int4 ,info text) ;
INSERT INTO pg2_fdw (id , info ) VALUES (1, 'a'),(2, 'b');
---创建外部表(本地库)
CREATE FOREIGN TABLE ft_fdw (
id int4,
info text
) SERVER fdwpg2 OPTIONS (schema_name 'pg2user', table_name 'pg2_fdw');
通过外部表访问远端数据表
注意：远端数据库pg_hba.conf文件需要允许本地库访问策略
select * from pg2_fdw;
✨ 4.5 其它特性
支持可写特性
从PostgreSQL 9.3 版本开始支持postgres_fdw 外部表可写的条件
创建映射用户时配置的远端数据库用户需要对远端表具有写权限
PostgreSQL 必须是 9.3及以上版本。
聚合函数下推
PostgreSQL 10 版本在 postgres_fdw 扩展模块中新增了一个可以将聚合、
关联操作下推到远端 PostgreSQL 数据库进行的特性。
它大幅度减少了从远程库传送到本地库的数据量，
提升了 postgres_fdw 外部表上聚合查询的性能。
📣 5.总结
FDW的其它插件支持外部表方式访问mysql,oracle等数据库表数据，PG的FDW可以根据业务开发各种FDW把数据进行融合，比如数据迁移、异构DBMS之间的联动，是一个做数据整个的非常棒的功能
