DB Link Test on Local Cluster across Two DBs with Same Owner and Public Schema

Test Case : 

DB Link Test on Local Cluster across Two DBs with Same Owner and Schema


Procedures :

Assuming User already done setup on PG & DB their own server.


Pre-req :

Same Local Cluster -> Different Database -> Both Users are having common Public Schema and Both DB Owners are same ie,.postgres

Yum install yum install postgresql11-contrib

Note : Make sure whether you installed this rpm.

Without Creating dblink extension, You will not get output..

Steps :


postgres=# CREATE EXTENSION dblink;


SELECT pg_namespace.nspname, pg_proc.proname  FROM pg_proc, pg_namespace
WHERE pg_proc.pronamespace=pg_namespace.oid  AND pg_proc.proname LIKE '%dblink%';


        proname
---------+-------------------------
 public  | dblink_connect
 public  | dblink_connect
 public  | dblink_connect_u
 public  | dblink_connect_u
 public  | dblink_disconnect
 public  | dblink_disconnect
 public  | dblink_open
 public  | dblink_open
 public  | dblink_open
 public  | dblink_open
 public  | dblink_fetch
 public  | dblink_fetch
 public  | dblink_fetch
 public  | dblink_fetch
 public  | dblink_close
 public  | dblink_close
 public  | dblink_close
 public  | dblink_close
 public  | dblink
 public  | dblink
 public  | dblink
 public  | dblink
 public  | dblink_exec
 public  | dblink_exec
 public  | dblink_exec
 public  | dblink_exec
 public  | dblink_get_pkey
 public  | dblink_build_sql_insert
 public  | dblink_build_sql_delete
 public  | dblink_build_sql_update
 public  | dblink_current_query
 public  | dblink_send_query
 public  | dblink_is_busy
 public  | dblink_get_result
 public  | dblink_get_result
 public  | dblink_get_connections
 public  | dblink_cancel_query
 public  | dblink_error_message
 public  | dblink_get_notify
 public  | dblink_get_notify
 public  | dblink_fdw_validator
(41 rows)

postgres=#

Now Test Connection of Database :


postgres=# \l
                                    List of databases
      Name      |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
----------------+----------+----------+-------------+-------------+-----------------------
 Adventureworks | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 dvdrental      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |          |          |             |             | postgres=CTc/postgres
 template1      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                |          |          |             |             | postgres=CTc/postgres
(5 rows)

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# select count(*) from a1;
 count
-------
     2
(1 row)

dvdrental=# \c Adventureworks
You are now connected to database "Adventureworks" as user "postgres".
Adventureworks=# select dblink_connect('host=localhost user=postgres password=welcome123 dbname=dvdrental');

Error :

ERROR:  function dblink_connect(unknown) does not exist
LINE 1: select dblink_connect('host=localhost user=postgres password...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Fix Error :

Adventureworks=# create extension dblink;
CREATE EXTENSION

Test DB Link  :

Adventureworks=#  select dblink_connect('host=localhost user=postgres password=welcome123 dbname=dvdrental');

ERROR:  could not establish connection
DETAIL:  could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

Fix Connection :


Adventureworks=#  select dblink_connect('host=pg1 user=postgres password=welcome123 dbname=dvdrental');
 dblink_connect
----------------
 OK
(1 row)

Adventureworks=#

Note : You can use FDW for Global authentication.


Adventureworks=# create extension postgres_fdw;
CREATE EXTENSION

Adventureworks=# CREATE FOREIGN DATA WRAPPER FDW VALIDATOR postgresql_fdw_validator;
CREATE FOREIGN DATA WRAPPER

Adventureworks=# create server kart4avn FOREIGN DATA WRAPPER FDW OPTIONS (hostaddr '192.168.56.101',dbname 'dvdrental');
CREATE SERVER

Adventureworks=#  CREATE USER MAPPING FOR postgres SERVER  kart4avn OPTIONS (user 'postgres',password 'welcome123');
CREATE USER MAPPING
Adventureworks=# SELECT dblink_connect('kart4avn');
 dblink_connect
----------------
 OK
(1 row)


Adventureworks=# GRANT USAGE ON FOREIGN SERVER kart4avn to  postgres;
GRANT

                    ^
Adventureworks=# select * from dblink('kart4avn','select a from dvdrental.public.a1') AS DATA(A INT2);
 a
----
 10
 11
(2 rows)


                     ^
Adventureworks=# select * from dblink('kart4avn','select a from dvdrental.public.a1') AS DATA(ASDASD INT2);
 asdasd
--------
     10
     11
(2 rows)