What is PostgreSQL:

PostgreSQL is an open-source database management system. It has many features that are safely stored and scaled at very complicated data workloads.

Why use PostgreSQL?

For these reasons:

• It helps to build many applications by the developers to protect data under integrity and fault tolerance.
• It is free and open source.
• Supports many languages like C/C++, Python, Java etc.

What is a DB link?

A database link means access to the objects of another database from the current database.

To access those objects, we will use the dB link.

It acts like a connection between two databases.

It acts as a one-way connection between the two databases.

Purpose of creating DB link in PostgreSQL:

The user can access the objects of one database from the other database.

Environment:

Accessibility: The user should connect to a remote database by creating a link to any other database.

Credentials: credentials (user, password, port, and hostname) of the remote database.

Setup:

Here I have considered two databases named mouritech_db and Postgres.

Assumed that a table called orders is available in the Postgres database.

By using the DB link created in the mouritech_db, we can be able to access the objects of the order table from the Postgres database.

Current DB details:

Database_name: mouritech_db

User details:

Hostname: localhost

Port: 5432

Database_name: Postgres

User: Postgres

Password: ****

Steps to create DB link:

Step 1: Create a DB link extension.  

create extension if not exists dblink

Step 2: To verify the dB link.

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%’;

Step 3: To test the dB connection of the remote database.

SELECT dblink_connect (‘host=localhost port=5432 user=postgres password=**** dbname=postgres’)

Step 4: Create foreign key data wrapper:

create foreign data wrapper dblink_fdw validator postgresql_fdw_validator

Step 5: Create a foreign server by giving the hostname and database name.

Create server test_server foreign data wrapper dblink_fdw  (hostaddr ‘10.0.0.0’, dbname ‘postgres’)

Step 6: Create a server mapping to the user. It needs the credentials of the Postgres database to connect to the Postgres database remotely.

Create user mapping for Postgres server test_server options (user ‘postgres’, password ‘****’)

Step-7: Establish a connection to the PostgreSQL using dblink_connect.

select dblink_connect(‘test_server’)

Step-8: Grant the permissions to the PostgreSQL database to access the object from the mouritech_db.

grant usage on foreign server test_server to Postgres.

Step 9: Execute the select query of the PostgreSQL database using dblink ().

Select * from dblink (‘test_server’,’select ord_id, create_ts from as public.orders’)   data(ord_id varchar,create_ts timestamp)

References:

https://www.postgresql.org/docs/current/contrib-dblink-function.html

https://www.educba.com/postgresql-dblink/

https://stackoverflow.com/questions/41803430/postgresql-create-stored-database-link

https://medium.com/@techrandomthoughts/setting-up-db-link-in-postgresql-d196468b43f8

https://www.postgresql.org/docs/current/contrib-dblink-connect.html