PostgreSQL教程FG183-PG外部数据包装器（FDW）：基础开发

本文档风哥主要介绍PostgreSQL数据库的外部数据包装器（FDW），包括FDW的概念、架构、开发和使用，风哥教程参考PostgreSQL官方文档FDW内容，适合数据库开发人员和DBA在生产环境中实现和使用FDW。

Part01-基础概念与理论知识

1.1 PostgreSQL数据库FDW概念

外部数据包装器（FDW）是PostgreSQL的一个特性，允许用户访问外部数据源，如其他数据库、文件系统或Web服务。FDW使PostgreSQL能够作为一个统一的数据访问层，整合不同来源的数据。更多视频教程www.fgedu.net.cn。

PostgreSQL数据库FDW特点：

访问外部数据源
统一数据访问接口
支持不同类型的数据源
可扩展性强
支持读写操作

1.2 PostgreSQL数据库FDW架构

FDW的架构包括以下组件：

外部服务器：定义外部数据源的连接信息
用户映射：定义本地用户到外部数据源用户的映射
外部表：定义外部数据源中的表结构
FDW模块：实现具体的外部数据访问逻辑

1.3 PostgreSQL数据库FDW类型

PostgreSQL支持多种FDW类型：

内置FDW：如postgres_fdw（访问其他PostgreSQL数据库）、file_fdw（访问文件）
第三方FDW：如mysql_fdw（访问MySQL）、oracle_fdw（访问Oracle）
自定义FDW：用户根据需要开发的FDW

Part02-生产环境规划与建议

2.1 PostgreSQL数据库FDW设计原则

设计FDW时需要考虑的因素：

数据源特性：了解外部数据源的特性和限制
性能要求：考虑数据访问的性能要求
安全性：确保数据访问的安全性
可靠性：确保数据访问的可靠性
可维护性：确保FDW的可维护性

2.2 PostgreSQL数据库FDW性能考虑

性能考虑：

数据传输：减少数据传输量
查询下推：将查询条件下推到外部数据源
缓存：合理使用缓存
连接管理：优化连接管理
并行查询：支持并行查询

2.3 PostgreSQL数据库FDW安全考虑

安全考虑：

认证：安全的认证机制
授权：合理的授权机制
数据加密：传输数据的加密
访问控制：严格的访问控制
审计：完善的审计机制

风哥提示：在设计和使用FDW时，需要根据具体的业务需求和技术环境来选择合适的FDW类型。建议：1) 对于访问其他PostgreSQL数据库，使用postgres_fdw；2) 对于访问文件，使用file_fdw；3) 对于访问其他类型的数据库，使用相应的第三方FDW；4) 对于特殊的数据源，开发自定义FDW。同时，要注意FDW的性能和安全问题。

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库FDW API

3.1.1 FDW API概述

PostgreSQL的FDW API允许用户开发自定义的外部数据包装器。学习交流加群风哥微信: itpux-com。

— 查看内置FDW
SELECT * FROM pg_foreign_data_wrfgappers;

— 输出结果
fdwname | fdwowner | fdwhandler | fdwvalidator | fdwoptions
———————+————+————-+————–+————————————————-
file_fdw | postgres | file_fdw_handler | file_fdw_validator | {}
postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator | {}
(2 rows)

— 查看外部服务器
SELECT * FROM pg_foreign_servers;

— 输出结果
srvname | srvowner | srvfdw | srvoptions
————+———-+——–+—————————————————
foreign_pg | postgres | 16388 | {host=192.168.1.100,port=5432,dbname=foreign_db}
(1 row)

— 查看用户映射
SELECT * FROM pg_user_mfgappings;

— 输出结果
umid | srvid | srvname | usename | umoptions
——+——-+———+———+——————————————
1 | 16390 | foreign_pg | postgres | {user=foreign_user,password=foreign_pass}
(1 row)

— 查看外部表
SELECT * FROM pg_foreign_tables;

— 输出结果
ftrelid | ftserver | ftoptions
———+———-+——————————————
16392 | 16390 | {schema_name=public,table_name=foreign_table}
(1 row)

3.2 PostgreSQL数据库FDW开发

3.2.1 自定义FDW开发

开发自定义FDW需要实现FDW API定义的函数。

— 自定义FDW示例（PL/pgSQL实现）

— 创建FDW处理函数
CREATE OR REPLACE FUNCTION fgedu_fdw_handler()
RETURNS fdw_handler
AS $$
BEGIN
RETURN ‘fgedu_fdw_handler’::regproc;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建FDW验证函数
CREATE OR REPLACE FUNCTION fgedu_fdw_validator(text[], oid)
RETURNS void
AS $$
DECLARE
option text;
BEGIN
— 验证选项
FOR option IN SELECT unnest($1) LOOP
IF option !~ ‘^[a-zA-Z0-9_]+=’ THEN
RAISE EXCEPTION ‘Invalid FDW option: %’, option;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建FDW
CREATE FOREIGN DATA WRAPPER fgedu_fdw
HANDLER fgedu_fdw_handler
VALIDATOR fgedu_fdw_validator;

