PostgreSQL DB-Link | A 5-Step Guide to Using dblink Extension

Learn how to connect two PostgreSQL servers with ease using the dblink extension. Follow our straightforward guide, consisting of just five simple steps, to effortlessly manage data across distributed environments. Let’s get started!

Step 1:- Add dblink extension

                                  create extension dblink;

postgres=# create extension dblink;
CREATE EXTENSION
postgres=#
postgres=# \dx
                                 List of installed extensions
  Name   | Version |   Schema   |                         Description
---------+---------+------------+--------------------------------------------------------------
 dblink  | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pgaudit | 1.7     | public     | provides auditing functionality
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

Step 2:- Establish a foreign server configuration referencing the remote database.

Note:- Please ensure that there is reliable connectivity between the two servers. You can verify the connection by using telnet to check connectivity with the remote database.

Syntax:- CREATE SERVER [any server name] FOREIGN DATA WRAPPER dblink_fdw  OPTIONS ( host ['remote server IP'] ,dbname ['remote database name'] , port ['remote server port no']);

CREATE SERVER remotedb FOREIGN DATA WRAPPER dblink_fdw  OPTIONS ( host '192.168.1.114' ,dbname 'sample_db' , port '5432');

postgres=# CREATE SERVER remotedb FOREIGN DATA WRAPPER dblink_fdw  OPTIONS ( host '192.168.1.114' ,dbname 'sample_db' , port '5432');
CREATE SERVER
postgres=#
postgres=# \des+
                                                                 List of foreign servers
   Name   |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                       FDW options                       | Description
----------+----------+----------------------+-------------------+------+---------+---------------------------------------------------------+-------------
 remotedb | postgres | dblink_fdw           |                   |      |         | (host '192.168.1.114', dbname 'sample_db', port '5432') |
(1 row)

postgres=#

Step 3:- Establish user mapping details

Note:- Ensure that the remote database user has appropriate permissions to read the data from the remote database.

syntax:- CREATE USER MAPPING FOR [user name host server]  SERVER [foreign server name] OPTIONS ( user ['remote db user'] ,password ['remote db password']);

  CREATE USER MAPPING FOR postgres  SERVER remotedb OPTIONS ( user 'suyog' ,password 'suyog');

postgres=#  CREATE USER MAPPING FOR postgres  SERVER remotedb OPTIONS ( user 'suyog' ,password 'suyog');
CREATE USER MAPPING
postgres=#
postgres=# \deu+
                   List of user mappings
  Server  | User name |            FDW options
----------+-----------+------------------------------------
 remotedb | postgres  | ("user" 'suyog', password 'suyog')
(1 row)

postgres=#

Step 4:- Verify the connection

Syntax:- SELECT dblink_connect(['any connection name'], ['foreign server name']);

                SELECT dblink_connect('test_connection', 'remotedb');

postgres=# SELECT dblink_connect('test_connection', 'remotedb');
 dblink_connect
----------------
 OK
(1 row)

postgres=#

Step 5:- Retrieve data from the foreign server database

Syntax:- select * from dblink(['foreign server name'],['query']) as [colunm and data types];

postgres=# SELECT * FROM dblink('remotedb', 'SELECT * FROM products') AS products(product_id INT, product_name VARCHAR, price NUMERIC);
 product_id | product_name | price
------------+--------------+-------
          1 | Product A    | 10.50
          2 | Product B    | 20.00
          3 | Product C    | 15.75
(3 rows)

postgres=# SELECT * FROM dblink('remotedb', 'SELECT * FROM users') AS users(user_id INT, username VARCHAR, email VARCHAR);
 user_id | username |       email
---------+----------+-------------------
       1 | user1    | user1@example.com
       2 | user2    | user2@example.com
       3 | user3    | user3@example.com
(3 rows)

postgres=# SELECT * FROM dblink('remotedb', 'SELECT COUNT(*) FROM users') AS user_count(count INT);
 count
-------
     3
(1 row)

In summary, creating a database link between two PostgreSQL servers using the dblink extension is a quick and effective way to enhance data accessibility and streamline operations across distributed environments. With just five simple steps, you can establish seamless connectivity and unlock the full potential of your PostgreSQL databases.