DBA Hub

📋Steps in this guide1/5

How to create database link in postgres - DBACLASS DBACLASS

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

postgresql configurationintermediate
by PostgreSQL
14 views
1

1. Create extension dblink if not present.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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

2. Create Foreign server pointing to remote db details:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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

3. Create user mapping details:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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

4. Test db_link connection:

Code/Command (click line numbers to comment):

1
2
3
4
5
postgres=# <span style="color: #993300;"><strong>SELECT dblink_connect('my_new_conn', 'pg_rep_db');</strong></span>
 dblink_connect
----------------
 OK
(1 row)
5

5. Retrieve data using db_link:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!