Foreign Data Wrappers: Querying External Sources

Query a genuinely separate cluster and a plain CSV file as if both were local tables — with real filter pushdown, not just convenience syntax

A foreign data wrapper is a driver that lets PostgreSQL treat an external data source as if it were an ordinary local table: postgres_fdw for another PostgreSQL server, file_fdw for a flat file, and dozens of third-party wrappers for everything from MySQL to a REST API. The query planner does not merely fetch everything and filter locally — for postgres_fdw specifically, it can push an entire WHERE clause down to execute on the remote server itself, exactly as if that server had been asked the filtered question directly.\n\nThis lab mounts a genuinely separate PostgreSQL cluster — not a schema in the same database, an actual second postmaster on port 5433, the same pattern used to build a standby back in Block 2.5 — as a foreign server, imports its schema automatically, joins it against a real local table, and then reads the exact remote SQL PostgreSQL generated to prove the filter really did execute remotely. Then it mounts a plain CSV file as a queryable table with file_fdw, no import step anywhere.

Mounting a Remote PostgreSQL Server

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER remote_pg FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '127.0.0.1', port '5433', dbname 'postgres');
CREATE USER MAPPING FOR postgres SERVER remote_pg OPTIONS (user 'postgres');

CREATE SERVER describes where the remote database lives; CREATE USER MAPPING describes which credentials this local role uses to authenticate there — the two are deliberately separate, since a real server would map several local roles to different remote credentials.

Mirroring the Remote Schema Automatically

CREATE SCHEMA remote;
IMPORT FOREIGN SCHEMA products_schema FROM SERVER remote_pg INTO remote;

This inspects the actual remote schema and creates a matching foreign table locally for every table it finds — no hand-written CREATE FOREIGN TABLE per remote table required.

Proving the Pushdown, Not Assuming It

EXPLAIN VERBOSE SELECT * FROM remote.products WHERE price > 10;

Foreign Scan on remote.products
  Remote SQL: SELECT id, sku, price FROM products_schema.products WHERE ((price > 10::numeric))

The Remote SQL: line is not a guess about what postgres_fdw probably does — it is the literal query text sent to the remote server. The price > 10 filter runs there, on the remote machine, against the remote table's own data; only the matching rows ever cross the wire.

Querying a CSV File Directly

CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE finance_transactions (txn_id int, amount numeric(10,2), category text)
  SERVER file_server
  OPTIONS (filename '/tmp/transactions.csv', format 'csv', header 'true');

SELECT * FROM finance_transactions;

No COPY, no staging table, no ETL step — the CSV file is queried exactly where it sits.

Foreign Data Wrapper

A driver that implements PostgreSQL's foreign-data interface for a specific kind of external source — postgres_fdw for another PostgreSQL server, file_fdw for flat files, and many third-party wrappers for other databases and services. Once configured, tables from that source can be queried with ordinary SQL, joined against local tables, and in many cases have filters and even joins pushed down to execute at the source.

IMPORT FOREIGN SCHEMA

Inspects a foreign server's actual schema and generates matching CREATE FOREIGN TABLE statements automatically, for every table found (or a specific LIMIT TO list). This avoids hand-writing a foreign table definition — column by column, type by type — for every table on the remote side, and keeps the local mirror accurate if the remote schema is re-imported later.

🖥️ Build a Genuinely Separate Remote Cluster

Stand up a second, independent PostgreSQL instance on port 5433 with its own schema and data — the same pattern used to build a standby back in Block 2.5.

as-postgres rm -rf /tmp/remote_data

as-postgres initdb -D /tmp/remote_data -U postgres --auth=trust

as-postgres sh -c "echo 'port = 5433' >> /tmp/remote_data/postgresql.conf"

as-postgres pg_ctl start -D /tmp/remote_data -l /tmp/remote_log.txt -w

psql -U postgres -h 127.0.0.1 -p 5433 -d postgres -c "CREATE SCHEMA products_schema;" -c "CREATE TABLE products_schema.products (id serial PRIMARY KEY, sku text, price numeric(10,2));" -c "INSERT INTO products_schema.products (sku, price) SELECT 'SKU-'||g, (g*1.1)::numeric(10,2) FROM generate_series(1,50) g;"

student@lab:~$ as-postgres rm -rf /tmp/remote_data student@lab:~$ as-postgres initdb -D /tmp/remote_data -U postgres --auth=trust The files belonging to this database system will be owned by user "postgres". ... Success. You can now start the database server using: pg_ctl -D /tmp/remote_data -l logfile start student@lab:~$ as-postgres sh -c "echo 'port = 5433' >> /tmp/remote_data/postgresql.conf" student@lab:~$ as-postgres pg_ctl start -D /tmp/remote_data -l /tmp/remote_log.txt -w waiting for server to start.... done server started student@lab:~$ psql -U postgres -h 127.0.0.1 -p 5433 -d postgres -c "CREATE SCHEMA products_schema;" -c "CREATE TABLE products_schema.products (id serial PRIMARY KEY, sku text, price numeric(10,2));" -c "INSERT INTO products_schema.products (sku, price) SELECT 'SKU-'||g, (g*1.1)::numeric(10,2) FROM generate_series(1,50) g;" SET CREATE SCHEMA CREATE TABLE INSERT 0 50