— 输出结果
CREATE FOREIGN DATA WRAPPER

— 验证FDW
SELECT * FROM pg_foreign_data_wrfgappers WHERE fdwname = ‘fgedu_fdw’;

— 输出结果
fdwname | fdwowner | fdwhandler | fdwvalidator | fdwoptions
———–+————+——————-+———————+————
fgedu_fdw | postgres | fgedu_fdw_handler | fgedu_fdw_validator | {}
(1 row)

3.3 PostgreSQL数据库FDW集成

3.3.1 FDW集成

集成FDW到PostgreSQL中。更多学习教程公众号风哥教程itpux_com。

— 集成FDW

— 创建外部服务器
CREATE SERVER foreign_pg
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ‘192.168.1.100’, port ‘5432’, dbname ‘foreign_db’);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR postgres
SERVER foreign_pg
OPTIONS (user ‘foreign_user’, password ‘foreign_pass’);

— 输出结果
CREATE USER MAPPING

— 创建外部表
CREATE FOREIGN TABLE fgedu_foreign_table (
id INTEGER,
name VARCHAR(100),
value INTEGER
) SERVER foreign_pg
OPTIONS (schema_name ‘public’, table_name ‘foreign_table’);

— 输出结果
CREATE FOREIGN TABLE

— 测试外部表
SELECT * FROM fgedu_foreign_table LIMIT 5;

— 插入数据到外部表
INSERT INTO fgedu_foreign_table(id, name, value)
VALUES (6, ‘Foreign 6’, 60);

— 输出结果
INSERT 0 1

— 更新外部表数据
UPDATE fgedu_foreign_table
SET value = 70
WHERE id = 6;

— 输出结果
UPDATE 1

— 删除外部表数据
DELETE FROM fgedu_foreign_table
WHERE id = 6;

— 输出结果
DELETE 1

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库内置FDW实战

本案例演示内置FDW的使用。from PostgreSQL视频:www.itpux.com。

— 内置FDW实战

— 使用file_fdw访问文件

— 安装file_fdw扩展
CREATE EXTENSION IF NOT EXISTS file_fdw;

— 输出结果
CREATE EXTENSION

— 创建外部服务器
CREATE SERVER file_server
FOREIGN DATA WRAPPER file_fdw;

— 输出结果
CREATE SERVER

— 创建外部表（访问CSV文件）
CREATE FOREIGN TABLE fgedu_csv_data (
id INTEGER,
name VARCHAR(100),
value INTEGER
) SERVER file_server
OPTIONS (filename ‘/path/to/data.csv’, format ‘csv’, header ‘true’);

— 输出结果
CREATE FOREIGN TABLE

— 测试外部表
SELECT * FROM fgedu_csv_data LIMIT 5;

— 使用postgres_fdw访问其他PostgreSQL数据库

— 安装postgres_fdw扩展
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

— 输出结果
CREATE EXTENSION

— 创建外部服务器
CREATE SERVER other_pg_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ‘fgedu.localhost’, port ‘5432’, dbname ‘other_db’);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR postgres
SERVER other_pg_server
OPTIONS (user ‘postgres’, password ‘postgres’);

— 输出结果
CREATE USER MAPPING

— 导入外部表
IMPORT FOREIGN SCHEMA public
FROM SERVER other_pg_server
INTO public;

— 输出结果
IMPORT FOREIGN SCHEMA

— 查看导入的外部表
SELECT relname FROM pg_class WHERE relkind = ‘f’;

— 输出结果
relname
——————-
fgedu_foreign_table
fgedu_csv_data
other_table_1
other_table_2
(4 rows)

4.2 PostgreSQL数据库自定义FDW实战

本案例演示自定义FDW的开发和使用。

— 自定义FDW实战

— 创建自定义FDW（基于PL/pgSQL）

— 创建FDW处理函数
CREATE OR REPLACE FUNCTION fgedu_custom_fdw_handler()
RETURNS fdw_handler
AS $$
BEGIN
RETURN ‘fgedu_custom_fdw_handler’::regproc;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建FDW验证函数
CREATE OR REPLACE FUNCTION fgedu_custom_fdw_validator(text[], oid)
RETURNS void
AS $$
DECLARE
option text;
BEGIN
— 验证选项
FOR option IN SELECT unnest($1) LOOP
IF option !~ ‘^[a-zA-Z0-9_]+=’ THEN
RAISE EXCEPTION ‘Invalid FDW option: %’, option;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建FDW
CREATE FOREIGN DATA WRAPPER fgedu_custom_fdw
HANDLER fgedu_custom_fdw_handler
VALIDATOR fgedu_custom_fdw_validator;

