Use dblink

Eugenia Kuzina

Overview

The dblink extension allows you to establish connections to remote PostgreSQL databases within the current database session.

IMPORTANT

In most scenarios, the postgres_fdw extension, which covers 90% of dblink functionality, is preferred as it provides better performance. The Foreign tables article describes an example of using postgres_fdw. However, some commands such as VACUUM, REINDEX, and CREATE DATABASE can only be executed on a remote database via dblink.

The package required for the dblink installation is shipped with ADP. To use dblink, just run the CREATE EXTENSION command:

CREATE EXTENSION dblink;
NOTE
If the dblink extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the 1.2 dblink version. To check it, execute the following query:

SELECT extversion FROM pg_extension
    WHERE extname = 'dblink';
 extversion
------------
1.2

Once you create a dblink extension, its functionality will be available in the current database.

Open a connection to a remote database

Use the dblink_connect function to open a persistent connection to a remote database. It has the following syntax:

dblink_connect(text <connection_string>) returns <text>
dblink_connect(text <connection_name>, text <connection_string>) returns <text>

where:

  • <connection_string> — a standard libpq connection string or a name of a PostgreSQL foreign server;

  • <connection_name> — a name to use for the connection;

  • <text> — a status that is always OK; if an error occurs, the function throws an exception instead of returning a value.

Multiple named connections can be opened simultaneously, but only one unnamed connection is permitted at a time. If <connection_name> is omitted, ADP/PostgreSQL opens a new unnamed connection, replacing any existing unnamed connection.

The connection will persist until it is closed or until the database session is terminated.

If untrusted users have access to a database that is not aligned with the secure schema pattern, start each session with removing the schemas they can write from the search path. To do this, you can add the options=-csearch_path= option to a connection string. This consideration is not specific to dblink — it applies to every interface for executing arbitrary SQL commands.

Only superusers can use the dblink_connect function to create connections that do not require password or GSSAPI authentication. If other users need this capability, use the dblink_connect_u function that establishes unsecured connections to a remote database.

NOTE

All connections that you plan to use should be allowed in the PG_HBA field for ADP or in the pg_hba.conf file for PostgreSQL.

Use a connection string

The following code establishes a connection with the test_connection name to the postgres database on the host with IP address 10.92.41.60 using the default credentials: user — postgres, password — postgres.

SELECT dblink_connect('test_connection', 'hostaddr=10.92.41.60 dbname=postgres user=postgres password=postgres');
 dblink_connect
----------------
 OK

Use a foreign server

As mentioned above, it is possible to utilize a PostgreSQL foreign server instead of a connection string. Run the CREATE SERVER command to create a server. It is recommended to use the dblink_fdw foreign-data wrapper for the dblink extension:

CREATE SERVER server_dblink FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '10.92.41.60', dbname 'postgres');

To access a remote database, you need to provide local database users with the remote user’s credentials. Utilize the CREATE USER MAPPING command to create a new user mapping object. Note that a local user should be a superuser to run the dblink_connect command. The code below creates the user1 user, adds mapping, and grants privileges to use the server_dblink foreign server to this user:

CREATE USER user1 WITH SUPERUSER PASSWORD 'password';
CREATE USER MAPPING FOR user1 SERVER server_dblink OPTIONS (user 'postgres', password 'postgres');
GRANT USAGE ON FOREIGN SERVER server_dblink TO user1;

To test the connection, you need to connect to the ADP local server on behalf of this user. For example, you can use the psql tool for this:

$ psql -U user1 -d postgres -h localhost;

Run the dblink_connect command:

SELECT dblink_connect('test_connection_server', 'server_dblink');
 dblink_connect
----------------
 OK

Work with a remote database

This section describes several functions that allow you to work with a remote database. Refer to dblink — connect to other PostgreSQL databases to obtain a full list of the available functions.

The dblink_exec function executes an SQL statement that does not return rows in a remote database. It has the following syntax:

