==============Using Foreign Data Wrappers to access remote PostgreSQL and Oracle databases and other Database ========

A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource. Additional user-specific connection information may be specified by means of user mappings. The server name must be unique within the database.

There are many methods for accessing remote tables data—the data present in the tables existing on the remote database—such as PostgreSQL’s inbuilt dblink module. One of the most popular methods is accessing the table with the help of PostgreSQL Foreign Data Wrapper (postgres_fdw). In addition to postgres_fdw there are other Foreign Data Wrappers such as mongo_fdw, hadoop_fdw, and mysql_fdw,oracle_fdw which can be used to access a MongoDB database, the Hadoop Distributed File System, and data present in a MySQL database, respectively.

postgres_fdw  

For remote access to data in an external PostgreSQL server using postgres_fdw, please refer to the following steps:

create the extension 

For remote access to data in an external PostgreSQL server using postgres_fdw, please refer to the following steps:

login the source database server :

postgres=# 

postgres=# \des

       List of foreign servers

 Name | Owner | Foreign-data wrapper 

------+-------+----------------------

(0 rows)

postgres=# 

ds_sapphire=> select current_database() ;

 current_database 

------------------

 fa_saphire

(1 row)

farukh21@host1 ~]$ psql -U fapphire -d fa_sapphire

Step 1 > Create the extension in the source server ("where you want fetch the data of the schema ")

fa_sapphire=# CREATE EXTENSION postgres_fdw;

CREATE EXTENSION

fa_sapphire=# 

fa_sapphire=# \dx

                               List of installed extensions

     Name     | Version |   Schema   |                    Description                     

--------------+---------+------------+----------------------------------------------------

 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language

 postgres_fdw | 1.1     | public     | foreign-data wrapper for remote PostgreSQL servers

(2 rows)

Step 2:  Create a foreign server for each remote database to which the user wants to connect. Please ensure that the remote database cluster has the pg_hba.conf entry corresponding to the database server for which the foreign server has to be created.

fa_sapphire=# 

fa_sapphire=# CREATE SERVER foreign_sapphire FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.XX.XX.X0 /target_IP', port '5432', dbname 'target_dbname');

CREATE SERVER

fa_sapphire=# 

fa_sapphire=# \des

                 List of foreign servers

        Name         |    Owner    | Foreign-data wrapper 

---------------------+-------------+----------------------

 foreign_fapphire | fapphire | postgres_fdw

(1 row)

Step 3 > create the schemas on source side there can map of remote data through the foreign server 

fa_sapphire=# create schema foreign_fapphire authorization fapphire ;

CREATE SCHEMA

fa_sapphire=# 

fa_sapphire=# CREATE USER MAPPING FOR fapphire SERVER foreign_fapphire OPTIONS (user 'XXXXK', password 'fXXX');

CREATE USER MAPPING

fa_sapphire=# 

fa_sapphire=# IMPORT FOREIGN SCHEMA focus from SERVER foreign_fapphire into foreign_fapphire;

IMPORT FOREIGN SCHEMA

fa_sapphire=# 

fa_sapphire=# 

fa_sapphire=# 

fa_sapphire=# select count(*) from foreign_fapphire.cust_lkodba ;

 count  

--------

 884567

(1 row)

fa_sapphire=# 

Thank you to All ....

Thank you for giving your valuable time to read the above information.