| PostgreSQL 9.5.3 文档 | |||
|---|---|---|---|
| 上一页 | 上一级 | 附录 F. 额外提供的模块 | 下一页 |
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目前已经能够容忍在需要时执行数据类型转换， 但是由于远程服务器解释WHERE子句时可能会与本地服务器有所不同， 所以当类型或排序规则不匹配时可能会发生奇怪的语义异常。
注意一个外部表可以声明比底层的远程表较少的列，或者使用一种不同的列序。 与远程表列的匹配是通过名字而不是位置进行的。
一个使用postgres_fdw外部数据封装器的外部服务器可以使用和 libpq在连接字符串中接受的相同的选项， 如第 31.1.2 节所述，不过不允许这些选项：
user和password（在用户映射中指定这些值）
client_encoding（这是从本地服务器编码自动设置的）
fallback_application_name（总是设置为postgres_fdw）
只有超级用户可以在不经过口令认证的情况下连接到外部服务器， 因此应总是为属于非超级用户的用户映射指定password选项。
这些选项可以被用来控制使用在被发送到远程PostgreSQL 服务器的 SQL 语句中使用的名称。当一个外部表被使用不同于底层远程表的名称创建时， 就需要这些选项。
这个选项给出用在远程服务器之上的外部表的模式名称，它可以为一个外部表指定。 如果忽略该选项，则使用外部表的模式名。
这个选项给出用在远程服务器上的外部表的表名，它可以为一个外部表指定。 如果忽略该选项，则使用外部表的名称。
这个选项给出用在远程服务器上的列的列名，它可以为一个外部表的一个列指定。 如果忽略该选项，则使用列的名称。
postgres_fdw通过在远程服务器上执行查询来检索远程数据， 因此理想的扫描一个外部表的估计代价应该是在远程服务器上完成它的花销，外加一些通信开销。 得到这样一个估计的最可靠的方法是询问远程服务器并加上一些通信开销 — 但是对于简单查询， 不值得为获得一个代价估计而额外使用一次远程查询。因此postgres_fdw 提供了下列选项来控制如何完成代价估计：
这个选项控制postgres_fdw是否发出远程EXPLAIN 命令来获得代价估计，它可以为一个外部表或一个外部服务器指定。 一个外部表的设置会覆盖它的服务器的任何设置，但是只用于这个表。 默认值是false。
这个选项是添加到那个服务器上任何外部表扫描的估计启动代价的数字值， 可以为一个外部服务器指定。 这表示建立一个连接、在远端解析和规划查询的额外负荷等。默认值是100。
这个选项是被用作那个服务器上外部表扫描的每元组额外代价，它可以为一个外部服务器指定。 这表示在服务器之间数据传输的额外负荷。 你可以增加或减少这个数来反映到远程服务器更高或更低的网络延迟。默认值是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是更新本地统计数据的方法， 这将执行远程表的一次扫描并接着计算和存储统计数据，就好像表在本地一样。 保留本地统计数据可能是减少远程表的每查询规划开销的有用方法 — 但是如果远程表被频繁的更新，本地统计数据将很快就被废弃。
默认情况下，所有使用postgres_fdw的外部表都被假定是可更新的。 这可以使用下列选项覆盖：
这个选项控制postgres_fdw是否允许使用INSERT、 UPDATE和DELETE命令修改外部表。 它可以为一个外部表或一个外部服务器指定。表级别的选项会覆盖服务器级别的选项。 默认值是true。
当然，如果远程表实际上不是可更新的，将产生一个错误。 这个选项的使用主要是允许在不查询远程服务器的情况下在本地抛出错误。 但是要注意information_schema视图会根据这个选项的设置报告一个 postgres_fdw外部表是可更新的（或者不可更新）， 而不需要远程服务器的任何检查。
postgres_fdw可以使用IMPORT FOREIGN SCHEMA导入外部表定义。 此命令在本地服务器上创建匹配远程服务器上存在的表或视图的外部表定义。 如果要导入的远程表有用户定义数据类型的列，则本地服务器必须具有相同名称的兼容类型。
可以使用以下选项自定义导入行为 (在IMPORT FOREIGN SCHEMA命令中给出):
此选项控制列COLLATE选项是否包含在从外部服务器导入的外表的定义中。 默认值为true。如果远程服务器具有不同于本地服务器的排序规则名称， 则可能需要将其关闭，这可能是在不同操作系统上运行的情况。
此选项控制列DEFAULT表达式是否包含在从外部服务器导入的外表的定义中。
默认值为false。如果启用此选项，
请注意在本地服务器上和在远程服务器上计算的默认值可能不同；nextval()
是常见的问题来源。如果导入的默认表达式使用本地不存在的函数或运算符，
则IMPORT将完全失败。
此选项控制列NOT NULL约束是否包含在从外部服务器导入的外表的定义中。 默认值为true。
注意，除了NOT NULL之外的约束将永远不会从远程表中导入。 虽然PostgreSQL确实支持外部表上的CHECK约束， 但是没有自动导入这些约束的规定，因为约束表达式可能在本地和远程服务器上进行不同的计算。 在CHECK约束的行为中的任何这种不一致性可能导致查询优化中难以检测的错误。 因此，如果您希望导入CHECK约束，则必须手动进行，并且您应仔细验证每个约束的语义。 有关对外部表上CHECK约束的处理的更多详细信息， 请参阅CREATE FOREIGN TABLE。
postgres_fdw在使用与外部服务器关联的外部表第一个查询期间， 建立一个到外部服务器的连接。这个连接会被保持，并被重用于同一个会话中的后续查询。 但是，如果使用了多个用户标识（用户映射）来访问外部服务器，则为每个用户映射建立连接。
在一个引用外部服务器上任何远程表的查询期间，如果还没有打开对应于当前本地事务的远程事务， 则postgres_fdw在远程服务器上打开事务。当本地事务提交或中止时， 远程事务也被提交或中止。保存点也相似地通过创建相应的远程保存点来管理。
当本地事务为SERIALIZABLE隔离级别时，远程事务使用SERIALIZABLE 隔离级别；否则它使用REPEATABLE READ隔离级别。 如果一个查询在远程服务器上执行多表查询，这种选择保证它将为所有扫描得到快照一致的结果。 结果是在单个事务中的连续查询将会看到来自远程服务器的相同数据， 即便由于其他活动在远程服务器上发生了其他并发更新。如果本地事务使用 SERIALIZABLE或REPEATABLE READ隔离级别，这种行为也是可以预期的， 但是对于一个READ COMMITTED本地事务它是奇怪的。 未来的PostgreSQL发布可能会修改这些规则。
postgres_fdw尝试优化远程查询来减少从外部服务器传来的数据量。 这可以通过将查询WHERE子句发送到远程服务器执行， 并且不检索当前查询不需要的表列来完成的。为了减少查询被错误执行的风险， 除非它们仅使用内置的数据类型、操作符和函数，否则WHERE子句不会发送到远程服务器。 子句中的操作符和函数也必须是IMMUTABLE。
实际被发送到远程服务器执行的查询可以使用EXPLAIN VERBOSE来检查。
在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发生故障。
postgres_fdw能够与最老是PostgreSQL 8.3 的远程服务器一起使用。 只读能力则最低可以在 8.1 中使用。但是一个限制是如果不变的内建函数和操作符出现在一个外部表的 WHERE子句中，postgres_fdw通常假定将它们发送到远程服务器执行是安全的。 因此，远程服务器的发布之后添加的内置函数可能被发送给它执行，导致 "function does not exist"或一个类似的错误。这类错误可以通过重写查询来解决， 例如在具有OFFSET 0作为优化墙的子SELECT 中嵌入外部表引用，并将有问题的函数或操作符放在子SELECT的外面。。
这里是一个用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优于手动构建外部表定义。
Shigeru Hanada <shigeru.hanada@gmail.com>