DB Link

'DB Link' in PostgreSQL enables a database user to access a table present on a different postgres instance. It provides a functionality in PostgreSQL similar to that of 'DB Link' in Oracle, 'Linked Server' in SQL Server and 'Federated tables' in MySQL.

Options :
  • dblink_connect — opens a persistent connection to a remote database
  • dblink_disconnect — closes a persistent connection to a remote database
  • dblink — executes a query in a remote database
  • dblink_exec — executes a command in a remote database

    STEPS : 
        postgres=# create user krishna password 'krish';
    
    CREATE ROLE
    postgres=# create database krishnadb;
    
    CREATE DATABASE
    postgres=# grant all privileges on database krishnadb to krishna;
    GRANT

    ==============================
    postgres=# create user kithari password 'kith';
    
    CREATE ROLE
    postgres=# create database kithdb;
    
    CREATE DATABASE
    postgres=# grant all privileges on database kithdb to kithari;
    
    GRANT
    postgres=# alter user krishna superuser;
    
    ALTER ROLE
    **The user must be a superuser to create the DBlink extension**
    -bash-4.2$ 	
    psql (13.8)
    Type "help" for help.
    Psql -d krishnadb -U krishna -p 5432 (\c krishnadb krishna)

    krishnadb=> create schema krishschema authorization krishna;
    CREATE SCHEMA
    krishnadb=# create extension dblink;
    ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/dblink.control": No such file or directory
    ***if above error come , we nee to install below package****
    [root@krishna ~]# yum install postgresql13-contrib
    krishnadb=# create extension dblink;
    krishnadb=#rCREATE EXTENSION
    krishnadb=# create server kith_link foreign data wrapper dblink_fdw options(host '192.168.1.63',dbname 'kithdb', port '5432');
    CREATE SERVER
    krishnadb=# create user mapping for krishna server kith_link options(user 'kithari', password 'kith');
    CREATE USER MAPPING : 
        krishnadb=# select dblink_connect('conn_db_link','kith_link');
    dblink_connect 
    ----------------
    OK
    (1 row)


    -bash-4.2$ psql -U kithari -d kithdb
    psql (13.8)
    Type "help" for help.

    kithdb=> create table a(id int);
    CREATE TABLE
    kithdb=> insert into a values(1);
    INSERT 0 1
    kithdb=> insert into a values(2);
    INSERT 0 1

    krishnadb=# select * from dblink('conn_db_link','select * from a') as x(id int);
    a 
    ---
    1
    2
    (2 rows)

    krishnadb=# select dblink_exec('kith_link','insert into a values(3);'); 
    dblink_exec 
    -------------
    INSERT 0 1
    (1 row)

    krishnadb=# select * from dblink('conn_db_link','select * from a') as x(a int);
    a 
    ---
    1
    2
    3
    (3 rows)

    kithdb=> select * from a;
    a 
    ---
    1
    2
    3
    (3 rows)
    krishnadb=# select dblink_exec('kith_link','delete from a where id=3');
    dblink_exec 
    -------------
    DELETE 1
    (1 row)
    krishnadb=# select * from dblink('conn_db_link','select * from a') as x(a int);
    a 
    ---
    1
    2
    (2 rows)
    kithdb=> select * from a;
    a 
    ---
    1
    2
    (2 rows)
    krishnadb=# select dblink_disconnect('conn_db_link');
    dblink_disconnect 
    -------------------
    OK
    (1 row)

    krishnadb=# select * from dblink('conn_db_link','select * from a') as x(a int);
    ERROR:  could not establish connection
    DETAIL:  missing "=" after "conn_db_link" in connection info string

    **********************************************************************************


    (Foreign Data Wrapper)