PostgreSQL FDW 概述及原理

最新推荐文章于 2024-09-09 09:47:53 发布

「光与松果」

最新推荐文章于 2024-09-09 09:47:53 发布

阅读量890

点赞数 16

分类专栏： PostgreSQL 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/qq_34254018/article/details/141964302

版权

PostgreSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

PostgreSQL 外部数据包装器（Foreign Data Wrapper，简称 FDW）是一种用于访问外部数据源的机制。通过 FDW，你可以在 PostgreSQL 中查询、插入、更新和删除非本地数据库中的数据，就像操作本地表一样。FDW 提供了一种透明且高效的方式来集成和管理分布式数据。

基本概念

FDW: 外部数据包装器，是一个接口允许 PostgreSQL 通过标准 SQL 访问外部数据源。
外部服务器: 定义要访问的远程数据源。例如另一个 PostgreSQL 数据库、MySQL 数据库或文件系统等。
外部表: 在 PostgreSQL 中定义的指向外部数据源的表，使其像普通表一样被查询。

工作原理

安装和配置 FDW 扩展：首先需要在 PostgreSQL 中安装并配置相应的 FDW 扩展。
创建外部服务器：定义要连接的外部数据源，包括服务器地址和其他连接参数。
创建用户映射：定义 PostgreSQL 用户和外部数据源用户之间的映射关系，以便进行身份验证。
创建外部表：在 PostgreSQL 中创建指向外部数据源的表，定义如何在外部数据源中找到和解释数据。

示例1：使用 PostgreSQL FDW 访问远程 PostgreSQL 数据库

步骤 1：安装 postgres_fdw 扩展

首先，需要安装 postgres_fdw 扩展。通常默认情况下，该扩展包含在 PostgreSQL 包中，只需启用即可：

CREATE EXTENSION postgres_fdw;

步骤 2：创建外部服务器

然后，定义外部服务器，指定远程 PostgreSQL 数据库的连接信息：

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');

步骤 3：创建用户映射

为当前 PostgreSQL 用户创建用户映射，提供远程服务器的认证信息：

CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'remote_user', password 'remote_password');

步骤 4：创建外部表

最后，创建外部表，指向远程服务器上的具体表：

CREATE FOREIGN TABLE foreign_table (
    id integer,
    name text,
    age integer
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'remote_table');

现在，你可以像操作本地表一样查询外部表：

SELECT * FROM foreign_table;

高级功能

推送谓词（Pushdown Predicate）：

FDW 支持将某些过滤条件（如 WHERE 子句）推送到远程服务器执行，从而减少传输的数据量，提高性能。

EXPLAIN SELECT * FROM foreign_table WHERE age > 30;

写操作：

通过 FDW，不仅可以读取远程数据，还可以执行写操作（INSERT、UPDATE、DELETE）。

INSERT INTO foreign_table (id, name, age) VALUES (4, 'David', 35);
UPDATE foreign_table SET name = 'Davy' WHERE id = 4;
DELETE FROM foreign_table WHERE id = 4;

事务与一致性：

FDW 提供了对多种事务隔离级别的支持，但需要注意跨数据库事务的复杂性和潜在风险。

多种数据源支持：

除了 PostgreSQL 数据库外，FDW 还支持访问多种不同类型的数据源，如 Oracle（通过oracle_fdw）、MySQL（通过 mysql_fdw）、MongoDB（通过 mongo_fdw）、文件系统（通过 file_fdw）等。

示例2：使用 file_fdw 访问 CSV 文件

除了远程数据库，还可以使用 file_fdw 访问文件系统中的数据，如 CSV 文件。

安装 file_fdw 扩展

CREATE EXTENSION file_fdw;

创建外部服务器

CREATE SERVER file_server
FOREIGN DATA WRAPPER file_fdw;

创建外部表

CREATE FOREIGN TABLE csv_table (
    id integer,
    name text,
    age integer
)
SERVER file_server
OPTIONS (filename '/path/to/your/file.csv', format 'csv', header 'true');