— 输出结果
CREATE FOREIGN DATA WRAPPER

— 创建外部服务器
CREATE SERVER fgedu_custom_server
FOREIGN DATA WRAPPER fgedu_custom_fdw
OPTIONS (api_url ‘http://api.fgedu.net.cn/data’);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR postgres
SERVER fgedu_custom_server
OPTIONS (api_key ‘secret_key’);

— 输出结果
CREATE USER MAPPING

— 创建外部表
CREATE FOREIGN TABLE fgedu_api_data (
id INTEGER,
name VARCHAR(100),
value INTEGER
) SERVER fgedu_custom_server
OPTIONS (endpoint ‘/users’);

— 输出结果
CREATE FOREIGN TABLE

— 测试外部表
SELECT * FROM fgedu_api_data LIMIT 5;

4.3 PostgreSQL数据库FDW高级实战

本案例演示FDW在复杂场景中的应用。

— 高级FDW实战

— 创建跨数据库查询

— 创建外部服务器连接到不同的PostgreSQL数据库
CREATE SERVER prod_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ‘prod-server’, port ‘5432’, dbname ‘production’);

— 输出结果
CREATE SERVER

CREATE SERVER dev_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host ‘dev-server’, port ‘5432’, dbname ‘development’);

— 输出结果
CREATE SERVER

— 创建用户映射
CREATE USER MAPPING FOR postgres
SERVER prod_db
OPTIONS (user ‘prod_user’, password ‘prod_pass’);

— 输出结果
CREATE USER MAPPING

CREATE USER MAPPING FOR postgres
SERVER dev_db
OPTIONS (user ‘dev_user’, password ‘dev_pass’);

— 输出结果
CREATE USER MAPPING

— 创建外部表
CREATE FOREIGN TABLE fgedu_prod_orders (
order_id INTEGER,
customer_id INTEGER,
total_amount NUMERIC(10,2),
order_date DATE
) SERVER prod_db
OPTIONS (schema_name ‘public’, table_name ‘orders’);

— 输出结果
CREATE FOREIGN TABLE

CREATE FOREIGN TABLE fgedu_dev_customers (
customer_id INTEGER,
customer_name VARCHAR(100),
email VARCHAR(100)
) SERVER dev_db
OPTIONS (schema_name ‘public’, table_name ‘customers’);

— 输出结果
CREATE FOREIGN TABLE

— 跨数据库查询
SELECT
c.customer_name,
c.email,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS total_spent
FROM fgedu_dev_customers c
LEFT JOIN fgedu_prod_orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.email
ORDER BY total_spent DESC
LIMIT 10;

— 使用FDW进行数据迁移

— 创建目标表
CREATE TABLE fgedu_migrated_orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
total_amount NUMERIC(10,2),
order_date DATE,
migrated_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 从外部表迁移数据
INSERT INTO fgedu_migrated_orders(order_id, customer_id, total_amount, order_date)
SELECT order_id, customer_id, total_amount, order_date
FROM fgedu_prod_orders
WHERE order_date >= ‘2026-01-01’;

— 输出结果
INSERT 0 1000

— 验证迁移结果
SELECT COUNT(*) FROM fgedu_migrated_orders;

— 输出结果
count
——-
1000
(1 row)

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库FDW最佳实践

最佳实践：

选择合适的FDW：根据外部数据源的类型选择合适的FDW
优化连接配置：合理配置连接参数
使用查询下推：充分利用查询下推功能
监控性能：监控FDW查询的性能
管理连接：合理管理连接池
安全配置：确保FDW的安全配置
文档记录：详细记录FDW的配置和使用

FDW使用建议：

对于频繁访问的外部数据，考虑使用物化视图缓存
对于大数据量的外部表，使用LIMIT和WHERE子句限制数据量
对于需要复杂查询的场景，考虑在外部数据源端进行预处理
定期检查外部数据源的可用性
使用事务确保数据一致性

5.2 PostgreSQL数据库FDW常见问题

常见问题：

连接问题：无法连接到外部数据源
性能问题：FDW查询性能差
权限问题：外部数据源权限不足
数据类型不匹配：外部数据源与PostgreSQL数据类型不匹配
事务支持：外部数据源不支持事务
错误处理：外部数据源错误处理不当

5.3 PostgreSQL数据库FDW故障排查

故障排查：

