Foreign Data Wrappers in PostgreSQL Databases (postgres_fdw & dblink)

A Foreign Data Wrapper (FDW) is a feature in database management systems that can communicate with an external data source and access data without physically moving the data on a working local database.

PostgreSQL implements portions of the SQL/MED specification, allowing access to data that resides outside PostgreSQL using regular SQL queries. Such data, which is referred to as foreign data, can be accessed by Foreign Data Wrapper.

Note: SQL/MED is Management of External Data, a part of the SQL standard that deals with how a database management system can integrate data stored outside the database.

We have various Foreign Data wrappers in PostgreSQL like postgres_fdw, file_fdw, Oracle_fdw, Mysql_fdw, TDS_fdw  etc. Here in this blog post we will discuss dblink and postgres_fdw.

dblink

dblink is an extension that facilitates database sessions in establishing connections to other PostgreSQL databases.

• dblink executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.
• There exist a few dblink functions for accessing data from external databases by establishing connections and disconnecting after usage.

Installation of dblink on a database:

The installation of the dblink extension can be executed directly through the CREATE command, as illustrated below.

Steps to set up and access the remote database using dblink:

• dblink_connect(): This function opens a persistent connection to a remote source database.

• dblink() : This function executes a query in a remote database.

• dblink_exec(): This function executes a command in a remote database.

Below are a few examples shown for executing commands like CREATE TABLE, INSERT data, and CREATE VIEW on the remote database.

• dblink_error_message(): This function retrieves the last error message on the named connection.

• dblink_get_connections(): This function provides the names of all open-named dblink connections.

• dblink_disconnect(): This function closes a persistent connection to a remote database.

postgres_fdw

The postgres_fdw extension offers the foreign-data wrapper postgres_fdw, enabling access to data stored in external PostgreSQL servers.

• postgres_fdw provides the same functionality as dblink but uses a more modern and standards-compliant infrastructure and provides better performance.

1: Steps to implement remote access using postgres_fdw:

1. Here we Install the postgres_fdw extension using CREATE EXTENSION.

     2. To establish connections to remote (source) databases, you can create a foreign server object using the CREATE SERVER command.

E.g.: CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (hostaddr  ‘remote_ip_addr’ , port ‘remote_port’ , dbname ‘remote_db);’

Furthermore, you can list foreign servers through the below command.

      3. For each database user to access each remote server, create a user mapping using the CREATE USER MAPPING command.

E.g.: CREATE USER MAPPING FOR SERVER foreign_server OPTIONS (USER ‘remote_db_user’ , password ‘*****’);

Furthermore, to list user mappings, use the below command.

2: Creating a FOREIGN TABLE and accessing remote data:

To create a new foreign table in the current database, use CREATE FOREIGN TABLE

Note: Users must have USAGE privilege on the foreign server to be able to create a foreign table.

Below is the sample example shown for the Creation Foreign Table and accessing the data.

2.1: Here, we see the data from the Remote Database, which we try to access from the Target Database.

 2.2: Below, we create Foreign Table on the target Database and access the data.

• It’s essential that the data types and other properties of the columns declared in CREATE FOREIGN TABLE match the actual remote table. 

As a result, below we see a list of foreign tables created in the foreign_schema.

2.3: Validation of Source Data with step 2.1 and data copied to the target local database.

3: IMPORT FOREIGN SCHEMA and access the remote/source data:

The IMPORT FOREIGN SCHEMA command seamlessly integrates table definitions from a foreign server into the local database, facilitating the smooth incorporation of foreign table structures.

3.1: To import all the tables in the remote (source) schema to the local working database, use the below command.

Below we list Foreign Tables imported from remote source database.

Validating the data in the target after importing to the schema postgres_fdw_schema involves ensuring that it matches the source data.

3.2: Import only foreign tables matching one of the given table names using “LIMIT TO” enables selective inclusion of specific foreign tables.

Furthermore, validation of copied data into the local working database with remote data (step2.1).

3.3: Import all the foreign tables excluding the given table names using “EXCEPT” to enable selective exclusion of specific foreign tables.

Below are the tables imported excluding the table “employees” and validation of copied data into the local working database with remote data (step2.1).

Limitations:

1. The ONLY option specified in SELECT, UPDATE, DELETE, or TRUNCATE has no effect when accessing or modifying the remote table using postgres_fdw.
2. postgres_fdw currently lacks support for INSERT statements with an ON CONFLICT DO UPDATE clause. However, the ON CONFLICT DO NOTHING clause is supported.

 

For more details, please refer to these blog posts.

• Foreign Data Wrappers in PostgreSQL and a closer look at postgres_fdw
• New Features in PostgreSQL 14: Bulk Inserts for Foreign Data Wrappers
• Postgresql_fdw Authentication Changes in PostgreSQL 13

See why running open source PostgreSQL in-house demands more time, expertise, and resources than most teams expect — and what it means for IT and the business.

 

PostgreSQL in the Enterprise: The Real Cost of Going DIY