PostgreSQL Foreign Data Wrapper (FDW) Setup Guide

This document provides a complete step-by-step guide to set up PostgreSQL Foreign Data Wrapper (FDW) for connecting to remote PostgreSQL databases or databases on the same server.

1. Ensure FDW Support

Most modern PostgreSQL versions have FDW support by default.

Connect to your database:

psql -U postgres -d mydb

Enable the extension:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Verify:

 dx

You should see postgres_fdw in the list.

2. Create Foreign Server

Defines the connection info for the remote database.

CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
    host 'remote_host',       -- remote DB host ('localhost' if same server)
    dbname 'remote_db',       -- remote database name
    port '5432'               -- remote database port
);

3. Create User Mapping

Tells PostgreSQL which credentials to use for the remote server.

CREATE USER MAPPING FOR postgres   -- local user
SERVER remote_db
OPTIONS (
    user 'postgres',               -- remote username
    password 'your_password'       -- remote password
);

• Local user: role in your current database.
• Remote credentials: user on the remote database.

4. Import Tables from Remote Database

Option 1: Import All Tables

IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO public;

• Imports all tables from the remote public schema.
• Error occurs if tables already exist locally.

Option 2: Import Specific Tables (Recommended if some tables exist)

IMPORT FOREIGN SCHEMA public
LIMIT TO (table1, table2)
FROM SERVER remote_db
INTO public;

Option 3: Import All Except Existing Tables (PostgreSQL 14+)

IMPORT FOREIGN SCHEMA public
EXCEPT (employees, orders)
FROM SERVER remote_db
INTO public;

5. Create Foreign Table Manually (Optional)

CREATE FOREIGN TABLE remote_orders (
    id INT,
    customer_name TEXT,
    total NUMERIC
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'orders');

• remote_orders = local table name.
• table_name = actual remote table.

6. Query Remote Tables

SELECT * FROM orders;         -- queries remote table
INSERT INTO orders (...) ...;  -- allowed if remote table is writable

7. Handling New Tables on Remote DB

PostgreSQL does not auto-import new tables.

a) Import Specific New Tables

IMPORT FOREIGN SCHEMA public
LIMIT TO (new_table1, new_table2)
FROM SERVER remote_db
INTO public;

b) Re-import Everything (Postgres skips existing tables)

IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO public;

c) Detect New Tables Dynamically

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name NOT IN (
    SELECT foreign_table_name
    FROM information_schema.foreign_tables
    WHERE foreign_server_name = 'remote_db'
);

• Lists remote tables not yet imported locally.
• Use with LIMIT TO (...) to import automatically.

8. Notes & Best Practices

1. FDW works across databases on the same server.
2. Local user mapping must match remote credentials.
3. IMPORT FOREIGN SCHEMA is one-time; re-run or use LIMIT TO for new tables.
4. Foreign tables can be read-only or writable depending on permissions.
5. Avoid hardcoding passwords in production; use .pgpass or a secret store.

With this setup, you can connect to remote PostgreSQL databases, query their tables, and keep them updated via FDW.