PostgreSQL 访问外部数据库之 postgres_fdw

文章目录

        postgres_fdw 简介
        postgres_fdw 安装
        创建外部服务器对象
        创建用户映射
        创建外部表
        访问远程数据表
        postgres_fdw 选项
            连接选项
            对象名称选项
            成本评估选项
            远程执行选项
            更新操作选项
            导入选项
        连接管理
        事务管理
        远程查询优化
        远程查询执行环境

在某些情况下，例如数据仓库的 ETL 流程中，我们可能需要从一个 PostgreSQL 服务器访问另一个远程 PostgreSQL 服务器中的数据。为此，PostgreSQL 提供了一个扩展的模块：postgres_fdw。今天我们就来介绍一下这个模块的使用方法和案例。
postgres_fdw 简介

postgres_fdw 是基于 SQL/MED 标准开发的一个外部数据封装器（Foreign Data Wrapper），可以用于访问外部 PostgreSQL 服务器，对远程数据表执行 SELECT、INSERT、UPDATE 以及 DELETE 操作。

与 postgres_fdw 类似的另一个扩展模块是 dblink。它们的功能基本相同，但是 postgres_fdw 提供了更透明且符合标准的语法来访问远程表，并且在很多情况下可以提供更好的性能。postgres_fdw 可以支持的远程服务器最低版本为 PostgreSQL 8.3，如果是只读访问则可以支持到 PostgreSQL 8.1。

通过 postgres_fdw 访问远程数据表的步骤如下：

    使用CREATE EXTENSION命令安装 postgres_fdw 模块；
    使用CREATE SERVER命令创建一个外部服务器对象，该对象代表了想要连接的远程数据库；
    使用CREATE USER MAPPING命令为本地用户创建一个远程用户映射；
    使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA语句为每个远程数据库中的表创建一个外部表。

然后，就可以通过查询外部表访问远程表中的数据，包括 SELECT、INSERT、UPDATE 以及 DELETE 操作（当然，用户映射时指定的远程用户必须拥有这些表上的相应权限）。接下来我们就通过一个实际案例介绍如何实现以上步骤。

    📝除了 postgres_fdw，PostgreSQL 还支持访问各种数据源的外部数据封装器，包括 Oracle、MySQL、SQL Server、SQLite 等关系型数据库，MongoDB、Redis、Neo4j、Cassandra 等 NoSQL 数据库，CSV、XML、JSON 等文件，Elasticsearch、Hive、HBase 等大数据平台等等，具体可以参考 PostgreSQL Wiki。

postgres_fdw 安装

首先，我们需要安装 postgres_fdw 模块。对于 Linux 操作系统，可以通过 postgresql-contrib 软件包下载 PostgreSQL 扩展模块；对于 Windows 和 macOS，默认安装已经包含了扩展模块。我们只需要执行以下语句在当前数据库中安装 postgres_fdw 模块：

CREATE EXTENSION postgres_fdw;

创建外部服务器对象

然后，使用 CREATE SERVER 语句创建一个外部服务器（foreign server）。例如：

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

其中，foreign_server 是我们指定的外部服务器名称；host 参数是远程服务器的地址，示例中为 192.168.56.104；port 参数是远程服务器的端口，示例中为 5432；dbname 参数是远程数据库的名称，示例中为 pagila。

    📝关于 CREATE SERVER 语句的详细介绍，可以参考官方文档。

创建用户映射

下一步，使用 CREATE USER MAPPING 语句创建一个用户映射（user mapping），为本地用户指定一个访问远程服务器时的角色。例如：

CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'tony', password 'pswd123#');

以上语句为本地 postgres 用户创建了一个访问远程服务器 foreign_server 时的用户映射，也就是使用用户名 tony 和密码 pswd123# 连接远程服务器。

    📝关于 CREATE USER MAPPING 语句的详细介绍，可以参考官方文档。

创建外部表

然后，使用 CREATE FOREIGN TABLE 语句创建一个外部表（foreign table）。例如：

CREATE FOREIGN TABLE country (
    country_id int NOT NULL,
    country varchar(50) NOT NULL,
    last_update timestamp NOT NULL
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'country');

以上示例在本地数据库中创建了一个名为 country 的外部表，对应的是远程服务器 foreign_server 中 public 模式下的 country 表。

通常来说，推荐创建外部表时使用和远程表一致的数据类型以及可能的排序规则。虽然目前 postgres_fdw 支持各种类型转换，远程服务器和本地服务器解析 WHERE 子句的细微差别可能会导致意外的语义异常。

另外，外部表的字段个数可以少于远程表，字段顺序也可以不同；因为字段的映射是通过名称而不是字段位置实现。

    📝关于 CREATE FOREIGN TABLE 语句的详细介绍，可以参考官方文档。
    📝另一种创建外部表的方法是使用 IMPORT FOREIGN SCHEMA 语句实现批量导入，具体参考官方文档。

访问远程数据表

现在，我们可以在本地数据库中通过外部表访问对应的远程表。例如：

SELECT *
FROM country
LIMIT 5;
country_id|country       |last_update        |
----------|--------------|-------------------|
         1|Afghanistan   |2006-02-15 09:44:00|
         2|Algeria       |2006-02-15 09:44:00|
         3|American Samoa|2006-02-15 09:44:00|
         4|Angola        |2006-02-15 09:44:00|
         5|Anguilla      |2006-02-15 09:44:00|

除了查询操作，postgres_fdw 同样可以通过 DML 语句修改远程表中的数据。对于 INSERT 语句，目前还不支持 ON CONFLICT DO UPDATE 子句；但是可以使用省略了冲突条件的 ON CONFLICT DO NOTHING 子句。例如：

INSERT INTO country VALUES (109, 'Zambia', '2006-02-15 09:44:00')
ON CONFLICT DO NOTHING;

另外，虽然 postgres_fdw 支持对分区表执行导致数据行移动的 UPDATE 语句；但是如果远程分区既是行移动的目标分区，又是更新操作的目标分区时，UDATE 语句无法执行。
postgres_fdw 选项

在使用 postgres_fdw 创建外部服务器、用户映射以及外部表等对象时，可以通过 OPTIONS 指定一些选项设置不同的行为。
连接选项

通过 postgres_fdw 创建远程服务器时的选项和 libpq 连接字符串相同，但是不支持以下选项：

    user 和 password（这两个选项可以在创建用户映射时指定）；
    client_encoding（自动使用本地服务器编码进行设置）；
    fallback_application_name（默认设置为 postgres_fdw）。

只有超级永远可以不通过密码认证连接远程服务器，所以为普通用户创建用户映射时不要忘记指定 password 选项。

更多请见：http://www.mark-to-win.com/tutorial/51608.html