Steps to Setup Database Link in PostgreSQL

October 5, 2021

Steps to Setup Database Link in PostgreSQL

Database Link in PostgreSQL enables a database user to access a table present on a different PostgreSQL cluster. It provides a functionality in PostgreSQL similar to that of DB Link in Oracle, Linked Server in SQL Server and Federated Table in MySQL. In previous post we saw How to Setup foreign data wrapper (postgres_fdw) Click here to read more. In this post we will see Steps to Setup DB Link in PostgreSQL.

For this demonstration, we’ll use the databases local_db and remote_db. We will access a table employee in remote_db from local_db through DB LINK.

Below are the high-level steps we will follow to set up DB Link
1. Setup Remote DB & pg_hba.conf
2. Create the DBLink Extension
3. Create the Foreign Server
4. Create User Mapping
5. Grant the Local User Access to the Foreign Server
6. Establish connection

Below are setup details and the same will be used in this demonstration.

Sr. No.	DB Name	User Name	Hostname	IP	Port
1	local_db	app_user	test-machine01	192.168.114.177	5432
2	remote_db	fdw_user	test-machine02	192.168.114.176	5432

Step 1. Setup Remote DB & pg_hba.conf: In this step, we will create Database remote_db & User fdw_user. DB remote_db will be used in a later step to create a Foreign Server and user fdw_user will be used in a later step to Create User Mapping. Make sure to update file pg_hba.conf to allow PostgreSQL DB connection from Server: test-machine01. We have added the line “host all all 192.168.114.0/24 scram-sha-256” as we are using the ” 192.168.114.0/24 ” as a subnet.

[postgres@test-machine02]
[postgres@test-machine02]psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=#
postgres=# create database remote_db;
CREATE DATABASE
postgres=#
postgres=# CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret';
CREATE ROLE
postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 user_test   | Superuser                                                  | {}        |
 fdw_user  |                                                            | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 repluser  | Replication                                                | {}        |
postgres=#
postgres=# \q
[postgres@test-machine02]psql -h test-machine02 -U fdw_user remote_db
Password for user fdw_user:
psql (13.2)
Type "help" for help.
 
remote_db=> create table employee (id int, first_name varchar(20), last_name varchar(20));
CREATE TABLE
remote_db=> insert into employee values (1,'jobin','augustine'),(2,'avinash','vallarapu'),(3,'fernando','camargos');
INSERT 0 3
remote_db=> \dt employee
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | employee | table | fdw_user
(1 row)
 
remote_db=> select count(*) from employee;
 count
-------
     3
(1 row)
 
remote_db=>
 
[postgres@test-machine02]pwd
/var/lib/pgsql/13/data
[postgres@test-machine02]cat pg_hba.conf
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.114.0/24        scram-sha-256
[postgres@test-machine02]

Step 2. Create the DBLink Extension: Use psql command to create an extension to create the dblink extension. If you receive the error “ERROR: could not open extension control file dblink.control: No such file or directory“. You need to install postgresql13-contrib package. Use OS yum install command to install the missing PostgreSQL package. Once the package is installed try to create a dblink extension again. Please note all below commands we are executing in DB: local_db as we want to avoid using default postgres DB.

[root@test-machine01 ~]# su - postgres
Last login: Fri Oct  1 12:24:24 +03 2021 on pts/0
[postgres@test-machine01]
[postgres@test-machine01] psql local_db
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

local_db=# create extension dblink;
ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/dblink.control": No such file or directory
local_db=#

[root@test-machine01 extension]# yum install postgresql13-contrib
Loaded plugins: langpacks, ulninfo
 
Dependencies Resolved
 
=============================================================================================================================================================================
 Package                                          Arch                               Version                                        Repository                          Size
=============================================================================================================================================================================
Installing:
 postgresql13-contrib                             x86_64                             13.4-1PGDG.rhel7                               pgdg13                             609 k
Updating for dependencies:
 postgresql13                                     x86_64                             13.4-1PGDG.rhel7                               pgdg13                             1.4 M
 postgresql13-libs                                x86_64                             13.4-1PGDG.rhel7                               pgdg13                             381 k
 postgresql13-server                              x86_64                             13.4-1PGDG.rhel7                               pgdg13                             5.4 M
 
Transaction Summary
=============================================================================================================================================================================
Install  1 Package
Upgrade             ( 3 Dependent packages)
  Verifying  : postgresql13-13.1-1PGDG.rhel7.x86_64                                                                                                                      5/7
  Verifying  : postgresql13-libs-13.1-1PGDG.rhel7.x86_64                                                                                                                 6/7
  Verifying  : postgresql13-server-13.1-1PGDG.rhel7.x86_64                                                                                                               7/7
 
Installed:
  postgresql13-contrib.x86_64 0:13.4-1PGDG.rhel7
 
