FDW 外部数据源

在 PostgreSQL 中，外部数据包装器（Foreign Data Wrapper，简称 FDW）是一个非常强大的功能，它允许 PostgreSQL 数据库访问外部数据源，如其他数据库、文件系统或甚至 Web 服务。通过 FDW，PostgreSQL 可以像查询本地表一样查询外部数据，从而实现跨数据库和跨系统的数据整合。

1. 外部数据源的概念

外部数据源指的是 PostgreSQL 以外的系统或数据库，通常存储着不同格式的数据。通过 FDW，PostgreSQL 可以将这些外部数据源的数据抽象为关系型表，使得查询和操作外部数据与操作本地数据类似。

常见的外部数据源类型

• 其他 PostgreSQL 数据库：通过 PostgreSQL 提供的 postgres_fdw 外部数据包装器访问。
• MySQL 数据库：使用 mysql_fdw 外部数据包装器连接到 MySQL 数据库。
• CSV 文件：通过 file_fdw 或 csv_fdw 读取外部 CSV 文件。
• NoSQL 数据库：如 MongoDB、Cassandra 等，可以使用特定的 FDW 连接器进行访问。

2. 安装与配置 FDW

安装外部数据包装器

要使用 FDW 功能，首先需要安装相应的外部数据包装器。例如，要连接其他 PostgreSQL 数据库，可以使用 PostgreSQL 官方提供的 postgres_fdw。

# 安装 postgres_fdw
sudo apt-get install postgresql-contrib

如果需要连接 MySQL，可以安装 mysql_fdw：

# 安装 mysql_fdw
sudo apt-get install postgresql-mysql-fdw

配置外部数据源

一旦安装了 FDW，您可以在 PostgreSQL 中创建外部服务器、用户映射和外部表，以实现与外部数据源的连接。

1. 创建外部服务器

首先，需要创建一个外部服务器来指定外部数据源的连接信息。

-- 创建外部服务器
CREATE SERVER my_foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');

2. 创建用户映射

在 PostgreSQL 中创建外部服务器后，还需要配置用户映射，指定如何通过用户名和密码连接到外部服务器。

-- 创建用户映射
CREATE USER MAPPING FOR current_user
    SERVER my_foreign_server
    OPTIONS (user 'remote_user', password 'password');

3. 创建外部表

最后，您可以在 PostgreSQL 中创建外部表，以便查询和操作外部数据源的数据。

-- 创建外部表
CREATE FOREIGN TABLE foreign_table (
    id SERIAL,
    name VARCHAR(100)
) SERVER my_foreign_server
    OPTIONS (schema_name 'public', table_name 'remote_table');

3. 查询外部数据源

创建了外部表后，您就可以像查询本地表一样查询外部数据源中的数据。例如，查询远程 PostgreSQL 数据库的 remote_table：

SELECT * FROM foreign_table;

由于 PostgreSQL 会在查询时自动将请求转发给外部数据源，您可以通过同样的 SQL 语法访问外部数据。

4. 外部数据源的写操作

FDW 允许在外部数据源中进行某些写操作，前提是外部数据源支持相应的操作。对于某些外部数据源，如 PostgreSQL 到 PostgreSQL，您可以进行插入、更新和删除等操作。

-- 插入数据到外部表
INSERT INTO foreign_table (id, name) VALUES (1, 'John Doe');

但是，并非所有外部数据源都支持写操作。例如，连接到文件系统时，只能读取数据，而不能修改。

5. 外部数据源的性能优化

尽管 FDW 提供了强大的数据访问能力，但在性能方面可能会有一定的开销，特别是在跨网络查询时。以下是一些性能优化策略：

使用分区表

如果外部数据源支持分区表，可以在 PostgreSQL 中创建相应的分区表，并利用分区的查询优化策略。

优化查询

尽量避免复杂的查询，尤其是涉及到外部数据源和本地数据源混合的查询。可以通过拆分查询或只查询需要的数据来提高性能。

本地缓存

在某些场景下，可以将外部数据源的数据缓存到本地 PostgreSQL 中，避免频繁访问外部数据源。

6. 常见的 FDW 类型

postgres_fdw

这是 PostgreSQL 官方提供的用于连接其他 PostgreSQL 数据库的 FDW。

# 安装并使用 postgres_fdw
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

mysql_fdw

用于连接 MySQL 数据库的 FDW。

# 安装并使用 mysql_fdw
CREATE EXTENSION IF NOT EXISTS mysql_fdw;

file_fdw

用于连接文件系统的 FDW，可以用于访问本地或远程文件系统中的数据文件。

# 安装并使用 file_fdw
CREATE EXTENSION IF NOT EXISTS file_fdw;

7. 小结

通过使用 FDW，PostgreSQL 可以高效地访问外部数据源，扩展其数据访问能力。无论是连接其他数据库、文件系统，还是 NoSQL 数据库，FDW 都能让 PostgreSQL 成为一个更加灵活和强大的数据平台。合理配置外部服务器、用户映射和外部表，并结合适当的性能优化策略，可以大幅提升跨数据源查询的效率。