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=# <span style="color: #993300;"><strong>create extension dblink;</strong></span>
CREATE EXTENSION
postgres=#

postgres=# <span style="color: #800000;"><strong>\dx</strong></span>
                                        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=#  <span style="color: #993300;"><strong>CREATE SERVER pg_rep_db FOREIGN DATA WRAPPER dblink_fdw  OPTIONS ( host '10.21.100.131' ,dbname 'postgres' , port '5444');</strong></span>
CREATE SERVER

postgres=# <span style="color: #800000;"><strong>\des+</strong></span>
                                                                   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=#  <span style="color: #993300;"><strong>CREATE USER MAPPING FOR enterprisedb  SERVER pg_rep_db OPTIONS ( user 'dba_raj' ,password 'dba_raj');</strong></span>
CREATE USER MAPPING
postgres=#

postgres=#<span style="color: #993300;"><strong> \deu+</strong></span>
                       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=# <span style="color: #993300;"><strong>SELECT dblink_connect('my_new_conn', 'pg_rep_db');</strong></span>
 dblink_connect
----------------
 OK
(1 row)

5. Retrieve data using db_link:

postgres=# <strong><span style="color: #993300;">select * from dblink('pg_rep_db','select object_name from test') as object_list(object_list varchar );</span></strong>
                  object_list
---------------------------------------------------
 PG_AGGREGATE_FNOID_INDEX
 PG_AM_NAME_INDEX
 PG_AM_OID_INDEX
 PG_AMOP_FAM_STRAT_INDEX

postgres=#
postgres=#  select * from dblink('<span style="color: #993300;"><strong>pg_rep_db</strong></span>','select count(*) from test') as total_count(total_count int);
 total_count
-------------
        4122
(1 row)