speedtech-website/content/en/blog/postgresql-database-link.md

+++
images = ['images/blog/postgresql.jpg']
categories = ['Solutions', 'Technology']
tags = ['Server', 'Database', 'PostgreSQL']
date = '2022-12-11'
lastmod = '2022-12-11'
title = 'Database link with PostgreSQL'
published = true
translationKey ='blog-postgresql-database-link'
slug = 'postgresql-database-link'
+++

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.

<!--more-->

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``` :

```sql
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``` :
```sql
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
```sql
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``` :

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