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.
$ cd postgresql-9.1.x $ ./configure $ make $ cd contrib $ tar zxvf pgsql_fdw-x.y.z.tar.gz $ cd pgsql_fdw-x.y.z $ make $ sudo make install
You need to set USE_PGXS shell variable to build and install.
$ tar xzvf pgsql_fdw-x.y.z.tar.gz $ cd pgsql_fdw $ make USE_PGXS=1 $ sudo make USE_PGXS=1 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.
$ psql
postgres=# CREATE EXTENSION pgsql_fdw;
CREATE EXTENSION
postgres=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
-----------+----------+-------------------+---------------------
pgsql_fdw | postgres | pgsql_fdw_handler | pgsql_fdw_validator
(1 row)
postgres=#
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.
postgres=# drop EXTENSION pgsql_fdw CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to server remote_db drop cascades to user mapping for public drop cascades to foreign table remote_accounts drop cascades to foreign table remote_branches drop cascades to foreign table remote_tellers drop cascades to foreign table remote_history DROP EXTENSION postgres=#
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.
postgres=# CREATE SERVER remote_db FOREIGN DATA WRAPPER pgsql_fdw postgres-# OPTIONS (host 'hostname', port '5432', dbname 'remote'); CREATE SERVER postgres=#
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.
postgres=# CREATE USER MAPPING FOR postgres SERVER remote_db postgres-# OPTIONS (user 'postgres', password 'secret'); CREATE USER MAPPING postgres=#
User mapping for "public" is used for local users who don't have explicit user mapping.
postgres=# CREATE USER MAPPING FOR public SERVER remote_db postgres-# OPTIONS (user 'app_user', password 'secret'); CREATE USER MAPPING postgres=#
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".
postgres=# CREATE FOREIGN TABLE remote_accounts ( postgres(# aid integer, postgres(# bid integer, postgres(# abalance integer, postgres(# filler char(84)) postgres(# SERVER remote_db postgres(# OPTIONS (nspname 'public', relname 'pgbench_accounts'); CREATE FOREIGN TABLE postgres=#
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.
postgres=# SELECT aid, bid, abalance FROM remote_accounts WHERE aid = 1; aid | bid | abalance -----+-----+---------- 1 | 1 | 0 (1 row) postgres=#
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".
postgres=# EXPLAIN SELECT aid, bid, abalance FROM remote_accounts WHERE aid = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on remote_accounts (cost=100.00..130.82 rows=1 width=12)
Filter: (aid = 1)
Remote SQL: DECLARE pgsql_fdw_cursor_3 SCROLL CURSOR FOR SELECT aid, bid, abalance, NULL FROM public.pgbench_accounts WHERE ((aid OPERATOR(pg_catalog.=) 1))
(3 rows)
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.
postgres=# ALTER USER MAPPING FOR postgres SERVER remote_db postgres-# OPTIONS (SET password 'more secret'); ALTER USER MAPPING postgres=#
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.
postgres=# select * from pgsql_fdw_connections; srvid | srvname | usesysid | usename -------+----------------+----------+---------- 16530 | remote_pgbench | 10 | postgres (1 row)
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:
postgres=# SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_connections; pgsql_fdw_disconnect ---------------------- OK OK (2 rows)
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 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.
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.