In PostgreSQL, it is possible to create database links and connect two separate databases, whether they reside on the same server or on separate servers, as long as they are accessible over a network.

The used nomenclature is :

• serverorig : the source main server
• databaseorig : the database name on the source main server
• serverdest : the secondary destination server where the database link will be created pointing to serverorig
• schemaorig : the schema on the main source server
• schemadest : the schema on the secondary destination server

1. Extension installation

The first step is to install the extension postgres_fdw on serverdest as user postgres :

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. User creation to access through the database link

On serverorig create the user that permits the connection from serverdest and has access to the schema schemaorig and to the necessary tables. For simplicity we call the user userdest with password passworddest and it should be able to access the table tableorig with the desired privileges : (SELECT, INSERT, UPDATE, DELETE)

3. Database link creation

On serverdest :

CREATE serverorig FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'serverorig', port 'portorig', dbname 'databaseorig');

On serverdest create the mapping for the user (myuser) that should be able to use the database link

CREATE USER MAPPING FOR myuser SERVER serverorig OPTIONS ( user 'userdest' , password 'passworddest');

5. Import the tables structure from the main schema ad assign the privileges

On serverdest :

IMPORT FOREIGN SCHEMA schemaorig LIMIT TO (tableorig) FROM SERVER serverorig INTO schemadest;
GRANT ALL ON TABLE schemadest.tableorig TO myuser;