Dependency Updated:
  postgresql13.x86_64 0:13.4-1PGDG.rhel7               postgresql13-libs.x86_64 0:13.4-1PGDG.rhel7               postgresql13-server.x86_64 0:13.4-1PGDG.rhel7
 
Complete!
[root@test-machine01 extension]#

local_db=#
local_db=# create extension dblink;
CREATE EXTENSION
local_db=#
local_db=#
local_db=# \dx
                                    List of installed extensions
     Name     | Version |   Schema   |                         Description
--------------+---------+------------+--------------------------------------------------------------
 dblink       | 1.2     | public     | connect to other PostgreSQL databases from within a database
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers
(3 rows)

local_db=#  select * from pg_extension;
  oid  |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
 14161 | plpgsql      |       10 |           11 | f              | 1.0        |           |
 26435 | postgres_fdw |       10 |         2200 | t              | 1.0        |           |
 26444 | dblink       |       10 |         2200 | t              | 1.2        |           |
(3 rows)

local_db=#

Step 3. Create the Foreign Server: Now we can create a server definition. This foreign server is created using the connection details of the remote server running on host “test-machine02“. Let’s name the foreign server as “testmachine02_dblink “. Use \des to list foreign servers or query view pg_foreign_server.

local_db=# create server testmachine02_dblink foreign data wrapper dblink_fdw options (dbname 'remote_db', host 'test-machine02', port '5432');
CREATE SERVER
local_db=#
local_db=#  \des+
                                                                        List of foreign servers
         Name         |  Owner   | Foreign-data wrapper |  Access privileges  | Type | Version |                       FDW Options                        | Description
----------------------+----------+----------------------+---------------------+------+---------+----------------------------------------------------------+-------------
 testmachine02_dblink | postgres | dblink_fdw           |                     |      |         | (dbname 'remote_db', host 'test-machine02', port '5432') |
 testmachine02_fdw    | postgres | postgres_fdw         | postgres=U/postgres+|      |         | (dbname 'remote_db', host 'test-machine02', port '5432') |
                      |          |                      | app_user=U/postgres |      |         |                                                          |
(2 rows)

local_db=#
local_db=# select * from pg_foreign_server;
  oid  |       srvname        | srvowner | srvfdw | srvtype | srvversion |                  srvacl                   |                    srvoptions
-------+----------------------+----------+--------+---------+------------+-------------------------------------------+--------------------------------------------------
 26439 | testmachine02_fdw    |       10 |  26438 |         |            | {postgres=U/postgres,app_user=U/postgres} | {dbname=remote_db,host=test-machine02,port=5432}
 26492 | testmachine02_dblink |       10 |  26489 |         |            |                                           | {dbname=remote_db,host=test-machine02,port=5432}
(2 rows)

local_db=#

Step 4. Create User Mapping: Create a mapping on the local database: local_db user app_user to target database: remot_db user fdw_user.

local_db=#
local_db=# create user mapping for app_user server testmachine02_dblink options (user 'fdw_user', password 'secret');
CREATE USER MAPPING
local_db=#
local_db=# select * from pg_user_mappings;
 umid  | srvid |       srvname        | umuser | usename  |            umoptions
-------+-------+----------------------+--------+----------+---------------------------------
 26440 | 26439 | testmachine02_fdw    |  26421 | app_user | {user=fdw_user,password=secret}
 26493 | 26492 | testmachine02_dblink |  26421 | app_user | {user=fdw_user,password=secret}
(2 rows)

local_db=#
local_db=#

Step 5. Grant the Local User Access to the Foreign Server: Grant our local user app_user access to the foreign server testmachine02_dblink. Without the below permission, you will receive the error “ERROR: permission denied for foreign server testmachine02_dblink” if you try to create a connection as we are doing in Step 6.

local_db=#
local_db=# grant usage on foreign server testmachine02_dblink to app_user;
GRANT
local_db=#

Step 6. Establish connection: Establish a connection to the foreign server using the dblink_connect() function. Once the connection is established try to access Table: employee from remote_db.

local_db=# \quit
[postgres@test-machine01]
[postgres@test-machine01]  psql -h test-machine01 -U app_user local_db
Password for user app_user:
psql (9.2.24, server 13.4)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

local_db=>
local_db=> select dblink_connect('conn_dblink','testmachine02_dblink');
 dblink_connect
----------------
 OK
(1 row)

local_db=> select * from dblink ('conn_dblink','select * from employee') as test_dblink (id int, first_name varchar(20), last_name varchar(20));
 id | first_name | last_name
----+------------+-----------
  1 | jobin      | augustine
  2 | avinash    | vallarapu
  3 | fernando   | camargos
(3 rows)

local_db=>

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DBLINK, POSTGRESQL

Steps to Setup Database Link in PostgreSQL

Related Articles