Postgresql - dblinks and foreign data wrappers(FDW)

PostgreSQL dblink

PostgreSQL provides a way to connect to other PostgreSQL databases using the dblink extension. This allows you to execute queries on remote databases and retrieve results as if they were local.

  • install contrib package to configure dblink and FDW 

     
                      
configure https://ftp.postgresql.org/pub/repos/yum/17/redhat/rhel-9-x86_64/ for rpms.

[root@dbapage yum.repos.d]# cat postgre.repo
[postgres-17]
name=PostgreSQL common RPMs for RHEL / Rocky / AlmaLinux $releasever - $basearch
baseurl=https://ftp.postgresql.org/pub/repos/yum/17/redhat/rhel-9-x86_64
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/PGDG-RPM-GPG-KEY-RHEL
repo_gpgcheck = 1
[root@dbapage yum.repos.d]#



[root@dbapage yum.repos.d]# yum install postgresql17-contrib
Oracle Linux 9 UEK Release 7 (x86_64)                                                                    353  B/s | 3.5 kB     00:10
Oracle Linux 9 UEK Release 7 (x86_64)                                                                    4.5 MB/s |  71 MB     00:15
Last metadata expiration check: 0:00:15 ago on Mon 14 Jul 2025 07:51:06 PM IST.
Dependencies resolved.
=========================================================================================================================================
 Package                                 Architecture              Version                               Repository                 Size
=========================================================================================================================================
Installing:
 postgresql17-contrib                    x86_64                    17.5-3PGDG.rhel9                      pgdg17                    730 k

Transaction Summary
=========================================================================================================================================
Install  1 Package

Total download size: 730 k
Installed size: 2.7 M
Is this ok [y/N]: y
Downloading Packages:
postgresql17-contrib-17.5-3PGDG.rhel9.x86_64.rpm                                                         1.4 MB/s | 730 kB     00:00
-----------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                    1.4 MB/s | 730 kB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                 1/1
  Installing       : postgresql17-contrib-17.5-3PGDG.rhel9.x86_64                                                                    1/1
  Running scriptlet: postgresql17-contrib-17.5-3PGDG.rhel9.x86_64                                                                    1/1
  Verifying        : postgresql17-contrib-17.5-3PGDG.rhel9.x86_64                                                                    1/1

Installed:
  postgresql17-contrib-17.5-3PGDG.rhel9.x86_64

Complete!

[postgres@dbapage extension]$ pwd
/usr/pgsql-17/share/extension
[postgres@dbapage extension]$ ls -lrt *db*
-rw-r--r--. 1 root root  170 May 28 13:30 dblink.control
-rw-r--r--. 1 root root 6645 May 28 13:30 dblink--1.2.sql
-rw-r--r--. 1 root root 2832 May 28 13:30 dblink--1.1--1.2.sql
-rw-r--r--. 1 root root  419 May 28 13:30 dblink--1.0--1.1.sql
[postgres@dbapage extension]$ ls -lrt *fdw*
-rw-r--r--. 1 root root 172 May 28 13:30 postgres_fdw.control
-rw-r--r--. 1 root root 507 May 28 13:30 postgres_fdw--1.0.sql
-rw-r--r--. 1 root root 626 May 28 13:30 postgres_fdw--1.0--1.1.sql
-rw-r--r--. 1 root root 155 May 28 13:30 file_fdw.control
-rw-r--r--. 1 root root 475 May 28 13:30 file_fdw--1.0.sql
    ✔ DBLINK 
    				[postgres@dbapage ~]$ psql
                psql (17.5)
                Type "help" for help.

                postgres=# \dx
                                List of installed extensions
                Name   | Version |   Schema   |         Description
                ---------+---------+------------+------------------------------
                plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
                (1 row)

                postgres=# create extension dblink;
                CREATE EXTENSION
                postgres=# \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
                (2 rows)
                db1=# create extension dblink;
                CREATE EXTENSION
                db1=# select dblink_connect('remote_db', 'host=192.168.1.121 port=5432 dbname=db2 user=postgres password=postgres');
                dblink_connect
                ----------------
                OK
                (1 row)

                db1=# select * from dblink('remote_db', 'select * from a') as t(id int);
                id
                ----
                1
                2
                3
                4
                5
                6
                7
                8
                9
                10
                (10 rows)

                 db1=# select dblink_get_connections();
                dblink_get_connections
                ------------------------
                {remote_db}
                (1 row)


                postgres=# select dblink_disconnect('remote_db');
                dblink_disconnect
                ------------------- 
                Connection closed
                (1 row)

                        
DIRECT CONNECTIONS:

db1=# SELECT * FROM dblink('dbname=db2','select * from a') AS t1(a name);
 a
----
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 1
 1
(12 rows)

db1=# SELECT * FROM dblink_Exec('dbname=db2','insert into a values(1)');
 dblink_exec
-------------
 INSERT 0 1
(1 row)
    ✔ FOREIGN DATA WRAPPERS 
                 create extension postgres_fdw;
	
                create server db2server 
                foreign data wrapper postgres_fdw 
                OPTIONS (host '192.168.1.121',dbname 'db1',port '5432');

            create user mapping for postgres 
            SERVER db2server  
            OPTIONS (user 'postgres', password 'postgres');
            

            create foreign table f1
            (a int,
            b int)
            server db2server
            options (table_name 'a');

            select * from pg_foreign_table;
            
            import foreign schema public from server db2server into public;



            select * from pg_user_mapping;
            select * from pg_foreign_server;