本文共 7094 字，大约阅读时间需要 23 分钟。
pgsql_fdw -- A foreign data wrapper fot external PostgreSQL servers.
You can create foreign tables which point tables on external PostgreSQL servers. Then you can use those foreign tables like ordinary local tables in SELECT query to retrieve data from external data.
pgsql_fdw optimizes remote query to reduce amount of data to be transferred.
Foreign tables are read-only in PostgreSQL 9.1, so modifying foreign tables would cause error.
This section describes how to install pgsql_fdw.
Source code of PostgreSQL 9.1 or pgxs environment is required to build pgsql_fdw binary module. Please interpret x.y.z as the version you downloaded.
You need to extract source files of pgsql_fdw down to contrib directory of PostgreSQL which has already been built, and invoke make command.
You need to set USE_PGXS shell variable to build and install.
pgsql_fdw conforms EXTENSION mechanism, which was introduced in PostgreSQL 9.1, so just need to invoke CREATE EXTENSION command to install pgsql_fdw to your database. You don't need to execute SQL script which has been installed into extension directory of your PostgreSQL installation. It's contrib modules style. To create pgsql_fdw extension, you need to be a superuser.
Executing DROP EXTENSION is the only thing you need to do to uninstall pgsql_fdw. If some objects which were created with pgsql_fdw exist, you need to drop them first, or use CASCADE option of DROP EXTENSION statement.
Following example shows how to drop pgsql_fdw extension and some related objects: a server, a user mapping and four foreign tables.
Here you see how to retrieve data on an external PostgreSQL server. This is just a sample, so please replace connection information and table definition to fit your environment.
First of all, become a superuser and execute CREATE SERVER command to create a server object which points target server. You can specify hostname, port number and database name of the external server here.
Execute CREATE USER MAPPING command to create a user mapping object which links server and local user. Here you can specify username and password of external database user. Superuser privilege is required to this.
User mapping for "public" is used for local users who don't have explicit user mapping.
Execute CREATE FOREIGN TABLE command to define a foreign table which points an external table. The syntax of CREATE FOREIGN TABLE is basically similar to syntax of CREATE TABLE, except that you need to specify server name. You can specify schema name and/or table name of external table as FDW option. Following example shows how to define a foreign table "remote_accounts", which points an external table "public.pgbench_accounts" on a server "remote_db".
Now you can get external data by executing usual SELECT statement against foreign tables. You can execute any SELECT against foreign tables, but can't modify external data via foreign table.
Owner of a foreign table can allow other users to use the foreign table by executing GRANT statement, like ordinary tables.
EXPLAIN command shows actual query which is sent to external server for each foreign table. Following example shows a plan tree which is used to retrieve data from external table "pgbench_accounts" via a foreign table "remote_accounts".
You can change value of FDW options with ALTER statement. Following example shows how to change remote user's password. Note that SET is required to change value of existing option.
pgsql_fdw establishes a connection in the first query which uses a foreign table on a foreign server. Established connection is reused by following queries, and even by following scans in same query, during local session. In other words, only one connection is established against a foreign server until you switch local user. You can see active connections via pgsql_fdw_connections view.
You can discard any active connection anytime by invoking pgsql_fdw_disconnect() with server oid and local user oid. You can discard all active connections with following query:
When local transaction aborts, pgsql_fdw discards all active connections automatically. This would prevent connection leak occurs with unexpected error. Connection will be established again automatically when a foreign server was accessed later.
pgsql_fdw retrieves various information required for a query against a foreign table from FDW options of objects which are related to the foreign table.
All libpq options except following can be used as connection opions. Only "user" and "password" are fore user mappings, and others are for foreign servers.
See for details of libpq options. Note that omitted options are taken from environment variables of the user who started PostgreSQL server. Invalid options would be caught at once, but omission of required parameters and wrong values will be found when query was executed actually.
You can specify name and/or schema of external table as FDW options of a foreign table, "relname" and "nspname". They default to foreign table's name ans schema respectively.
With a FDW option "fetch_count", you can control the behavior of cursor used internally. It is used as number of rows fetched from external server at a time, and defualt to 10000. This option can be specified to foreign table and/or foreign server. If both of them has value, foreign table's setting is used.
There are some restrictions to use pgsql_fdw.
This section describes advanced usage and internal details.
Some of libpq options are used internally. "client_encoding" is set to local encoding to retrieve external data properly. "fallback_application_name" is set to "pgsql_fdw" to allow remote DBAs to know originator of connections via pg_stat_activity and server log.
This extension provides pgsql_fdw_validator as a valiidator, and pgsql_fdw_handler as a handler. These are created automatically during CREATE EXTENSION statement.
转载地址：http://dfitl.baihongyu.com/