dblink_exec(<connection_name>, text <sql> [, bool <fail_on_error>]) returns text
dblink_exec(<connection_string>, text <sql> [, bool <fail_on_error>]) returns text
dblink_exec(text <sql> [, bool <fail_on_error>]) returns text

where:

  • <connection_string> — a standard libpq connection string or a name of a PostgreSQL foreign server.

  • <connection_name> — a name to use for the connection; omit this parameter to use an unnamed connection.

  • <sql> — an SQL query to execute in the remote database.

  • <fail_on_error> — if true (the default value), an error thrown on the remote side causes an error to be thrown locally. If it is set to false, the remote server error is reported as a NOTICE message in the output, and the function’s return value is set to ERROR.

When two text arguments are given, the first one is considered as a persistent connection’s name. If such a connection is not found, the first argument is treated as a connection string. In this case, the connection is established only to execute the specified SQL query.

Examples

Create a table in the remote database:

SELECT dblink_exec('test_connection', 'CREATE TABLE test_table (field1 integer, field2 text)');
 dblink_exec
--------------
 CREATE TABLE

Populate the table with random values:

SELECT dblink_exec('test_connection', 'INSERT INTO test_table SELECT i, md5(random()::text) FROM generate_series(1, 100) AS i');
 dblink_exec
--------------
 INSERT 0 100

Set <fail_on_error> to false and try to insert an incorrect row:

SELECT dblink_exec('test_connection', 'INSERT INTO test_table VALUES (102, 103, 104)', false);

The dblink_exec function displays a NOTICE message and returns ERROR:

NOTICE:  INSERT has more expressions than target columns
dblink_exec
-------------
 ERROR

The dblink function executes a query that returns rows in a remote database. It has the following syntax:

dblink(text <connection_name>, text <sql> [, bool <fail_on_error>]) returns setof record
dblink(text <connection_string>, text <sql> [, bool <fail_on_error>]) returns setof record
dblink(text <sql> [, bool <fail_on_error>]) returns setof record

The dblink function has the same parameters as dblink_exec except <fail_on_error> . The <fail_on_error> parameter has one difference — if it is set to false, the remote server error is also reported as a NOTICE message, but the function returns no rows.

When two text arguments are given, the first one is considered as a connection’s name. If such a connection does not exist, the first argument is treated as a connection string. In this case, the connection is established only to execute the specified SQL query.

Since dblink can be used with any query, it is declared to return a record rather than a set of columns. This means that you should specify the expected set of columns in the calling query:

SELECT * FROM dblink('test_connection', 'SELECT * FROM test_table LIMIT 10') AS table1(f1 integer, f2 text);
 f1 |                f2
----+----------------------------------
  1 | fdd8565308de9fb9e997e68460ed01d3
  2 | 10c9ba817a368129be55e0ff9dee0235
  3 | 91aa1a9332a7e900f3d08f7c42414884
  4 | 10f202262002d62585f791de703b278b
  5 | 5bd6c9c063d564c8869d32e1251b93a8
  6 | 6f1570d67f3c31c334781aad0dcc89b5
  7 | 172cb5f628946db212b8fead6838e8a7
  8 | e8de5993d5e114e6b1698de637275aec
  9 | 89a9c7e86d6a65eccfcd371efd0f9235
 10 | 765f1de7e23afb773d2599a9747bfd62

In some cases, it will be more convenient to create a view to use the dblink function. This approach allows you to hide information about column types in a view definition and do not write it in each query. For example:

CREATE VIEW remote_table AS
  SELECT *
    FROM dblink('test_connection', 'SELECT * FROM test_table')
    AS table1(f1 integer, f2 text);

SELECT * FROM remote_table WHERE f1 < 20;
 f1 |                f2
