How to Use the dblink Extension in PostgreSQL

In the world of databases, there are situations where you need to connect and access data from another PostgreSQL database within your current one. Instead of exporting and importing data manually, PostgreSQL provides a powerful feature called dblink.

The dblink extension allows you to connect to a remote PostgreSQL database and execute SQL commands directly from within another PostgreSQL session. It is extremely useful for cross-database queries, data synchronization, and migration scenarios.

In this blog, we’ll explore everything about the dblink extension, including its installation, configuration, and practical usage examples.

What is dblink?

dblink is a PostgreSQL extension that lets you establish a connection between two PostgreSQL databases and execute queries on a remote database from the local database session.

You can:

• Fetch data from another database.
• Insert, update, or delete records in a remote database.
• Join local and remote data in a single query.

Think of it as a way to query multiple databases without leaving psql.

Step 1: Enable the dblink Extension

By default, PostgreSQL comes with the dblink extension, but it’s not enabled automatically.

To enable it, open your psql terminal and run:

CREATE EXTENSION dblink;

You can check if it’s successfully created using:

\dx

If you see dblink listed in the installed extensions, the installation was successful.

You get results like this

  name  | default_version | installed_version |                           comment                            
--------+-----------------+-------------------+--------------------------------------------------------------
 dblink | 1.2             | 1.2               | connect to other PostgreSQL databases from within a database

If you are using the PostgreSQL source code, follow these steps for the installation of the dblink extension

cd postgres_directory/postgres/contrib/dblink
make
sudo make install

You can check the functions defined from the dblink extension through this command

\dx+ dblink

Step 2: Connect to Another Database Using dblink

To use dblink, you need to provide a connection string that includes the database name, user, host, and password.

Here’s an example:

postgres=# SELECT dblink_connect('myconn', 'dbname=production_db user=postgres password=cool host=localhost port=5433');
 dblink_connect 
----------------
 OK
(1 row)

In this example:

• myconn is the connection name.
• remote_db is the name of the target database.
• user and password are the credentials.
• host can be localhost or the remote IP address.
• Port is the port number where the postgres is running.

SELECT dblink_connect('dbname=remote_db user=postgres password=admin host=localhost');

To check active dblink connections, run:

SELECT * FROM dblink_get_connections();

Step 3: Fetch Data from a Remote Database

Once the connection is established, you can query a remote table directly.

For example:

SELECT *
FROM dblink('myconn', 'SELECT id, login, username  FROM res_users LIMIT 10')
AS t(id integer, login text, username text);

In this query:

• myconn is the connection name.
• The remote SQL statement runs on the connected database.
• You must specify the expected column structure after the AS keyword to map results properly.

Step 4: Insert Data into a Remote Database

You can also insert data into a remote database using dblink_exec.

postgres=# SELECT dblink_exec('myconn',
    'INSERT INTO res_users (id, name, mail)
     VALUES (1, ''marc_demo'', ''marc_demo@gmail.com'')');
 dblink_exec 
-------------
 INSERT 0 1
(1 row)

This will insert a new user record into the dummy table of the remote database.

You can also update or delete records remotely in the same way:

Update Example:

SELECT dblink_exec('myconn', 'UPDATE res_users SET mail = ''newmail@example.com'' WHERE id = 1');

Delete Example:

SELECT dblink_exec('myconn', 'DELETE FROM res_users WHERE id = 1');

Step 5: Disconnect from the Remote Database

When you’re done with the operations, it’s good practice to close the connection.

postgres=# SELECT dblink_disconnect('myconn');
 dblink_disconnect 
-------------------
 OK
(1 row)

You can also disconnect all active connections using:

SELECT dblink_disconnect();

Step 6: Using dblink in a Single Query (Without Persistent Connection)

Sometimes you don’t want to create a persistent connection. You can use a single query with a direct connection string instead:

SELECT * 
FROM dblink('dbname=production_db user=postgres password=cool host=localhost port=5433', 
            'SELECT id,username FROM res_users')
AS remote_users(id INTEGER, name TEXT);

This method is ideal when you need to execute occasional cross-database queries.

Step 7: Joining Local and Remote Data

One of the most powerful use cases of dblink is joining local and remote data within a single query.

For example, suppose your local database has a departments table, and the remote database has a users table. You can join them as follows:

SELECT d.name AS department_name, u.name AS user_name
FROM departments d
JOIN dblink('myconn', 'SELECT id, name, department_id FROM users')
AS u(id INTEGER, name TEXT, department_id INTEGER)
ON d.id = u.department_id;

This allows you to seamlessly merge data from two databases into a single result set.

Important Notes

• The user connecting via dblink must have sufficient privileges on the remote database.
• Make sure that both databases are running on the same PostgreSQL version for better compatibility.
• You can use pg_hba.conf to allow remote connections securely.
• For frequent cross-database access, consider using Foreign Data Wrapper (postgres_fdw), which provides better performance and schema mapping.

Example Use Case: Copying Data Between Databases

You can use dblink to copy data from one database to another easily.

INSERT INTO local_table(id, name)
SELECT id, name 
FROM dblink('myconn', 'SELECT id, name FROM remote_table')
AS remote_table(id INTEGER, name TEXT);

This is a simple and efficient way to replicate data without needing external tools.

Other Examples

1. dblink_get_pkey()

Purpose:

Fetches the primary key column(s) of a remote table.

This is especially helpful when dynamically building queries or verifying schema consistency across databases.

Example:

SELECT * 
FROM dblink_get_pkey('res_users');
Result:
 position | colname 
-----------+---------
         1 | id
(1 row)

Use Case:

Perfect when you’re synchronizing or migrating data and need to identify primary key fields automatically.

2. dblink_error_message()

Purpose:

Returns the last error message for a given dblink connection.

Example:

-- Try executing a faulty query
SELECT dblink_exec('myconn', 'SELECT * FROM non_existing_table');
-- Retrieve the error message
SELECT dblink_error_message('myconn');

Output:
          dblink_error_message           
----------------------------------------
 relation "non_existing_table" does not exist
(1 row)

Use Case:

Useful for debugging and building error-handling logic in automated database sync scripts.

3. dblink_get_result()

Purpose:

Fetches the result of a previously sent asynchronous query.

You can use it to execute queries asynchronously for performance.

Example:

-- Send query asynchronously
SELECT dblink_send_query('myconn', 'SELECT id, username FROM res_users LIMIT 3');
-- Check the result after the query finishes
SELECT * FROM dblink_get_result('myconn', true)
AS t(id integer, username text);

Output:
  id  | username
------+-----------
 2701 | best3750
 2746 | jleibowitz
 248  | tempaccount

Use Case:

Ideal for parallel or delayed query execution scenarios.

Conclusion

The dblink extension is a hidden gem in PostgreSQL that simplifies cross-database communication without relying on external tools or middleware. Whether you’re migrating data, syncing records, or performing analytics across multiple databases, dblink can save you time and effort.

With just a few SQL commands, you can connect, query, and manipulate data across databases securely and efficiently.

If you’re working on PostgreSQL optimizations or Odoo migrations, mastering dblink is an essential step toward smarter database management.