优化 PostgreSQL 外部数据包装器以实现多数据库集成

当您的数据分布在多个位置，并且您需要在不预先复制所有数据的情况下进行查询时，PostgreSQL 外部数据包装器 (FDW) 会很有帮助。FDW 可以使远程表看起来像本地表，但性能和事务行为在很大程度上取决于包装器、网络和查询结构。

什么是外部数据包装器？

FDW 实现了 SQL/MED（外部数据管理）标准，提供：

透明访问：使用标准 SQL 查询远程数据
虚拟表：无需数据复制
实时远程读取：查询从远程源读取，而不是本地副本
连接能力：组合本地和远程数据
写操作：某些包装器支持 INSERT、UPDATE 和 DELETE

常见用例

数据库迁移：在迁移期间访问遗留系统
多租户架构：跨租户数据库查询
数据聚合：合并来自多个源的数据
混合云：连接本地和云数据库
报表：创建集中式报表数据库
微服务：跨服务边界查询

可用的外部数据包装器

postgresql_fdw（内置）

连接到其他 PostgreSQL 数据库
完整功能支持
最佳性能

mysql_fdw

连接到 MySQL/MariaDB
支持读写

oracle_fdw

连接到 Oracle 数据库
通常通过开源 oracle_fdw 扩展使用，需单独安装 Oracle 客户端库

mongodb_fdw

访问 MongoDB 集合
JSON 数据集成

file_fdw（内置）

读取 CSV 和文本文件
日志文件分析

其他

redis_fdw、sqlite_fdw、couchdb_fdw 等

设置 postgres_fdw

步骤 1：安装扩展

-- 在本地数据库上
CREATE EXTENSION postgres_fdw;

步骤 2：创建外部服务器

CREATE SERVER remote_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote-db.example.com',
        port '5432',
        dbname 'production_db',
        fetch_size '10000'
    );

步骤 3：创建用户映射

-- 将本地用户映射到远程凭据
CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (
        user 'remote_user',
        password 'secure_password'
    );

-- 映射所有本地用户
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (
        user 'readonly_user',
        password 'readonly_pass'
    );

步骤 4：创建外部表

手动定义表：

CREATE FOREIGN TABLE remote_users (
    id INTEGER,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'users');

导入整个模式：

IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_db
    INTO local_schema;

导入特定表：

IMPORT FOREIGN SCHEMA public
    LIMIT TO (users, orders, products)
    FROM SERVER remote_db
    INTO public;

步骤 5：查询外部数据

-- 简单查询
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '7 days';

-- 连接本地和远程数据
SELECT 
    l.order_id,
    l.amount,
    r.username,
    r.email
FROM local_orders l
JOIN remote_users r ON l.user_id = r.id
WHERE l.status = 'pending';

高级配置

性能调优选项

CREATE SERVER fast_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'db.example.com',
        port '5432',
        dbname 'mydb',
        
        -- 每次往返检索的行数
        fetch_size '50000',
        
        -- 启用查询下推优化
        use_remote_estimate 'true',
        
        -- 在 PostgreSQL 会话中重用外部服务器连接
        keep_connections 'on',
        
        -- 并行查询执行
        parallel_commit 'on',
        parallel_abort 'on'
    );

表级选项

CREATE FOREIGN TABLE optimized_table (
    id INTEGER,
    data TEXT
)
SERVER remote_db
OPTIONS (
    schema_name 'public',
    table_name 'large_table',
    
    -- 为此表覆盖 fetch_size
    fetch_size '100000',
    
    -- 使用远程估计以获得更好的查询计划
    use_remote_estimate 'true'
);

列级选项

CREATE FOREIGN TABLE mapped_columns (
    local_id INTEGER OPTIONS (column_name 'remote_user_id'),
    local_name TEXT OPTIONS (column_name 'remote_username')
)
SERVER remote_db
OPTIONS (table_name 'users');

写操作

启用写操作

-- 授予权限
GRANT INSERT, UPDATE, DELETE ON remote_users TO app_user;

-- 插入数据
INSERT INTO remote_users (username, email)
VALUES ('newuser', '[email protected]');

-- 更新数据
UPDATE remote_users
SET email = '[email protected]'
WHERE username = 'newuser';

-- 删除数据
DELETE FROM remote_users WHERE username = 'newuser';

跨数据库事务

