PostgreSQL 访问外部数据库之 postgres_fdw

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net



文章目录

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

大家好，我是只谈技术不剪发的 Tony 老师。在某些情况下，例如数据仓库的 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 选项。
对象名称选项

以下选项可以用于控制发送给远程服务器的 SQL 语句中使用的对象名称，只有当外部表定义中的名称和远程表的名称不一致时才需要指定：

    schema_name，该选项在创建外部表时指定，表示远程表所在的模式；如果省略，默认使用外部表所在的模式名。
    table_name，该选项在创建外部表时指定，表示远程表的名称；如果省略，默认使用外部表的名称。
    column_name，该选项在定义外部表的字段名时指定，表示对应远程表中的字段名；如果省略，默认使用外部表的字段名称。

例如，以下语句重新创建了外部表 country：

DROP FOREIGN TABLE IF EXISTS country;

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



首先，外部表 country 中的 id 字段和远程表的 country_id 字段名称不同，需要使用 OPTIONS 选项指定名称映射；其次，外部表 country 定义中没有指定 schema_name 和 table_name 选项，表示使用该表在本地数据库中的模式名（public）和表名（country）访问远程表。
成本评估选项

postgres_fdw 通过在远程服务器中执行查询语句返回数据，因此理想情况下扫描外部表的评估成本等于远程服务器执行操作的成本加上网络传输成本。获取这个评估成本最可靠的方法就是询问远程服务器并加上一些额外的消耗，但是对于简单查询语句，没有必要为此执行一次额外的远程查询。所以 postgres_fdw 提供了以下用于成本评估的额选项：

    use_remote_estimate，用于控制 postgres_fdw 是否提交远程 EXPLAIN 命令获取评估成本。该选项支持外部表和外部服务器级别的设置，外部表的设置优先级更高。默认设置为 false。
    fdw_startup_cost，表示建立连接、远程服务器解析查询和创建执行计划所需的额外成本。该选项支持外部服务器级别的设置，任何外部表的扫描都需要加上这个额外的启动成本。默认值为 100。
    fdw_tuple_cost，表示服务器之间传输一行数据所需的额外成本。该选项支持外部服务器级别的设置，可以根据网络延迟的情况设置不同的参数。默认值为 0.01。

如果 use_remote_estimate 设置为 true，postgres_fdw 从远程服务器获取行数和成本评估，然后加上 fdw_startup_cost 和 fdw_tuple_cost；如果 use_remote_estimate 设置为 false，postgres_fdw 执行本地行数和成本评估，然后加上 fdw_startup_cost 和 fdw_tuple_cost。

本地评估通常不太准确，除非本地存储了远程表的统计信息。诊断外部表执行 ANALYZE 命令可以更新本地统计，该命令会对远程表执行扫描并计算和存储统计信息。保存本地统计可以有效减少访问远程表时的额外成本，但是如果远程表的更新很频繁，本地统计很快就会失效。
远程执行选项

默认情况下，只有 WHERE 子句中的内置操作符和函数可能会在远程服务器中执行，非内置函数在返回数据之后在本地进行处理。如果远程服务器中也存在这些函数，并且可以产生相同的结果，在远程服务器中执行可以提高查询的性能。这个行为可以通过以下选项进行控制：

    extensions，指定一个 PostgreSQL 扩展模块名称的列表，这些模块在本地和远程服务器中都需要安装并且版本兼容。这些模块中的 IMMUTABLE 函数和操作符可能发送到远程服务器执行。该选项只能在外部服务器级别进行设置，不支持表级设置。
    fetch_size，指定每个批次返回的行数。该选项支持外部表和外部服务器级别的设置，表级设置优先级更高。默认值为 100。

更新操作选项

默认情况下，所有的外部表都支持 UPDATE 语句。postgres_fdw 通过以下选项控制该行为：

    updatable，指定 postgres_fdw 是否允许外部表的 INSERT、UPDATE 以及 DELETE 操作。该选项支持外部表和外部服务器级别的设置，表级设置优先级更高。默认值为 true。

显然，如果远程表本身不允许更新，UPDATE 语句肯定会返回错误。该选项主要用于本地检查，如果不允许更新就会返回错误，而不需要查询远程服务器。本地数据库中的 information_schema 视图将会根据该选项显示外部表是否可更新，而不会检查远程服务器。
导入选项

postgres_fdw 支持使用 IMPORT FOREIGN SCHEMA 语句为指定远程模式中的所有表或视图创建外部表。如果远程表中的字段使用了自定义数据类型，本地服务器中也必须创建同名的兼容类型。

