** Cross Database Connection PostgreSQL **

User : postgres
Password : postgres

Step 1 :

— Create Database client1 and client2 —

Step 1.1:

— — database: client 1 — — SQL EDITOR1 — -
CREATE TABLE IF NOT EXISTS terminal (
id serial PRIMARY KEY,
terminal_code varchar(50) not null,
atm_teller_cash_id varchar(50) not null,
name varchar(50) not null
);

Insert Records in terminal Table

Step1.2:

— — database: client 2 — — SQL EDITOR2 — -
CREATE TABLE IF NOT EXISTS transaction (
id serial PRIMARY KEY,
main_code varchar(50) not null,
terminal_id int not null,
ref_no varchar(50) not null,
amount numeric(20,2) not null);
Insert Record in transaction Table

Step2:

— — DBLINK EXTENSION in client2 — -
create extension dblink

[Explanation: dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows)
in a remote database. dblink is a PostgreSQL contrib extension that allows users to connect to
other databases and to run arbitrary queries in them. The FDW acronym at the end of dblink_fdw
comes from the words Foreign Data Wrapper which is a unified way for PostgreSQL to define
remote FOREIGN SERVER which to access. It allows to set the DB connection details like hostnames
in a single place and to create a USER MAPPING for remote connections with the needed passwords only once.]

Step3:

— — VERIFY SYSTEM TABLES OF 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%’;

[Explanation: By default query to verify the existance of systme tables of dblink which can be used for
accessing user, databases]

Step4:

— — TEST THE CONNECTION FOR Client1 (DATABASE ONE) — -
select dblink_connect(‘host=localhost user=postgres password=postgres dbname=client1’);

[[Explanation: Connection query to test the link for different database from remote database]

Step5:

— — CREATE FOREIGN DATA WRAPPER AND SERVER FOR GLOBAL AUTHENTICATION — -
Create foreign data wrapper dbcnt validator postgresql_fdw_validator;
create server demodbcnt foreign data wrapper dbcnt options(hostaddr ‘127.0.0.1’, dbname ‘client1’);

[Explanation: You can use this server object for cross database queries where
remote database access another database with global authentication]
[dbcnt is the name for foreign data wrapper]

Step6:

— — MPPING OF USER AND SERVER
create user mapping for postgres server demodbcnt options(user ‘postgres’, password ‘postgres’);

[Explanation: Using password and user name to map between remote server and user]

Step7:

— — TEST THIS SERVER — -
select dblink_connect(‘demodbcnt’);

[Explanation: connection test for server]

Step8:

— — NOW YOU CAN SELECT THE DATA OF DATABASE ONE I.E. client1 FROM DATABASE TWO I.E. client2 — -
select * from dblink(‘demodbcnt’,’select id,terminal_code from public.postgres’)
as data (id integer, terminal_code varchar(50));

Step9:

— — Do same process in client1 database to access client2 database — -
[By doing same process in client2 , we can fetch table records of client1 from client2]

Thank You!