阅读(452) 赞(11)

PostgreSQL postgres_fdw

2021-08-24 15:40:10 更新

F.33.1. postgres_fdw 的 FDW 选项
F.33.2. 连接管理
F.33.3. 事务管理
F.33.4. 远程查询优化
F.33.5. 远程查询执行环境
F.33.6. 跨版本兼容性
F.33.7. 例子

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

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

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

使用CREATE EXTENSION来安装postgres_fdw扩展。
使用CREATE SERVER创建一个外部服务器对象，它用来表示你想连接的每一个远程数据库。指定除了user和password之外的连接信息作为该服务器对象的选项。
使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。
为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。外部表的列必须匹配被引用的远程表。但是，如果你在外部表对象的选项中指定了正确的远程名称，你可以使用不同于远程表的表名和/或列名。

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

注意当前postgres_fdw缺少对于带ON CONFLICT DO UPDATE子句的INSERT语句的支持。不过，它支持ON CONFLICT DO NOTHING子句，已提供的唯一索引推断说明会被省略。另请注意postgres_fdw支持 UPDATE语句在分区表上执行的行移动调用，但是它目前不处理这样的情况：选择将移动行插入到的远程分区也是稍后将更新的UPDATE目标分区。

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

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

F.33.1. postgres_fdw 的 FDW 选项

F.33.1.1. 连接选项

一个使用postgres_fdw外部数据包装器的外部服务器可以使用和libpq在连接字符串中能接受的选项，如第 33.1.2 节所述，除了这些选项不被允许或有特殊处理：

user、password和sslpassword（应该在用户映射中指定这些，或者使用服务文件）
client_encoding（这是自动从本地服务器编码设置）
fallback_application_name（总是设置为postgres_fdw）
sslkey和sslcert - 这些可能出现在either or both连接和用户映射中。如果两者都存在，则用户映射设置会覆盖连接设置。

只有超级用户可以使用sslcert或sslkey设置创建或修改用户映射。

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

超级用户可以通过设置用户映射选项password_required 'false'在每个用户映射的基础上覆盖此检查，例如，

ALTER USER MAPPING FOR some_non_superuser SERVER loopback_nopw
OPTIONS (ADD password_required 'false');

为了防止非特权用户利用正在运行的 postgres 服务器升级到超级用户权限的 unix 用户的身份验证权限，只有超级用户可以在用户映射上设置此选项。

需要注意确保这不允许映射用户能够根据 CVE-2007-3278 和 CVE-2007-6601 作为超级用户连接到映射数据库。不要在public角色上设置password_required=false。请记住，映射的用户可能会使用 postgres 服务器运行的系统用户的 unix 主目录中的任何客户端证书， .pgpass、 .pg_service.conf等。他们还可以使用由诸如peer或ident身份验证等身份验证模式授予的任何信任关系。

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发布可能会修改这些规则。

请注意postgres_fdw当前不支持为两阶段提交准备远程事务。

F.33.4. 远程查询优化

postgres_fdw尝试优化远程查询来减少从外部服务器传来的数据量。这可以通过把查询的WHERE子句发送给远程服务器执行来完成，并且还可以不检索当前查询不需要的表列。为了降低查询被误执行的风险，除非WHERE子句使用的数据类型、操作符和函数都是内建的或者属于列在该外部服务器的extensions选项中的一个扩展，将不会把 WHERE子句发送到远程服务器。这些子句中的操作符合函数也必须是IMMUTABLE。对于UPDATE或者DELETE查询，如果没有不能发送给远程服务器的WHERE子句、没有查询的本地连接、目标表上没有本地的行级BEFORE或 AFTER触发器或存储生成的列，并且没有来自父视图的CHECK OPTION约束，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设置为UTC
DateStyle设置为ISO
IntervalStyle设置为postgres
对于远程服务器9.0和更新版本，extra_float_digits 设置为3，并且针对更老版本设置为2

这些不如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 会更好。

← PostgreSQL pg_visibility

PostgreSQL seg →