这种通过 IMPORT FOREIGN SCHEMA 导入外部模式的行为可以通过以下选项进行控制：

    import_collate，控制导入外部表时是否包含字段的 COLLATE 属性，默认值为 true。如果远程服务器和本地服务器之间的排序规则名称不同时，可以将该选项关闭。
    import_default，控制导入外部表时是否包含字段的 DEFAULT 表达式，默认值为 false。 如果启用了该选项，需要注意本地服务器上的默认值计算结果可能与远程服务器不同；例如 nextval() 函数的结果。如果默认值表达式使用了本地服务器中不存在的函数或者操作符，所有导入操作都将失败。
    import_not_null，控制导入外部表时是否包含字段的 NOT NULL 约束，默认值为 true。

除了 NOT NULL 之外的其他约束不会从远程表中导入到外部表定义。虽然 PostgreSQL 支持外部表的 CHECK 约束，但是不会自动导入该约束，因为本地和远程服务器中的约束表达式的结果可能不一致。任何 CHECK 约束行为的不一致性都可能导致难以发现的查询优化问题如果想要导入 CHECK 约束，必须手动执行操作，并且仔细验证语义的一致性。

分区结构中的分区不会被导入；分区表会导入，除非它又是其他表的分区。因为通过分区表（分区层级中的根节点）可以访问所有的数据，这种方式可以访问表中的所有数据，不需要创建额外的对象。
连接管理

postgres_fdw 在第一次使用外部表时建立一个远程服务器连接，然后在同一个会话中保持并重用该连接。如果使用了多个用户映射访问远程服务器，每个用户映射都会创建一个连接。
事务管理

如果查询过程中引用了任何远程表，并且没有打开与本地事务对应的远程事务时，postgres_fdw 会在远程服务器中打开一个事务。远程事务随着本地事务一起提交或者中断。保存点的管理与此类似，也会在远程服务器中创建相应的保存点。

如果本地事务使用了 SERIALIZABLE 隔离级别，远程事务也使用 SERIALIZABLE 隔离级别；否则远程事务使用 REPEATABLE READ 隔离级别。这个设置可以确保一个查询扫描多个远程表时，可以获得快照一致性的结果。在同一个事务中，多次查询远程表将会返回相同的结果，即使其他事务修改了远程表中的数据。这个行为对于本地事务使用 SERIALIZABLE 或者 REPEATABLE READ 隔离级别时是预期行为，但是对于 READ COMMITTED 隔离级别可能有些奇怪；将来的 PostgreSQL 版本中可能会修改这些规则。

目前，postgres_fdw 还不支持远程事务的两阶段提交。
远程查询优化

postgres_fdw 可以尝试通过优化远程查询减少网络传输的数据量。这个功能通过将 WHERE 子句发送到远程服务器执行，以及不返回查询不需要的字段来实现。为了降低错误执行查询的风险，只有当 WHERE 子句中只涉及内置或者外部服务器 extensions 选项指定的模块中的数据类型、操作符和函数时才会发送到远程服务器。同时，操作符和函数必须指定 IMMUTABLE 属性。

对于 UPDATE 或者 DELETE 操作，如果不存在无法发送的 WHERE 子句、本地连接查询、本地行级 BEFORE 或者 AFTER 触发器、目标表上的存储计算列、父级视图上的 CHECK OPTION 约束，postgres_fdw 可以尝试将整个语句发送到远程服务器执行。对于 UPDATE，赋值表达式中只能使用内置数据类型、IMMUTABLE 操作符或者函数，从而减少错误执行的风险。

如果 postgres_fdw 遇到了同一个远程服务器上的外部表连接查询，会将整个连接操作发送到远程服务器，除非它认为单独返回每个表的效率更高，或者表的引用涉及了不同的用户映射。发送 JOIN 子句时，采用和上述 WHERE 子句相同的策略。

实际发送到远程服务器的查询可以通过 EXPLAIN VERBOSE 语句进行查看。
远程查询执行环境

对于 postgres_fdw 打开的远程会话，search_path 参数被设置为 pg_catalog，因此默认只能看见内置的对象，除非使用模式名限定。这种行为对于postgres_fdw 提交的查询而言没有问题，因为它总是会指定模式名。不过，它可能对远程表上的触发器函数或者规则函数的执行带来问题。例如，当远程表是一个视图时，该视图中的任何函数都会在该搜索路径中查找，结果可能是不存在。推荐对于这些函数使用时加上模式名限定，或者在创建函数时使用 SET search_path 选项设置搜索路径。

另外，postgres_fdw 还会在建立远程会话时对以下参数进行设置：

    TimeZone 设置为 UTC；
    DateStyle 设置为 ISO；
    IntervalStyle 设置为 postgres；
    extra_float_digits 设置为 （远程服务器为 PostgreSQL 9.0 以上版本） 或者 2（更早版本）。

这些参数通常不会像 search_path 一样导致问题，但同样可以使用函数的 SET 选项进行设置。

不推荐通过会话级别的设置覆盖这些参数的默认值，这样可能导致 postgres_fdw 运行错误。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