Steps to Setup a Foreign Data Wrapper(postgres_fdw) in PostgreSQL

A foreign data wrapper (postgres_fdw) is an extension available in PostgreSQL that allows you to access a table or schema in one PostgreSQL database from another PostgreSQL DB.

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.

Below are the high-level steps we will follow to set up postgres_fdw
1. Setup Remote DB & pg_hba.conf
2. Create the Extension
3. Create the Foreign Server
4. Create User Mapping
5. Grant the Local User Access to the Foreign Server
6. Import the Foreign Schema or Tables

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 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 Extension: Use psql command create an extension to create postgres_fdw extension. If you receive the error “ERROR: could not open extension control file ostgres_fdw.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 postgres_fdw extension again. Please note all below commands we are executing in DB: local_db as we want to avoid using default postgres DB.

[postgres@test-machine01] psql local_db
psql (9.2.24, server 13.4)
Type "help" for help.

local_db=# create extension postgres_fdw;
ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/postgres_fdw.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]#

[postgres@test-machine01] psql local_db
psql (9.2.24, server 13.4)
Type "help" for help.

local_db=# create extension postgres_fdw;
CREATE EXTENSION
local_db=#
local_db=#  \dx postgres_fdw
                             List of installed extensions
     Name     | Version | Schema |                    Description
--------------+---------+--------+----------------------------------------------------
 postgres_fdw | 1.0     | public | foreign-data wrapper for remote PostgreSQL servers
(1 row)

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        |           |
(2 rows)

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_fdw “. Use \des to list foreign servers or query view pg_foreign_server.

local_db=#
local_db=# CREATE SERVER testmachine02_fdw FOREIGN DATA WRAPPER postgres_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_fdw | postgres | postgres_fdw         |                   |      |         | (dbname 'remote_db', host 'test-machine02', port '5432') |
(1 row)

local_db=# select * from pg_foreign_server;
  oid  |      srvname      | srvowner | srvfdw | srvtype | srvversion | srvacl |                    srvoptions
-------+-------------------+----------+--------+---------+------------+--------+--------------------------------------------------
 26439 | testmachine02_fdw |       10 |  26438 |         |            |        | {dbname=remote_db,host=test-machine02,port=5432}
(1 row)

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_fdw 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}
(1 row)
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_fdw. Without the below permission, you will receive the error “ERROR: permission denied for foreign server testmachine02_fdw” if you try to create Foreign Table as we are doing in Step 6.

local_db=#
local_db=# GRANT USAGE ON FOREIGN SERVER testmachine02_fdw TO app_user;
GRANT
local_db=#

Step 6. Import the Foreign Schema or Tables: Create a foreign table in the local_db using our app user app_user with the same structure as the source table, but with OPTIONS specifying target schema_name and table_name. Now we can use the foreign table in local_db to access remote_db table employee.

Instead of creating foreign tables one by one like below you can perform full schema import using the below command
local_db=> IMPORT FOREIGN SCHEMA “public” FROM SERVER testmachine02_fdw INTO public; :- To import a full schema.

If you wish to choose a certain list of tables for import, you can use the following syntax.
local_db=> IMPORT FOREIGN SCHEMA “public” limit to (employee) FROM SERVER testmachine02_fdw INTO public; :-To choose a certain list of tables.

[postgres@test-machine01]  psql -h test-machine01 -U app_user local_db
Password for user app_user:
psql (9.2.24, server 13.4)
Type "help" for help.

local_db=>
local_db=> CREATE FOREIGN TABLE employee
local_db-> (id int, first_name character varying(20), last_name character varying(20))
local_db-> SERVER testmachine02_fdw OPTIONS (schema_name 'public', table_name 'employee');
CREATE FOREIGN TABLE
local_db=>
local_db=> select * from employee;
 id | first_name | last_name
----+------------+-----------
  1 | jobin      | augustine
  2 | avinash    | vallarapu
  3 | fernando   | camargos
(3 rows)

local_db=>
local_db=> \dE+
                          List of relations
 Schema |   Name   |     Type      |  Owner   |  Size   | Description
--------+----------+---------------+----------+---------+-------------
 public | employee | foreign table | app_user | 0 bytes |
(1 row)

local_db=>
local_db=>  select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 local_db              | public               | employee           | local_db               | testmachine02_fdw
(1 row)

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.

POSTGRES_FDW, POSTGRESQL

DBsGuru