.::: Create dblink using dblink_connect & dblink in PostgreSQL EDB :::.

1. check before setting
select pg_read_file('/etc/hostname') as hostname, version();

psql -h 10.10.10.8 -U admin -p 5432 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
psql -h 10.10.10.71 -U admin -p 5444 -d teguhth -c "select pg_read_file('/etc/hostname') as hostname, version();";
 

2. create create new database dblink_edb <optional only case>
teguhth=# create database dblink_edb;
CREATE DATABASE
teguhth=#
 
3. create extension dblink in dblink_edb

teguhth=# \c dblink_edb
You are now connected to database "dblink_edb" as user "postgres".
dblink_edb=#
dblink_edb=# CREATE EXTENSION dblink;
CREATE EXTENSION
dblink_edb=#

dblink_edb=# SELECT * FROM pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14267 | plpgsql |       10 |           11 | f              | 1.0        |           |
 24824 | dblink  |       10 |         2200 | t              | 1.2        |           |
(2 rows)

dblink_edb=#
 

4. create dblink_connect using dblink_connect

dblink_edb=# select dblink_connect ('con_edb','dbname=teguhth port=5444 host=10.10.10.71 user=admin password=admin');
 dblink_connect
----------------
 OK
(1 row)

dblink_edb=#

5. test connection dblink

select * from dblink ('con_edb','select * from barang') AS suplier (kode_barang text, nama_barang text,satuan_barang text, stok_barang text);

select * from dblink ('con_edb','select b.nama_barang,s.nama_suplier,p.tanggal_pasok,p.jumlah_pasok from barang b,suplier s,pasok p where b.kode_barang=p.kode_barang and s.kode_suplier=p.kode_suplier;')
AS table_dblink (nama_barang text,nama_suplier text,tanggal_pasok text,jumlah_pasok text);
 

6. or if directly access

select * from dblink ('dbname=teguhth port=5444 host=10.10.10.71 user=admin password=admin','select * from barang') AS suplier (kode_barang text, nama_barang text,satuan_barang text, stok_barang text);

select * from dblink ('dbname=teguhth port=5444 host=10.10.10.71 user=admin password=admin','select b.nama_barang,s.nama_suplier,p.tanggal_pasok,p.jumlah_pasok from barang b,suplier s,pasok p where b.kode_barang=p.kode_barang and s.kode_suplier=p.kode_suplier;')
AS table_dblink (nama_barang text,nama_suplier text,tanggal_pasok text,jumlah_pasok text);