dblink is a contrib module which makes it possible to query another PostgreSQL database, whether on the same or on a remote server. Interaction with the other database is carried out by passing queries as parameters to the functions installed by the
dblink module (all of which begin with dblink_). The output of any remote database queries which generate rows is returned as a recordset and can be combined with local queries.
dblink is still supported, postgres_fdw provides a generally more convenient way of accessing remote PostgreSQL databases.
dblink contrib module must be installed on the database server's operating system. dblink needs to be installed by a superuser using the CREATE EXTENSION syntax on any database where
dblink functionality is required (dblink is not required on the remote database).
testdb=*# CREATE EXTENSION dblink; CREATE EXTENSION
Permissions on the remote database
The remote database must be set up to receive connections from the local database; ensure pg_hba.conf and any firewalls are appropriately configured. No special setup on the remote server is required for
dblink connections; if you can connect with psql from the local server, you can connect with
Rather than provide the entire connection string with each query, it's possible to set up persistent connections which can be used for multiple
testdb=# SELECT dblink_connect('dbname=testdb host=192.168.1.35 user=someuser'); dblink_connect ---------------- OK (1 row)
The previous dblink remote query can now be executed without the connection parameters:
SELECT * FROM dblink( /* query to execute */ 'SELECT aid, bid, abalance FROM pgbench_accounts LIMIT 10') /* definition of the columns returned by the query */ AS t1(aid INT, bid INT, abalance INT)
The dblink_connect() function above creates an unnamed connection; it's also possible to create multiple named connections within the same session (note that it's not of course possible to have more than one named connection).
SELECT dblink_connect('my_dblink_conn','dbname=testdb host=192.168.1.35 user=someuser')
SELECT * FROM dblink('my_dblink_conn', 'SELECT aid, bid, abalance FROM pgbench_accounts LIMIT 10') AS t1(aid INT, bid INT, abalance INT)
Connecting using a Foreign Data Wrapper
A more verbose but standards-compliant way of defining
dblink connections is t o create a FOREIGN DATA WRAPPER (FDW). This sets up a global handler for the remote database, meaning the connection string does not need to be provided when setting up
1. Create the foreign data wrapper (only one required per database):
CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
2. Create the remote server:
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgresql OPTIONS (hostaddr '192.168.1.35', dbname 'testdb')
(Note that the connection parameters are provided as name/value pairs)
3. Map the local database user to the foreign server:
CREATE USER MAPPING FOR fdw_user SERVER remote_db OPTIONS(user 'local_fdwtest',password '54321')
Username and password are those on the remote server, of course.
4. Grant usage on the foreign server:
GRANT USAGE ON FOREIGN SERVER remote_db TO fdw_user
User fdw_user can then connect as follows:
testdb=> SELECT dblink_connect('conn1', 'remote_db'); dblink_connect ---------------- OK (1 row) testdb=> SELECT * FROM dblink('conn1', 'SELECT aid, bid, abalance FROM pgbench_accounts LIMIT 2') AS t1(aid INT, bid INT, abalance INT); aid | bid | abalance -----+-----+---------- 1 | 1 | 0 2 | 1 | 0 (2 rows)
Note: this list enumerates significant changes to dblink; see the PostgreSQL release notes for full details of all changes.
- PostgreSQL 9.3
- PostgreSQL 9.1
- PostgreSQL 8.4
- PostgreSQL 8.2
- async query capability added (commit 52a3ed9f)
- PostgreSQL 8.0
- general overhaul (commit 6a1e2b3c)
- PostgreSQL 7.4
- named persistent connections added (commit 8f337e86)
- PostgreSQL 7.2
- added (commit 5af48553)
dblink query looks like this:
SELECT * FROM dblink( /* connection string for the remote database */ 'dbname=testdb host=192.168.1.35 user=someuser', /* query to execute */ 'SELECT aid, bid, abalance FROM pgbench_accounts LIMIT 10') /* definition of the columns returned by the query */ AS t1(aid INT, bid INT, abalance INT)