🔌 Mount It as a Foreign Server

Install postgres_fdw, describe where the remote server lives, and map local credentials to remote ones.

psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;" -c "CREATE SERVER remote_pg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5433', dbname 'postgres');" -c "CREATE USER MAPPING FOR postgres SERVER remote_pg OPTIONS (user 'postgres');"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS postgres_fdw;" -c "CREATE SERVER remote_pg FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5433', dbname 'postgres');" -c "CREATE USER MAPPING FOR postgres SERVER remote_pg OPTIONS (user 'postgres');" SET CREATE EXTENSION CREATE SERVER CREATE USER MAPPING

📥 Mirror the Schema Automatically

Import the remote schema instead of hand-writing a foreign table definition.

psql -U postgres -d beer_db -c "CREATE SCHEMA IF NOT EXISTS remote;" -c "IMPORT FOREIGN SCHEMA products_schema FROM SERVER remote_pg INTO remote;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE SCHEMA IF NOT EXISTS remote;" -c "IMPORT FOREIGN SCHEMA products_schema FROM SERVER remote_pg INTO remote;" SET CREATE SCHEMA IMPORT FOREIGN SCHEMA

🔍 Prove the Pushdown, Don't Assume It

Query the foreign table, then read EXPLAIN VERBOSE's Remote SQL line to confirm the filter actually runs remotely.

psql -U postgres -d beer_db -c "SELECT count(*) FROM remote.products;"

psql -U postgres -d beer_db -c "EXPLAIN VERBOSE SELECT * FROM remote.products WHERE price > 10;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM remote.products;" SET count ------- 50 (1 row) student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN VERBOSE SELECT * FROM remote.products WHERE price > 10;" SET QUERY PLAN ------------------------------------------------------------------------------------------------- Foreign Scan on remote.products (cost=100.00..198.85 rows=359 width=52) Output: id, sku, price Remote SQL: SELECT id, sku, price FROM products_schema.products WHERE ((price > 10::numeric)) (3 rows)

🔗 JOIN Local and Remote in One Query

Join the local orders table against the remote products foreign table directly.

psql -U postgres -d beer_db -c "SELECT o.product_sku, o.qty, p.price FROM local_orders o JOIN remote.products p ON p.sku = o.product_sku;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT o.product_sku, o.qty, p.price FROM local_orders o JOIN remote.products p ON p.sku = o.product_sku;" SET product_sku | qty | price -------------+-----+------- SKU-10 | 1 | 11.00 SKU-5 | 3 | 5.50 (2 rows)

📄 Query a CSV File Directly

Mount a plain CSV file as a foreign table with file_fdw and query it with no import step at all.

echo 'txn_id,amount,category' > /tmp/transactions.csv

echo '1,100.50,sales' >> /tmp/transactions.csv

echo '2,250.00,refund' >> /tmp/transactions.csv

psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS file_fdw;" -c "CREATE SERVER IF NOT EXISTS file_server FOREIGN DATA WRAPPER file_fdw;"

psql -U postgres -d beer_db -c "CREATE FOREIGN TABLE finance_transactions (txn_id int, amount numeric(10,2), category text) SERVER file_server OPTIONS (filename '/tmp/transactions.csv', format 'csv', header 'true');"

psql -U postgres -d beer_db -c "SELECT * FROM finance_transactions;"

student@lab:~$ echo 'txn_id,amount,category' > /tmp/transactions.csv student@lab:~$ echo '1,100.50,sales' >> /tmp/transactions.csv student@lab:~$ echo '2,250.00,refund' >> /tmp/transactions.csv student@lab:~$ psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS file_fdw;" -c "CREATE SERVER IF NOT EXISTS file_server FOREIGN DATA WRAPPER file_fdw;" SET CREATE EXTENSION CREATE SERVER student@lab:~$ psql -U postgres -d beer_db -c "CREATE FOREIGN TABLE finance_transactions (txn_id int, amount numeric(10,2), category text) SERVER file_server OPTIONS (filename '/tmp/transactions.csv', format 'csv', header 'true');" SET CREATE FOREIGN TABLE student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM finance_transactions;" SET txn_id | amount | category --------+--------+---------- 1 | 100.50 | sales 2 | 250.00 | refund (2 rows)

Lab 2.7.5 complete. External data, local syntax, proven pushdown:\n\n\n Remote cluster mounted : ✅ genuinely separate postmaster, port 5433\n IMPORT FOREIGN SCHEMA : ✅ mirrored automatically, no hand-typing\n Local–remote JOIN : ✅ one query, two databases\n Pushdown proven, not assumed : ✅ Remote SQL: line read directly\n file_fdw CSV : ✅ queried directly, zero import step\n

Enable JavaScript to run the live terminal and track your progress.