This article explain how can we access data from a remote postgres database using db_link. In postgres we call achieve this using foreign data wrappers.

1. Create extension dblink if not present.

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
 edbspl             | 1.0     | pg_catalog | EDB-SPL procedural language

2. Create Foreign server pointing to remote db details:

postgres=#  CREATE SERVER pg_rep_db FOREIGN DATA WRAPPER dblink_fdw  OPTIONS ( host '10.21.100.131' ,dbname 'postgres' , port '5444');
CREATE SERVER

postgres=# \des+
                                                                   List of foreign servers
    Name    |    Owner     | Foreign-data wrapper | Access privileges | Type | Version |                      FDW options                       |
 Description
------------+--------------+----------------------+-------------------+------+---------+--------------------------------------------------------+
            |

 pg_rep_db  | enterprisedb | dblink_fdw           |                   |      |         | (host '10.21.100.131', dbname 'postgres', port '5444') |

(2 rows)

3. Create user mapping details:

postgres=#  CREATE USER MAPPING FOR enterprisedb  SERVER pg_rep_db OPTIONS ( user 'dba_raj' ,password 'dba_raj');
CREATE USER MAPPING
postgres=#

postgres=# \deu+
                       List of user mappings
   Server   |  User name   |              FDW options
------------+--------------+----------------------------------------
 pg_rep_db  | enterprisedb | ("user" 'dba_raj', password 'dba_raj')

4. Test db_link connection:

postgres=# SELECT dblink_connect('my_new_conn', 'pg_rep_db');
 dblink_connect
----------------
 OK
(1 row)

5. Retrieve data using db_link:

postgres=# select * from dblink('pg_rep_db','select object_name from test') as object_list(object_list varchar );
                  object_list
---------------------------------------------------
 PG_AGGREGATE_FNOID_INDEX
 PG_AM_NAME_INDEX
 PG_AM_OID_INDEX
 PG_AMOP_FAM_STRAT_INDEX

postgres=#
postgres=#  select * from dblink('pg_rep_db','select count(*) from test') as total_count(total_count int);
 total_count
-------------
        4122
(1 row)