dblink

A contrib module which enables execution of queries in another PostgreSQL database

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.

While dblink is still supported, postgres_fdw provides a generally more convenient way of accessing remote PostgreSQL databases.

dblink was introduced in PostgreSQL 7.2 (release notes; commit 5af48553).

Installation

The 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 dblink.

Defining Connections

Rather than provide the entire connection string with each query, it's possible to set up persistent connections which can be used for multiple dblink calls.

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 dblink connections:

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)

Change history

Note: this list enumerates significant changes to dblink; see the PostgreSQL release notes for full details of all changes.

Examples

A basic 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)

The connection string is a standard libpq connection string. The columns returned by the remote query must be explictly defined, as the local PostgreSQL server can't know the remote column types.

  • PostgreSQL documentation: dblink

Categories

Contrib module, Extension, Foreign Data Wrapper (FDW), Replication

See also

postgres_fdw