How to Use PostgreSQL Foreign Data Wrappers (FDW) for External Data Access

PostgreSQL Foreign Data Wrapper (FDW)

A Foreign Data Wrapper (FDW) in PostgreSQL is a feature that allows PostgreSQL to access external data stored in different databases, such as another PostgreSQL instance, MySQL, or even files. This enables PostgreSQL to query data from various sources as though it were local, providing seamless integration and data interoperability.

Syntax and Setup for Foreign Data Wrappers:

To use FDWs, you need to:

• Create the Extension: Install the foreign data wrapper for the specific external database.
• Create a Foreign Server: Define the external data source.
• Create User Mappings: Specify credentials to access the external data.
• Create Foreign Tables: Map the external table to a foreign table in PostgreSQL.

Example: Setting Up a PostgreSQL FDW to Another PostgreSQL Database

1. Install the postgres_fdw Extension

The postgres_fdw extension enables PostgreSQL to access another PostgreSQL database.

Code:

-- Install the postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. Create a Foreign Server

Define the external PostgreSQL database server.

Code:

-- Define the foreign server connection details
CREATE SERVER remote_pg_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');

Here:

• remote_pg_server: Name of the foreign server.
• host: Hostname of the external PostgreSQL database.
• dbname: Database name of the external database.
• port: Port number, typically 5432 for PostgreSQL.

3. Create User Mapping:

Specify the credentials for the user accessing the external database.

Code:

-- Define user mapping for the current user to access the foreign server
CREATE USER MAPPING FOR CURRENT_USER
SERVER remote_pg_server
OPTIONS (user 'remote_user', password 'remote_password');

Here:

• user: Username for the external database.
• password: Password for the external database user.

4. Create a Foreign Table

Map the external database table to a foreign table in the local PostgreSQL database.

Code:

-- Map a table from the remote database
CREATE FOREIGN TABLE foreign_table (
    id INT,
    name VARCHAR(100),
    age INT
)
SERVER remote_pg_server
OPTIONS (table_name 'remote_table');

Here:

• foreign_table: Name of the foreign table created in PostgreSQL.
• table_name: Name of the table in the external PostgreSQL database.

Querying the Foreign Table:

Now, you can query the foreign table as if it were a local table.

Code:

-- Query the foreign table
SELECT * FROM foreign_table WHERE age > 25;

Additional FDW Options:

• MySQL Foreign Data Wrapper: mysql_fdw for accessing MySQL databases.
• CSV Foreign Data Wrapper: file_fdw to access data in CSV files as tables.
• MongoDB Foreign Data Wrapper: mongo_fdw for MongoDB integration.

Explanation of FDW Components:

• Foreign Server: Represents the external database connection.
• User Mapping: Associates PostgreSQL users with credentials for external data sources.
• Foreign Table: Maps a table from an external database into the PostgreSQL schema.

Summary:

The Foreign Data Wrapper in PostgreSQL offers flexibility for working with multiple data sources by integrating remote databases and external files directly into PostgreSQL. This feature supports distributed queries, data migration, and seamless access to heterogeneous databases.

All PostgreSQL Questions, Answers, and Code Snippets Collection.