postgres-fdw.zh.md

## F.35. postgres_fdw

[F.35.1. FDW Options of postgres_fdw](postgres-fdw.html#id-1.11.7.44.11)[F.35.2. Functions](postgres-fdw.html#id-1.11.7.44.12)[F.35.3. Connection Management](postgres-fdw.html#id-1.11.7.44.13)[F.35.4. Transaction Management](postgres-fdw.html#id-1.11.7.44.14)[F.35.5. Remote Query Optimization](postgres-fdw.html#id-1.11.7.44.15)[F.35.6. Remote Query Execution Environment](postgres-fdw.html#id-1.11.7.44.16)[F.35.7. Cross-Version Compatibility](postgres-fdw.html#id-1.11.7.44.17)[F.35.8. Examples](postgres-fdw.html#id-1.11.7.44.18)[F.35.9. Author](postgres-fdw.html#id-1.11.7.44.19)

[](<>)

The`postgres_fdw`module provides the foreign-data wrapper`postgres_fdw`, which can be used to access data stored in external PostgreSQL servers.

The functionality provided by this module overlaps substantially with the functionality of the older[dblink](dblink.html)module. But`postgres_fdw`provides more transparent and standards-compliant syntax for accessing remote tables, and can give better performance in many cases.

To prepare for remote access using`postgres_fdw`:

1.  Install the`postgres_fdw`扩展使用[创建扩展](sql-createextension.html).

2.  创建一个外部服务器对象，使用[创建服务器](sql-createserver.html), 表示您要连接的每个远程数据库。指定连接信息，除了`用户`和`密码`，作为服务器对象的选项。

3.  创建用户映射，使用[创建用户映射](sql-createusermapping.html)，对于您希望允许访问每个外部服务器的每个数据库用户。指定要用作的远程用户名和密码`用户`和`密码`用户映射的选项。

4.  创建一个外部表，使用[创建外表](sql-createforeigntable.html)或者[导入国外模式](sql-importforeignschema.html), 对于您要访问的每个远程表。外部表的列必须与引用的远程表匹配。但是，如果您将正确的远程名称指定为外部表对象的选项，则可以使用与远程表不同的表和/或列名称。

    现在你只需要`选择`从外部表访问存储在其基础远程表中的数据。您还可以使用修改远程表`插入`,`更新`,`删除`， 或者`截短`.（当然，您在用户映射中指定的远程用户必须具有执行这些操作的权限。）

    请注意，`只要`中指定的选项`选择`,`更新`,`删除`或者`截短`访问或修改远程表时无效。

    注意`postgres_fdw`目前缺乏对`插入`带有`冲突时做更新`条款。然而`在冲突中什么都不做`如果省略了唯一索引推断规范，则支持子句。另请注意`postgres_fdw`支持由调用的行移动`使现代化`语句在分区表上执行，但它当前不处理选择在其中插入移动行的远程分区也是`使现代化`将在同一命令中的其他位置更新的目标分区。

    通常建议使用与远程表的引用列完全相同的数据类型和排序规则（如果适用）来声明外部表的列。虽然`博士后`目前对于在需要时执行数据类型转换相当宽容，当类型或排序规则不匹配时，可能会出现令人惊讶的语义异常，因为远程服务器对查询条件的解释与本地服务器不同。

    请注意，与基础远程表相比，外部表可以使用更少的列或不同的列顺序来声明。列与远程表的匹配是按名称，而不是按位置。

### F.35.1。博士后的FDW选择\_fdw

#### F.35.1.1。连接选项

使用`博士后`外部数据包装可以具有libpq在连接字符串中接受的相同选项，如中所述[第34.1.2节](libpq-connect.html#LIBPQ-PARAMKEYWORDS)，但这些选项不允许或有特殊处理：

-   `使用者`,`暗语`和`sslpassword`（而是在用户映射中指定这些，或使用服务文件）

-   `客户机编码`（这是根据本地服务器编码自动设置的）

-   `回退应用程序名称`（始终设置为`博士后`)

-   `sslkey`和`sslcert`- 这些可能出现在*一个或两个*一个连接和一个用户映射。如果两者都存在，则用户映射设置将覆盖连接设置。

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

    只有超级用户可以在没有密码验证的情况下连接到外部服务器，所以总是指定`密码`属于非超级用户的用户映射选项。

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


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

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

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

#### F.35.1.2. Object Name Options

These options can be used to control the names used in SQL statements sent to the remote PostgreSQL server. These options are needed when a foreign table is created with names different from the underlying remote table's names.

`schema_name`

This option, which can be specified for a foreign table, gives the schema name to use for the foreign table on the remote server. If this option is omitted, the name of the foreign table's schema is used.

`table_name`

This option, which can be specified for a foreign table, gives the table name to use for the foreign table on the remote server. If this option is omitted, the foreign table's name is used.

`column_name`

This option, which can be specified for a column of a foreign table, gives the column name to use for the column on the remote server. If this option is omitted, the column's name is used.

#### F.35.1.3. Cost Estimation Options

`postgres_fdw`retrieves remote data by executing queries against remote servers, so ideally the estimated cost of scanning a foreign table should be whatever it costs to be done on the remote server, plus some overhead for communication. The most reliable way to get such an estimate is to ask the remote server and then add something for overhead — but for simple queries, it may not be worth the cost of an additional remote query to get a cost estimate. So`postgres_fdw`provides the following options to control how cost estimation is done:

`use_remote_estimate`

This option, which can be specified for a foreign table or a foreign server, controls whether`postgres_fdw`issues remote`EXPLAIN`commands to obtain cost estimates. A setting for a foreign table overrides any setting for its server, but only for that table. The default is`false`.

`fdw_startup_cost`

This option, which can be specified for a foreign server, is a floating point value that is added to the estimated startup cost of any foreign-table scan on that server. This represents the additional overhead of establishing a connection, parsing and planning the query on the remote side, etc. The default value is`100`.

`fdw_tuple_cost`

This option, which can be specified for a foreign server, is a floating point value that is used as extra cost per-tuple for foreign-table scans on that server. This represents the additional overhead of data transfer between servers. You might increase or decrease this number to reflect higher or lower network delay to the remote server. The default value is`0.01`.

When`use_remote_estimate`is true,`postgres_fdw`obtains row count and cost estimates from the remote server and then adds`fdw_startup_cost`and`fdw_tuple_cost`to the cost estimates. When`use_remote_estimate`is false,`postgres_fdw`performs local row count and cost estimation and then adds`fdw_startup_cost`and`fdw_tuple_cost`to the cost estimates. This local estimation is unlikely to be very accurate unless local copies of the remote table's statistics are available. Running[ANALYZE](sql-analyze.html)on the foreign table is the way to update the local statistics; this will perform a scan of the remote table and then calculate and store statistics just as though the table were local. Keeping local statistics can be a useful way to reduce per-query planning overhead for a remote table — but if the remote table is frequently updated, the local statistics will soon be obsolete.

#### F.35.1.4. Remote Execution Options

By default, only`WHERE`clauses using built-in operators and functions will be considered for execution on the remote server. Clauses involving non-built-in functions are checked locally after rows are fetched. If such functions are available on the remote server and can be relied on to produce the same results as they do locally, performance can be improved by sending such`在哪里`远程执行的子句。可以使用以下选项控制此行为：

`扩展`

此选项是以逗号分隔的 PostgreSQL 扩展名列表，这些扩展名以兼容版本安装在本地和远程服务器上。不可变且属于列出的扩展的函数和运算符将被视为可发送到远程服务器。此选项只能为外部服务器指定，不能为每个表指定。

使用时`扩展`选项，*这是用户的责任*列出的扩展在本地和远程服务器上存在并且行为相同。否则，远程查询可能会失败或行为异常。

`fetch_size`

此选项指定行数`postgres_fdw`应该进入每个提取操作。可以为外部表或外部服务器指定它。在表上指定的选项会覆盖为服务器指定的选项。默认是`100`.

`批量大小`

此选项指定行数`postgres_fdw`应该在每个插入操作中插入。可以为外部表或外部服务器指定它。在表上指定的选项会覆盖为服务器指定的选项。默认是`1`.

注意实际行数`postgres_fdw`一次插入取决于列数和提供的`批量大小`价值。批处理作为单个查询执行，而 libpq 协议（其中`postgres_fdw`用于连接远程服务器）将单个查询中的参数数量限制为 65535。当列数\* `batch_size`exceeds the limit, the`batch_size`will be adjusted to avoid an error.

#### F.35.1.5. Asynchronous Execution Options

`postgres_fdw`supports asynchronous execution, which runs multiple parts of an`Append`node concurrently rather than serially to improve performance. This execution can be controlled using the following option:

`async_capable`

This option controls whether`postgres_fdw`allows foreign tables to be scanned concurrently for asynchronous execution. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is`false`.

In order to ensure that the data being returned from a foreign server is consistent,`postgres_fdw`will only open one connection for a given foreign server and will run all queries against that server sequentially even if there are multiple foreign tables involved, unless those tables are subject to different user mappings. In such a case, it may be more performant to disable this option to eliminate the overhead associated with running queries asynchronously.

Asynchronous execution is applied even when an`Append`node contains subplan(s) executed synchronously as well as subplan(s) executed asynchronously. In such a case, if the asynchronous subplans are ones processed using`postgres_fdw`, tuples from the asynchronous subplans are not returned until after at least one synchronous subplan returns all tuples, as that subplan is executed while the asynchronous subplans are waiting for the results of asynchronous queries sent to foreign servers. This behavior might change in a future release.

#### F.35.1.6. Updatability Options

By default all foreign tables using`postgres_fdw`are assumed to be updatable. This may be overridden using the following option:

`updatable`

This option controls whether`postgres_fdw`allows foreign tables to be modified using`INSERT`,`UPDATE`and`DELETE`commands. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is`true`.

Of course, if the remote table is not in fact updatable, an error would occur anyway. Use of this option primarily allows the error to be thrown locally without querying the remote server. Note however that the`information_schema`views will report a`postgres_fdw`foreign table to be updatable (or not) according to the setting of this option, without any check of the remote server.

#### F.35.1.7. Truncatability Options

By default all foreign tables using`postgres_fdw`are assumed to be truncatable. This may be overridden using the following option:

`truncatable`

This option controls whether`postgres_fdw`allows foreign tables to be truncated using the`TRUNCATE`command. It can be specified for a foreign table or a foreign server. A table-level option overrides a server-level option. The default is`true`.

Of course, if the remote table is not in fact truncatable, an error would occur anyway. Use of this option primarily allows the error to be thrown locally without querying the remote server.

#### F.35.1.8. Importing Options

`postgres_fdw`is able to import foreign table definitions using[IMPORT FOREIGN SCHEMA](sql-importforeignschema.html). This command creates foreign table definitions on the local server that match tables or views present on the remote server. If the remote tables to be imported have columns of user-defined data types, the local server must have compatible types of the same names.

Importing behavior can be customized with the following options (given in the`IMPORT FOREIGN SCHEMA`command):

`import_collate`

This option controls whether column`COLLATE`options are included in the definitions of foreign tables imported from a foreign server. The default is`true`. You might need to turn this off if the remote server has a different set of collation names than the local server does, which is likely to be the case if it's running on a different operating system. If you do so, however, there is a very severe risk that the imported table columns' collations will not match the underlying data, resulting in anomalous query behavior.

Even when this parameter is set to`true`, importing columns whose collation is the remote server's default can be risky. They will be imported with`COLLATE "default"`, which will select the local server's default collation, which could be different.

`import_default`

This option controls whether column`DEFAULT`expressions are included in the definitions of foreign tables imported from a foreign server. The default is`false`. If you enable this option, be wary of defaults that might get computed differently on the local server than they would be on the remote server;`nextval()`is a common source of problems. The`IMPORT`will fail altogether if an imported default expression uses a function or operator that does not exist locally.

`import_generated`

This option controls whether column`GENERATED`expressions are included in the definitions of foreign tables imported from a foreign server. The default is`true`. The`IMPORT`will fail altogether if an imported generated expression uses a function or operator that does not exist locally.

`import_not_null`

This option controls whether column`NOT NULL`constraints are included in the definitions of foreign tables imported from a foreign server. The default is`true`.

Note that constraints other than`NOT NULL`will never be imported from the remote tables. Although PostgreSQL does support check constraints on foreign tables, there is no provision for importing them automatically, because of the risk that a constraint expression could evaluate differently on the local and remote servers. Any such inconsistency in the behavior of a check constraint could lead to hard-to-detect errors in query optimization. So if you wish to import check constraints, you must do so manually, and you should verify the semantics of each one carefully. For more detail about the treatment of check constraints on foreign tables, see[CREATE FOREIGN TABLE](sql-createforeigntable.html).

Tables or foreign tables which are partitions of some other table are imported only when they are explicitly specified in`LIMIT TO`clause. Otherwise they are automatically excluded from[IMPORT FOREIGN SCHEMA](sql-importforeignschema.html). Since all data can be accessed through the partitioned table which is the root of the partitioning hierarchy, importing only partitioned tables should allow access to all the data without creating extra objects.

#### F.35.1.9. Connection Management Options

By default, all connections that`postgres_fdw`establishes to foreign servers are kept open in the local session for re-use.

`keep_connections`

This option controls whether`postgres_fdw`keeps the connections to the foreign server open so that subsequent queries can re-use them. It can only be specified for a foreign server. The default is`on`. If set to`off`, all connections to this foreign server will be discarded at the end of each transaction.

### F.35.2. Functions

`postgres_fdw_get_connections(OUT server_name text, OUT valid boolean) returns setof record`

This function returns the foreign server names of all the open connections that`postgres_fdw`established from the local session to the foreign servers. It also returns whether each connection is valid or not.`false`is returned if the foreign server connection is used in the current local transaction but its foreign server or user mapping is changed or dropped (Note that server name of an invalid connection will be`NULL`if the server is dropped), and then such invalid connection will be closed at the end of that transaction.`true`is returned otherwise. If there are no open connections, no record is returned. Example usage of the function:

```
postgres=# SELECT * FROM postgres_fdw_get_connections() ORDER BY 1;
 server_name | valid
### F.35.3. Connection Management

`postgres_fdw` establishes a connection to a foreign server during the first query that uses a foreign table associated with the foreign server. By default this connection is kept and re-used for subsequent queries in the same session. This behavior can be controlled using `keep_connections` option for a foreign server. If multiple user identities (user mappings) are used to access the foreign server, a connection is established for each user mapping.

 When changing the definition of or removing a foreign server or a user mapping, the associated connections are closed. But note that if any connections are in use in the current local transaction, they are kept until the end of the transaction. Closed connections will be re-established when they are necessary by future queries using a foreign table.

 Once a connection to a foreign server has been established, it's by default kept until the local or corresponding remote session exits. To disconnect a connection explicitly, `keep_connections` option for a foreign server may be disabled, or `postgres_fdw_disconnect` and `postgres_fdw_disconnect_all` functions may be used. For example, these are useful to close connections that are no longer necessary, thereby releasing connections on the foreign server.

### F.35.4. Transaction Management

 During a query that references any remote tables on a foreign server, `postgres_fdw` opens a transaction on the remote server if one is not already open corresponding to the current local transaction. The remote transaction is committed or aborted when the local transaction commits or aborts. Savepoints are similarly managed by creating corresponding remote savepoints.

 The remote transaction uses `SERIALIZABLE` isolation level when the local transaction has `SERIALIZABLE` isolation level; otherwise it uses `REPEATABLE READ` isolation level. This choice ensures that if a query performs multiple table scans on the remote server, it will get snapshot-consistent results for all the scans. A consequence is that successive queries within a single transaction will see the same data from the remote server, even if concurrent updates are occurring on the remote server due to other activities. That behavior would be expected anyway if the local transaction uses `SERIALIZABLE` or `REPEATABLE READ` isolation level, but it might be surprising for a `READ COMMITTED` local transaction. A future PostgreSQL release might modify these rules.

 Note that it is currently not supported by `postgres_fdw` to prepare the remote transaction for two-phase commit.

### F.35.5. Remote Query Optimization

`postgres_fdw` attempts to optimize remote queries to reduce the amount of data transferred from foreign servers. This is done by sending query `WHERE` clauses to the remote server for execution, and by not retrieving table columns that are not needed for the current query. To reduce the risk of misexecution of queries, `WHERE` clauses are not sent to the remote server unless they use only data types, operators, and functions that are built-in or belong to an extension that's listed in the foreign server's `extensions` option. Operators and functions in such clauses must be `IMMUTABLE` as well. For an `UPDATE` or `DELETE` query, `postgres_fdw` attempts to optimize the query execution by sending the whole query to the remote server if there are no query `WHERE` clauses that cannot be sent to the remote server, no local joins for the query, no row-level local `BEFORE` or `AFTER` triggers or stored generated columns on the target table, and no `CHECK OPTION` constraints from parent views. In `UPDATE`, expressions to assign to target columns must use only built-in data types, `IMMUTABLE` operators, or `IMMUTABLE` functions, to reduce the risk of misexecution of the query.

 When `postgres_fdw` encounters a join between foreign tables on the same foreign server, it sends the entire join to the foreign server, unless for some reason it believes that it will be more efficient to fetch rows from each table individually, or unless the table references involved are subject to different user mappings. While sending the `JOIN` clauses, it takes the same precautions as mentioned above for the `WHERE` clauses.

 The query that is actually sent to the remote server for execution can be examined using `EXPLAIN VERBOSE`.

### F.35.6. Remote Query Execution Environment

 In the remote sessions opened by `postgres_fdw`, the [search\_path](runtime-config-client.html#GUC-SEARCH-PATH) parameter is set to just `pg_catalog`, so that only built-in objects are visible without schema qualification. This is not an issue for queries generated by `postgres_fdw` itself, because it always supplies such qualification. However, this can pose a hazard for functions that are executed on the remote server via triggers or rules on remote tables. For example, if a remote table is actually a view, any functions used in that view will be executed with the restricted search path. It is recommended to schema-qualify all names in such functions, or else attach `SET search_path` options (see [CREATE FUNCTION](sql-createfunction.html)) to such functions to establish their expected search path environment.

`postgres_fdw` likewise establishes remote session settings for various parameters:

* [TimeZone](runtime-config-client.html#GUC-TIMEZONE) is set to `UTC`

* [DateStyle](runtime-config-client.html#GUC-DATESTYLE) is set to `ISO`

* [IntervalStyle](runtime-config-client.html#GUC-INTERVALSTYLE) is set to `postgres`

* [extra\_float\_digits](runtime-config-client.html#GUC-EXTRA-FLOAT-DIGITS) is set to `3` for remote servers 9.0 and newer and is set to `2` for older versions

 These are less likely to be problematic than `search_path`, but can be handled with function `SET` options if the need arises.

 It is *not* recommended that you override this behavior by changing the session-level settings of these parameters; that is likely to cause `postgres_fdw` to malfunction.

### F.35.7. Cross-Version Compatibility

`postgres_fdw` can be used with remote servers dating back to PostgreSQL 8.3. Read-only capability is available back to 8.1. A limitation however is that `postgres_fdw` generally assumes that immutable built-in functions and operators are safe to send to the remote server for execution, if they appear in a `WHERE` clause for a foreign table. Thus, a built-in function that was added since the remote server's release might be sent to it for execution, resulting in “function does not exist” or a similar error. This type of failure can be worked around by rewriting the query, for example by embedding the foreign table reference in a sub-`SELECT` with `OFFSET 0` as an optimization fence, and placing the problematic function or operator outside the sub-`SELECT`.

### F.35.8. Examples

 Here is an example of creating a foreign table with `postgres_fdw`. First install the extension:
```

CREATE EXTENSION postgres_fdw;

```
 Then create a foreign server using [CREATE SERVER](sql-createserver.html). In this example we wish to connect to a PostgreSQL server on host `192.83.123.89` listening on port `5432`. The database to which the connection is made is named `foreign_db` on the remote server:
```

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

```
 A user mapping, defined with [CREATE USER MAPPING](sql-createusermapping.html), is needed as well to identify the role that will be used on the remote server:
```

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

```
 Now it is possible to create a foreign table with [CREATE FOREIGN TABLE](sql-createforeigntable.html). In this example we wish to access the table named `some_schema.some_table` on the remote server. The local name for it will be `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');

```
 It's essential that the data types and other properties of the columns declared in `CREATE FOREIGN TABLE` match the actual remote table. Column names must match as well, unless you attach `column_name` options to the individual columns to show how they are named in the remote table. In many cases, use of [`IMPORT FOREIGN SCHEMA`](sql-importforeignschema.html) is preferable to constructing foreign table definitions manually.

### F.35.9. Author

 Shigeru Hanada `<[shigeru.hanada@gmail.com](mailto:shigeru.hanada@gmail.com)>`
```