BEGIN;
    -- 插入本地表
    INSERT INTO local_orders (user_id, amount) VALUES (123, 99.99);
    
    -- 更新远程表
    UPDATE remote_users SET last_order = NOW() WHERE id = 123;
    
    -- 两个操作一起提交
COMMIT;

PostgreSQL 通过 FDW 协调远程工作，但您不应假设每个包装器都提供完整的分布式事务保证。对于 postgres_fdw，如果您需要两阶段提交行为，请查看 two_phase_commit 服务器选项，并在依赖它进行关键写入之前测试崩溃恢复。

多数据库联邦

连接多个数据库

-- 连接到生产数据库
CREATE SERVER prod_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'prod.example.com', dbname 'production');

CREATE USER MAPPING FOR PUBLIC SERVER prod_db
    OPTIONS (user 'readonly', password 'pass1');

-- 连接到分析数据库
CREATE SERVER analytics_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics.example.com', dbname 'warehouse');

CREATE USER MAPPING FOR PUBLIC SERVER analytics_db
    OPTIONS (user 'readonly', password 'pass2');

-- 连接到归档数据库
CREATE SERVER archive_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive.example.com', dbname 'historical');

CREATE USER MAPPING FOR PUBLIC SERVER archive_db
    OPTIONS (user 'readonly', password 'pass3');

跨数据库的统一视图

-- 导入表
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (current_orders) 
    FROM SERVER prod_db INTO public;

IMPORT FOREIGN SCHEMA public 
    LIMIT TO (archived_orders) 
    FROM SERVER archive_db INTO public;

-- 创建统一视图
CREATE VIEW all_orders AS
SELECT *, 'current' AS source FROM current_orders
UNION ALL
SELECT *, 'archived' AS source FROM archived_orders;

-- 跨所有数据库查询
SELECT 
    source,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM all_orders
WHERE created_at > '2024-01-01'
GROUP BY source;

连接到 MySQL

安装 mysql_fdw

# 包名因 PostgreSQL 版本和仓库而异。
# 在 Debian/Ubuntu 上，先搜索：
apt search mysql-fdw

# 然后安装与您的 PostgreSQL 主版本匹配的包。
sudo apt install postgresql-16-mysql-fdw

配置 MySQL 连接

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (
        host 'mysql-db.example.com',
        port '3306'
    );

CREATE USER MAPPING FOR PUBLIC
    SERVER mysql_server
    OPTIONS (
        username 'mysql_user',
        password 'mysql_pass'
    );

CREATE FOREIGN TABLE mysql_products (
    id INTEGER,
    name VARCHAR(100),
    price DECIMAL(10,2)
)
SERVER mysql_server
OPTIONS (dbname 'ecommerce', table_name 'products');

-- 从 PostgreSQL 查询 MySQL 数据
SELECT * FROM mysql_products WHERE price > 100;

性能优化

1. 使用 WHERE 子句下推

PostgreSQL 将过滤器推送到远程服务器：

-- 好：过滤器在远程应用
SELECT * FROM remote_users WHERE country = 'US';

-- 检查查询计划
EXPLAIN (VERBOSE) SELECT * FROM remote_users WHERE country = 'US';
-- 查找："Remote SQL: SELECT ... WHERE country = 'US'"

2. 限制数据传输

-- 坏：获取所有列
SELECT * FROM remote_large_table;

-- 好：仅需要的列
SELECT id, username FROM remote_large_table;

-- 好：使用 LIMIT
SELECT * FROM remote_large_table LIMIT 1000;

3. 优化 Fetch 大小

-- 对于大型结果集
ALTER SERVER remote_db OPTIONS (SET fetch_size '50000');

-- 对于小型、频繁的查询
ALTER SERVER remote_db OPTIONS (SET fetch_size '1000');

4. 使用远程估计

ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- PostgreSQL 将查询远程 EXPLAIN 以获得准确的成本估计

5. 在远程表上创建索引

-- 确保远程表有适当的索引
-- 直接连接到远程数据库：

CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_orders_created_at ON orders(created_at);

6. 频繁访问数据的物化视图

-- 在本地缓存远程数据
CREATE MATERIALIZED VIEW cached_remote_data AS
SELECT * FROM remote_users WHERE active = true;

-- 定期刷新
REFRESH MATERIALIZED VIEW cached_remote_data;

