F.33. postgres_fdw

postgres_fdw 模块提供了外部数据包装器 postgres_fdw ，它可以被用来访问存储在外部 PostgreSQL 服务器中的数据。

这个模块提供的功能大体上覆盖了较老的 dblink 模块的功能。但是 postgres_fdw 提供了更透明且更兼容标准的语法来访问远程表，并且可以在很多情况下给出更好的性能。

要使用 postgres_fdw 来为远程访问做准备：

1. 使用 CREATE EXTENSION 来安装 postgres_fdw 扩展。

2. 使用 CREATE SERVER 创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了 user 和 password 之外的连接信息作为该服务器对象的选项。

3. 使用 CREATE USER MAPPING 创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的 user 和 password 选项。

4. 为每一个你想访问的远程表使用 CREATE FOREIGN TABLE 或者 IMPORT FOREIGN SCHEMA 创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。

   现在你只需要从一个外部表 SELECT 来访问存储在它的底层的远程表中的数据。你也可以使用 INSERT 、 UPDATE 或 DELETE 修改远程表（当然，你在你的用户映射中已经指定的远程用户必须具有做这些事情的权限）。

   注意当前 postgres_fdw 缺少对于带 ON CONFLICT DO UPDATE 子句的 INSERT 语句的支持。不过，它支持 ON CONFLICT DO NOTHING 子句，已提供的唯一索引推断说明会被省略。

   我们通常推荐一个外部表的列被声明为与被引用的远程表列完全相同的数据类型和排序规则（如果可用）。尽管 postgres_fdw 目前已经能够容忍在需要时执行数据类型转换，但是当类型或排序规则不匹配时可能会发生奇怪的语义异常，因为远程服务器解释 WHERE 子句时可能会与本地服务器有所不同。

   注意一个外部表可以被声明比底层的远程表较少的列，或者使用一种不同的列序。与远程表的列匹配是通过名字而不是位置进行的。

   F.33.1. postgres_fdw 的 FDW 选项

   F.33.1.1. 连接选项

   一个使用 postgres_fdw 外部数据包装器的外部服务器可以使用和 libpq 在连接字符串中能接受的选项，如 第 32.1.2 节 所述，不过不允许这些选项：

   • user 和 password （应该在用户映射中指定这些）

   • client_encoding （这是自动从本地服务器编码设置）

   • fallback_application_name （总是设置为 postgres_fdw ）

     只有超级用户可以在不经过口令认证的情况下连接到外部服务器，因此应总是为属于非超级用户的用户映射指定 password 选项。

   F.33.1.2. 对象名称选项

   这些选项可以被用来控制使用在被发送到远程 PostgreSQL 服务器的 SQL 语句中使用的名称。当一个外部表被使用不同于底层远程表的名称创建时，就需要这些选项。

   schema_name

   这个选项给出用在远程服务器之上的外部表的模式名称，它可以为一个外部表指定。如果这个选项被忽略，该外部表的模式名称将被使用。

   table_name

   这个选项给出用在远程服务器上的外部表给出表名，它可以为一个外部表指定。如果这个选项被忽略，该外部表的名字将被使用。

   column_name

   这个选项给出用在远程服务器上列的列名，它可以为一个外部表的一个列指定。如果这个选项被忽略，该列的名字将被使用。

   F.33.1.3. 代价估计选项

   postgres_fdw 通过在远程服务器上执行查询来检索远程数据，因此理想的扫描一个外部表的估计代价应该是在远程服务器上完成它的花销，外加一些通信开销。得到这样一个估计的最可靠的方法是询问远程服务器并加上一些通信开销 — 但是对于简单查询，不值得为获得一个代价估计而额外使用一次远程查询。因此 postgres_fdw 提供了下列选项来控制如何完成代价估计：

   use_remote_estimate

   这个选项控制 postgres_fdw 是否发出 EXPLAIN 命令来获得代价估计，它可以为一个外部表或一个外部服务器指定。一个外部表的设置会覆盖它的服务器的任何设置，但是只用于这个表。默认值是 false 。

   fdw_startup_cost

   这个选项是一个要被加到那个服务器上所有外部表扫描的估计启动代价的数字值。这表示建立一个连接、在远端解析和规查询的额外负荷等。默认值是 100 。

   fdw_tuple_cost

   这个选项是一个数字值，它被用作那个服务器上外部表扫描的每元组额外代价，它可以为一个外部服务器指定。这表示在服务器之间数据传输的额外负荷。你可以增加或减少这个数来反映到远程服务器更高或更低的网络延迟。默认值是 0.01 。

   当 use_remote_estimate 为真时， postgres_fdw 从远程服务器获得行计数和代价估计，然后在代价估计上加上 fdw_startup_cost 和 fdw_tuple_cost 。当 use_remote_estimate 为假时， postgres_fdw 执行本地行计数和代价估计，并且接着在代价估计上加上 fdw_startup_cost 和 fdw_tuple_cost 。这种本地估计不会很准确，除非有远程表统计数据的本地拷贝可用。在外部表上运行 ANALYZE 是更新本地统计数据的方法，这将执行远程表的一次扫描并接着计算和存储统计数据，就好像表在本地一样。保留本地统计数据可能是一种有用的方法来减少一个远程表的预查询规划负荷 — 但是如果远程表被频繁更新，本地统计数据将很快就被废弃。

   F.33.1.4. 远程执行选项

   默认情况下，只有使用了内建操作符和函数的 WHERE 子句才会被考虑在远程服务器上执行。涉及非内建函数的子句将会在取完行后在本地进行检查。如果这类函数在远程服务器上可用并且可以用来产生和本地执行时一样的结果，则可以通过将这种 WHERE 子句发送到远程执行来提高性能。可以用下面的选项控制这种行为：

   extensions

   这个选项是一个用逗号分隔的已安装的 PostgreSQL 扩展名称列表，这些扩展在本地和远程服务器上具有兼容的版本。属于一个该列表中扩展的 immutable 函数和操作符将被考虑转移到远程服务器上执行。这个选项只能为外部服务器指定，无法逐个表指定。

   在使用 extensions 选项时， 用户应该负责确保 列出的扩展在本地和远程服务器上都存在且保持一致。否则，远程查询可能失败或者行为异常。

   fetch_size

   这个选项指定在每次获取行的操作中 postgres_fdw 应该得到的行数。可以为一个外部表或者外部服务器指定这个选项。在表上指定的选项将会覆盖在服务器级别上指定的选项。默认值为 100 。

   F.33.1.5. 可更新性选项

   默认情况下，所有使用 postgres_fdw 的外部表都被假定是可更新的。这可以使用下列选项覆盖：

   updatable

   这个选项控制 postgres_fdw 是否允许外部表被使用 INSERT 、 UPDATE 和 DELETE 命令更新。它可以为一个外部表或一个外部服务器指定。一个表级选项会覆盖一个服务器级选项。默认值是 true 。

   当然，如果远程表实际上并非可更新的，将产生一个错误。这个选项的使用主要是允许在不查询远程服务器的情况下在本地抛出错误。但是要注意 information_schema 视图会根据这个选项的设置报告一个 postgres_fdw 外部表是可更新的（或者不可更新），而不需要远程服务器的任何检查。

   F.33.1.6. 导入选项

   postgres_fdw 能使用 IMPORT FOREIGN SCHEMA 导入外部表定义。这个命令会在本地服务器上创建外部表定义，这个定义能匹配存在于远程服务器上的表或者视图。如果要被导入的远程表有用户自定义数据类型的列，本地服务器上也必须具有相同名称的兼容类型。

   导入行为可以用下列选项自定义（在 IMPORT FOREIGN SCHEMA 命令中给出）：

   import_collate

   这个选项控制是否在从外部服务器导入的外部表定义中包括列的 COLLATE 选项。默认是 true 。如果远程服务器具有和本地服务器不同的排序规则名集合，可能需要关闭这个选项，在不同的操作系统上运行时很可能就是这样。

   import_default

   这个选项控制是否在从外部服务器导入的外部表定义中包括列的 DEFAULT 表达式。默认是 false 。如果启用这个选项，要当心在远程服务器和本地服务器上计算表达式的方式不同， nextval() 常会导致这类问题。如果导入的默认值表达式使用了一个本地不存在的函数或者操作符， IMPORT 将整个失败。

   import_not_null

   这个选项控制是否在从外部服务器导入的外部表定义中包括列的 NOT NULL 约束。默认是 true 。

   注意除 NOT NULL 之外的约束将不会从远程表中导入。虽然 PostgreSQL 确实支持外部表上的 CHECK 约束，但不会自动导入它们，因为存在本地和远程服务器计算约束表达式方式不同的风险。 CHECK 约束中的任何这类不一致都可能导致查询优化中很难检测的错误。因此，如果你希望导入 CHECK 约束，你必须手工来做，并且你应该仔细地验证每一个这种约束的语义。有关处理外部表上 CHECK 约束的更多细节，请见 CREATE FOREIGN TABLE 。

   F.33.2. 连接管理

   postgres_fdw 在第一个使用关联到外部服务器的外部表的查询期间建立一个到外部服务器的连接。这个连接会被保持，并被重用于同一个会话中的后续查询。但是，如果使用了多个用户实体（用户映射）来访问外部服务器，会为每一个用户映射建立一个连接。

   F.33.3. 事务管理

   在一个引用外部服务器上任何远程表的查询期间，如果还没有根据当前的本地事务打开一个远程事务， postgres_fdw 将在远程服务器上打开一个事务。当本地事务提交或中止时，远程事务也被提交或中止。保存点也相似地采用创建相应的远程保存点来管理。

   当本地事务为 SERIALIZABLE 隔离级别时，远程事务使用 SERIALIZABLE 隔离级别；否则它使用 REPEATABLE READ 隔离级别。如果一个查询在远程服务器上执行多个表查询，这种选择保证它将为所有扫描得到快照一致的结果。一种后果是在单一事务中的后继查询将会看到来自远程服务器的相同数据，即便由于其他活动在远程服务器上发生了其他并发更新。如果本地事务使用 SERIALIZABLE 或 REPEATABLE READ 隔离级别，这种行为也是可以预期的，但是对于一个 READ COMMITTED 本地事务它是奇怪的。一个未来的 PostgreSQL 发布可能会修改这些规则。

   F.33.4. 远程查询优化

   postgres_fdw 尝试优化远程查询来减少从外部服务器传来的数据量。这可以通过把查询的 WHERE 子句发送给远程服务器执行来完成，并且还可以不检索当前查询不需要的表列。为了降低查询被误执行的风险，除非 WHERE 子句使用的数据类型、操作符和函数都是内建的或者属于列在该外部服务器的 extensions 选项中的一个扩展，将不会把 WHERE 子句发送到远程服务器。这些子句中的操作符合函数也必须是 IMMUTABLE 。对于 UPDATE 或者 DELETE 查询，如果没有不能发送给远程服务器的 WHERE 子句、没有本地连接并且目标表上没有本地的行级 BEFORE 或 AFTER 触发器， postgres_fdw 会尝试通过将整个查询发送给远程服务器来优化查询的执行。在 UPDATE 中，赋值给目标列的表达式只能使用内建数据类型、 IMMUTABLE 操作符或者 IMMUTABLE 操作符，这样能降低查询被误执行的风险。

   当 postgres_fdw 碰到同一个外部服务器上的外部表之间的连接时，它会把整个连接发送给外部服务器，除非由于某些原因它认为逐个从每一个表取得行的效率更高或者涉及的表引用属于不同的用户映射。在发送 JOIN 子句时，它也会采取和上述 WHERE 子句相同的预防措施。

   实际被发送到远程服务器执行的查询可以使用 EXPLAIN VERBOSE 来检查。

   F.33.5. 远程查询执行环境

   在 postgres_fdw 开启的远程会话中， search_path 参数只被设置为 pg_catalog ，因此只有内建对象可以在无模式限定时可见。这对于 postgres_fdw 本身产生的查询来说不是问题，因为它总是会提供这样的限定。不过，这可能会对在远程服务器上通过触发器或者远程表上的规则执行的函数带来灾难。例如，如果一个远程表实际是一个视图，任何在该视图中使用的函数都将被在这个受限的搜索路径中执行。我们推荐在这类函数中用模式限定所有名称，或者为这类函数附着 SET search_path 选项（见 CREATE FUNCTION ）来建立它们所期望的搜索路径环境。

   postgres_fdw 同样也为参数 TimeZone 、 DateStyle 、 IntervalStyle 和 extra_float_digits 建立远程会话设置。这些不如 search_path 有那么多问题，但是如果需要也可以使用函数 SET 选项来处理。

   我们 不 推荐通过更改这些参数的会话级设置来推翻这种行为，这很可能会导致 postgres_fdw 故障。

   F.33.6. 跨版本兼容性

   postgres_fdw 能够与最老是 PostgreSQL 8.3 的远程服务器一起使用。只读能力则最低可以在 8.1 中使用。但是一个限制是 postgres_fdw 通常假定不变的内建函数和操作符是安全的，如果它们出现在一个外部表的 WHERE 子句中，它们可以发送给远程服务器执行。因此，由于一个由于远程服务器的发布可能被发送给它来执行而被增加的内建函数，会导致 "function does not exist" 或一个类似的错误。这类错误可以通过重写查询来解决，例如通过嵌入在一个带 OFFSET 0 的子 SELECT 中引用的外部表作为一种优化墙，并且把出问题的函数或操作符放在子 SELECT 的外部。

   F.33.7. 例子

   这里是一个用 postgres_fdw 创建外部表的例子。首先安装该扩展：

   CREATE EXTENSION postgres_fdw;

   然后使用 CREATE SERVER 创建一个外部服务器。在这个例子中我们希望连接到一个位于主机 192.83.123.89 上并且监听 5432 端口的 PostgreSQL 服务器。在该远程服务器上要连接的数据库名为 foreign_db ：

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

   需要用 CREATE USER MAPPING 定义一个用户映射来标识在远程服务器上使用哪个角色：

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

   现在就可以使用 CREATE FOREIGN TABLE 创建外部表了。在这个例子中我们希望访问远程服务器上名为 some_schema.some_table 的表。它的本地名称是 foreign_table ：

   CREATE FOREIGN TABLE foreign_table (
           id integer NOT NULL,
           data text
           SERVER foreign_server
           OPTIONS (schema_name 'some_schema', table_name 'some_table');

   CREATE FOREIGN TABLE 中声明的列数据类型和其他性质必须要匹配实际的远程表。列名也必须匹配，不过也可以为个别列附上 column_name 选项以表示它们在远程服务器上对应哪个列。在很多情况中，要手工构造外部表定义，使用 IMPORT FOREIGN SCHEMA 会更好。

   F.33.8. 作者

   Shigeru Hanada < shigeru.hanada@gmail.com >