pgsql_fdw
Name
pgsql_fdw -- A foreign data wrapper fot external PostgreSQL servers.
Description
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.
- Replace unnecessary column reference with NULL literal.
- Push some of WHERE clause down to remote server.
Foreign tables are read-only in PostgreSQL 9.1, so modifying foreign tables would cause error.
Install
This section describes how to install pgsql_fdw.
Requirement
- PostgreSQL
- 9.1
Build module
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.
Building with source code
You need to extract source files of pgsql_fdw down to contrib directory of PostgreSQL which has already been built, and invoke make command.
Building with pgxs
You need to set USE_PGXS shell variable to build and install.
Create extension
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.
Uninstall
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.
How to use
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.
Create server
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.
Create user mapping
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.
Create foreign table
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".
Execute query
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".
Alter FDW options
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.
Connection management
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.
Vaild FDW options
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.
Connection options
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.
- client_encoding
- fallback_application_name
- replication
See Database Connection Control Functions 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.
Object name optionsl
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.
Cursor option
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.
Restrictions
There are some restrictions to use pgsql_fdw.
- Foreign tables are read-only
- Foreign tables are read-only for specification of PostgreSQL, so DMLs other than SELECT fail with error. In addition, VACUUM and ANALYZE don't handle foreign tables. Please connect external server directly to execute this kind of commands to external tables.
- Management of connection and transaction
- A connection is shared between scans which access same foreign server even those scans are in a local query, and pgsql_fdw starts a transaction only at the beginning of a connection. As a result, multiple scans in a local query might have inconsistent result from the viewpoint of transaction.
Details
This section describes advanced usage and internal details.
Internal use of libpq options
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.
Functions
This extension provides pgsql_fdw_validator as a valiidator, and pgsql_fdw_handler as a handler. These are created automatically during CREATE EXTENSION statement.
See also
PostgreSQL Documents
Foreign Data, CREATE FOREIGN DATA WRAPPER, CREATE SERVER, CREATE USER MAPPING, CREATE FOREIGN TABLE, Database Connection Control Functions,
Top > pgsql_fdw