-- 如果安装了 pg_cron 扩展，则自动刷新
CREATE EXTENSION pg_cron;
SELECT cron.schedule('refresh-cache', '0 */6 * * *', 
    'REFRESH MATERIALIZED VIEW cached_remote_data');

监控和故障排除

查看外部服务器

SELECT 
    srvname AS server_name,
    srvoptions AS options
FROM pg_foreign_server;

查看外部表

SELECT 
    foreign_table_schema,
    foreign_table_name,
    foreign_server_name
FROM information_schema.foreign_tables;

检查用户映射

SELECT 
    um.umuser::regrole AS local_user,
    fs.srvname AS foreign_server,
    um.umoptions AS options
FROM pg_user_mapping um
JOIN pg_foreign_server fs ON um.umserver = fs.oid;

分析查询性能

EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
SELECT * FROM remote_users WHERE created_at > NOW() - INTERVAL '1 day';

连接检查

使用 EXPLAIN (ANALYZE, VERBOSE) 确认远程 SQL 和时序。对于 PostgreSQL 到 PostgreSQL 的连接，在长时间 FDW 查询运行时，也检查远程服务器的 pg_stat_activity。

常见问题及解决方案

问题 1：连接超时

错误：could not connect to server

解决方案：

-- 添加连接超时
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

-- 检查网络连接
-- 从 shell：
psql -h remote-db.example.com -U user -d dbname

问题 2：查询缓慢

解决方案：

-- 启用远程估计
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- 增加 fetch 大小
ALTER SERVER remote_db OPTIONS (SET fetch_size '100000');

-- 检查 WHERE 子句是否被下推
EXPLAIN VERBOSE SELECT * FROM remote_table WHERE id > 1000;

问题 3：身份验证失败

错误：password authentication failed

解决方案：

-- 更新用户映射
ALTER USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (SET password 'new_password');

-- 检查远程服务器上的 pg_hba.conf
-- 确保它允许来自本地服务器的连接

问题 4：列类型不匹配

错误：column type mismatch

解决方案：

-- 显式转换列
CREATE FOREIGN TABLE remote_data (
    id INTEGER,
    data TEXT,
    created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (table_name 'source_table');

-- 或使用正确类型导入
DROP FOREIGN TABLE remote_data;
IMPORT FOREIGN SCHEMA public 
    LIMIT TO (source_table) 
    FROM SERVER remote_db INTO public;

安全考虑

1. 使用只读账户

-- 在远程数据库上，创建受限用户
CREATE ROLE fdw_readonly;
GRANT CONNECT ON DATABASE production_db TO fdw_readonly;
GRANT USAGE ON SCHEMA public TO fdw_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_readonly;

-- 在用户映射中使用
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_db
    OPTIONS (user 'fdw_readonly', password 'safe_pass');

2. 安全密码存储

-- 您可以使用 .pgpass 文件进行 libpq 连接，而不是将密码存储在用户映射中。确认文件所有权和权限。
-- ~/.pgpass 格式：
-- hostname:port:database:username:password

CREATE USER MAPPING FOR postgres
    SERVER remote_db
    OPTIONS (user 'remote_user');
    -- 密码从 .pgpass 读取

3. 网络安全

# 使用 SSL 连接
# 在远程的 postgresql.conf 中：
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- 在用户映射中要求 SSL
CREATE SERVER secure_remote
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'mydb',
        sslmode 'require'
    );

最佳实践

最小化数据传输：仅选择需要的列
使用索引：确保远程表有适当的索引
缓存频繁访问的数据：使用物化视图
监控性能：定期执行 EXPLAIN ANALYZE
限制写操作：大多数 FDW 使用场景优先选择只读
使用连接池：启用 keep_connections
设置适当的 fetch_size：根据查询模式调整
测试故障转移场景：为远程数据库不可用做计划
记录外部依赖：维护清晰的文档
定期安全审计：审查用户映射和权限

要点

PostgreSQL 外部数据包装器对于数据库联邦非常有用，特别是当您需要：

无缝集成：使用标准 SQL 查询远程数据
实时访问：无需数据复制或同步延迟
多数据库支持：PostgreSQL、MySQL、Oracle 等
性能：当包装器和查询支持时进行查询下推
灵活性：首先进行只读访问，在验证包装器行为后再进行写入

从对远程数据库的只读查询开始，通过适当的索引和 fetch 大小优化性能，然后根据需要扩展到更复杂的集成。