----+----------------------------------
  1 | fdd8565308de9fb9e997e68460ed01d3
  2 | 10c9ba817a368129be55e0ff9dee0235
  3 | 91aa1a9332a7e900f3d08f7c42414884
  4 | 10f202262002d62585f791de703b278b
  5 | 5bd6c9c063d564c8869d32e1251b93a8
  6 | 6f1570d67f3c31c334781aad0dcc89b5
  7 | 172cb5f628946db212b8fead6838e8a7
  8 | e8de5993d5e114e6b1698de637275aec
  9 | 89a9c7e86d6a65eccfcd371efd0f9235
 10 | 765f1de7e23afb773d2599a9747bfd62
 11 | 40170f8041d6d14e64d9fe85d4b1341d
 12 | a5c4daa3c95c263338c520c901adb076
 13 | 4bbee39fbc8bb084392b6db3c3cd953c
 14 | 280762ef18f7ccf1cb42d3016ea8d096
 15 | 978a4cdb33644799710a00240d8273c2
 16 | 6e9facc41e99a7d72560eedbaa347964
 17 | 8d0dfc0d641cb911172cb97f2d3ac1a0
 18 | ec2f06f1606d4d463aa5a66c2a7a6091
 19 | f4673b0474c2682354539218ad42365f

Close a connection

The dblink_disconnect function closes a persistent connection to a remote database that was previously opened using dblink_connect. The function has the following syntax:

dblink_disconnect() returns text
dblink_disconnect(text <connection_name>) returns text

The dblink_disconnect function, called without arguments, closes an unnamed connection.

The returned status is always OK. If an error occurs, the function throws an exception instead of returning a value.

SELECT dblink_disconnect('test_connection');
 dblink_disconnect
-------------------
 OK

If you need to determine an opened connection’s name, use the dblink_get_connections function, which returns the names of all open dblink connections named:

SELECT dblink_get_connections();
 dblink_get_connections
------------------------
 {test_connection}

Implementation of autonomous transactions

ADP/PostgreSQL does not support autonomous transactions. However, the dblink extension allows you to realize a similar scenario. The example below shows how to implement logging using autonomous transactions.

  1. Create a table to store logs and a test table:

    CREATE TABLE logs(
    log_id BIGSERIAL PRIMARY KEY,
    level TEXT NOT NULL,
    message TEXT NOT NULL,
    time TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL);

CREATE TABLE test_table_logs (field1 integer, field2 text);

  2. Define a function in PL/pgSQL that adds a record to the logs table using dblink:

    CREATE OR REPLACE FUNCTION logging(param_level text, param_message text) RETURNS void AS
$$
DECLARE
  INSERT_SQL text;

BEGIN
  INSERT_SQL := format('INSERT INTO logs (level, message) VALUES (''%s'',''%s'');', param_level, param_message);

  PERFORM dblink_connect('connection1', 'dbname=postgres');
  PERFORM dblink_exec('connection1', INSERT_SQL);
  PERFORM dblink_exec('connection1','COMMIT;');
  PERFORM dblink_disconnect('connection1');
END
$$ LANGUAGE 'plpgsql';

  3. Start a transaction with the BEGIN command and insert data into the test_table_logs table:

    BEGIN;

INSERT INTO test_table_logs (field1, field2) VALUES (1, 'test row');

    Call the logging function defined above:

    SELECT logging('ERROR', 'An error occurs');

  4. Check the table contents:

    SELECT * FROM logs;
     log_id | level |     message     |            time
--------+-------+-----------------+----------------------------
      1 | ERROR | An error occurs | 2026-01-15 10:31:50.537001
    SELECT * FROM test_table_logs;
     field1 |  field2
--------+----------
      1 | test row

    Each table contains one row.

  5. Roll back the transaction using the ROLLBACK command and then check the table contents:

    ROLLBACK;

SELECT * FROM logs;
     log_id | level |     message     |            time
--------+-------+-----------------+----------------------------
      1 | ERROR | An error occurs | 2026-01-15 10:31:50.537001

    In the logs table, changes have been saved.

    SELECT * FROM test_table_logs;
     field1 | field2
--------+--------
(0 rows)

    There are no rows in the test_table_logs table.

The result shows that changes made using dblink inside the rolled back transaction were preserved.

Found a mistake? Seleсt text and press Ctrl+Enter to report it