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 serverdatabaseorig
: the database name on the source main serverserverdest
: the secondary destination server where the database link will be created pointing to serverorig
schemaorig
: the schema on the main source serverschemadest
: the schema on the secondary destination serverThe first step is to install the extension postgres_fdw
on serverdest
as user postgres
:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
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
)
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');
On serverdest
:
IMPORT FOREIGN SCHEMA schemaorig LIMIT TO (tableorig) FROM SERVER serverorig INTO schemadest;
GRANT ALL ON TABLE schemadest.tableorig TO myuser;
Solutions | Technology | Server | Database | PostgreSQL