Foreign data wrappers concept in PostgreSQL

Introduction

Foreign data wrappers let you access data stored outside your database as if it were inside. This helps you work with different data sources easily.

You want to query data from another database without copying it.

You need to combine data from different systems in one query.

You want to access files or external services as tables.

You want to keep data in place but still analyze it with SQL.

You want to join local and remote data in a single query.

Syntax

PostgreSQL

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'host_address', dbname 'database_name', port '5432');

CREATE USER MAPPING FOR local_user SERVER server_name OPTIONS (user 'remote_user', password 'remote_password');

IMPORT FOREIGN SCHEMA public FROM SERVER server_name INTO local_schema;

-- Or create foreign table manually:
CREATE FOREIGN TABLE local_schema.table_name (
  column1 datatype,
  column2 datatype
) SERVER server_name OPTIONS (schema_name 'public', table_name 'remote_table');

You must create the foreign data wrapper extension before using it.

User mappings define how local users connect to the remote server.

Examples

This command enables the foreign data wrapper feature for PostgreSQL.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Defines a connection to a remote PostgreSQL server.

PostgreSQL

CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.1.10', dbname 'remotedb', port '5432');

Maps your local user to a remote user for authentication.

PostgreSQL

CREATE USER MAPPING FOR CURRENT_USER SERVER myserver OPTIONS (user 'remoteuser', password 'secret');

Imports all tables from the remote public schema into your local schema.

PostgreSQL

IMPORT FOREIGN SCHEMA public FROM SERVER myserver INTO local_schema;

Sample Program

This example sets up a foreign data wrapper to access a table named 'people' from a remote database called 'foreign_db'. Then it selects all rows from that foreign table.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER remoteserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'foreign_db', port '5432');

CREATE USER MAPPING FOR CURRENT_USER SERVER remoteserver OPTIONS (user 'foreign_user', password 'foreign_pass');

CREATE FOREIGN TABLE foreign_table (
  id integer,
  name text
) SERVER remoteserver OPTIONS (schema_name 'public', table_name 'people');

SELECT * FROM foreign_table;

Important Notes

Foreign data wrappers allow you to query remote data without moving it.

Performance depends on the remote server and network speed.

You can use FDWs for many data sources, not just PostgreSQL.

Summary

Foreign data wrappers let you treat external data like local tables.

You set up a server connection, user mapping, and foreign tables.

This helps combine and analyze data from different places easily.