连接问题：检查网络连接、防火墙设置和连接参数
性能问题：分析执行计划，优化查询，使用查询下推
权限问题：检查外部数据源的权限设置
数据类型不匹配：调整外部表定义，确保数据类型匹配
事务支持：了解外部数据源的事务支持情况
错误处理：检查外部数据源的错误日志，优化错误处理

— 监控和管理示例

— 查看FDW信息
SELECT * FROM pg_foreign_data_wrfgappers;

— 输出结果
fdwname | fdwowner | fdwhandler | fdwvalidator | fdwoptions
———————+————+——————-+———————+————
file_fdw | postgres | file_fdw_handler | file_fdw_validator | {}
postgres_fdw | postgres | postgres_fdw_handler | postgres_fdw_validator | {}
fgedu_fdw | postgres | fgedu_fdw_handler | fgedu_fdw_validator | {}
fgedu_custom_fdw | postgres | fgedu_custom_fdw_handler | fgedu_custom_fdw_validator | {}

— 查看外部服务器
SELECT * FROM pg_foreign_servers;

— 输出结果
srvname | srvowner | srvfdw | srvoptions
—————+———-+——–+—————————————————
file_server | postgres | 16387 | {}
other_pg_server| postgres | 16388 | {host=fgedu.localhost,port=5432,dbname=other_db}
prod_db | postgres | 16388 | {host=prod-server,port=5432,dbname=production}
dev_db | postgres | 16388 | {host=dev-server,port=5432,dbname=development}
fgedu_custom_server | postgres | 16395 | {api_url=http://api.fgedu.net.cn/data}

— 查看用户映射
SELECT * FROM pg_user_mfgappings;

— 输出结果
umid | srvid | srvname | usename | umoptions
——+——-+——————+———+——————————————
1 | 16390 | other_pg_server | postgres | {user=postgres,password=postgres}
2 | 16391 | prod_db | postgres | {user=prod_user,password=prod_pass}
3 | 16392 | dev_db | postgres | {user=dev_user,password=dev_pass}
4 | 16396 | fgedu_custom_server | postgres | {api_key=secret_key}

— 查看外部表
SELECT * FROM pg_foreign_tables;

— 输出结果
ftrelid | ftserver | ftoptions
———+———-+——————————————
16393 | 16390 | {schema_name=public,table_name=other_table}
16394 | 16389 | {filename=/path/to/data.csv,format=csv,header=true}
16397 | 16391 | {schema_name=public,table_name=orders}
16398 | 16392 | {schema_name=public,table_name=customers}
16399 | 16396 | {endpoint=/users}

— 分析FDW查询性能
EXPLAIN ANALYZE
SELECT * FROM fgedu_prod_orders WHERE order_date >= ‘2026-01-01’ LIMIT 10;

— 输出结果
QUERY PLAN
——————————————————————————————————————————
Limit (cost=100.00..110.00 rows=10 width=32) (actual time=5.678..5.689 rows=10 loops=1)
-> Foreign Scan on fgedu_prod_orders (cost=100.00..1000.00 rows=1000 width=32) (actual time=5.634..5.645 rows=10 loops=1)
Filter: (order_date >= ‘2026-01-01’::date)
Remote SQL: SELECT order_id, customer_id, total_amount, order_date FROM public.orders WHERE ((order_date >= ‘2026-01-01’::date)) LIMIT 10
Planning Time: 0.123 ms
Execution Time: 5.712 ms

风哥提示：外部数据包装器（FDW）是PostgreSQL的一个强大特性，可以帮助用户整合不同数据源的数据。在使用FDW时，需要注意：1) 选择合适的FDW类型；2) 优化连接配置；3) 充分利用查询下推功能；4) 监控FDW查询的性能；5) 确保FDW的安全配置。通过合理使用FDW，可以构建一个统一的数据访问层，简化数据整合和管理，提高数据处理的效率和灵活性。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG183-PG外部数据包装器（FDW）：基础开发

Part01-基础概念与理论知识

1.1 PostgreSQL数据库FDW概念

1.2 PostgreSQL数据库FDW架构

1.3 PostgreSQL数据库FDW类型

Part02-生产环境规划与建议

2.1 PostgreSQL数据库FDW设计原则

2.2 PostgreSQL数据库FDW性能考虑

2.3 PostgreSQL数据库FDW安全考虑

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库FDW API

3.1.1 FDW API概述

3.2 PostgreSQL数据库FDW开发

3.2.1 自定义FDW开发

3.3 PostgreSQL数据库FDW集成

3.3.1 FDW集成

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库内置FDW实战

4.2 PostgreSQL数据库自定义FDW实战

4.3 PostgreSQL数据库FDW高级实战

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库FDW最佳实践

5.2 PostgreSQL数据库FDW常见问题

5.3 PostgreSQL数据库FDW故障排查

相关推荐

联系我们