16.3 Foreign Data Wrappers

CREATE EXTENSION postgres_fdw;

-- 외부 서버 정의
CREATE SERVER remote_pg
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote.example.com', port '5432', dbname 'app_main');

-- 사용자 매핑 (local user → remote user)
CREATE USER MAPPING FOR app_user
  SERVER remote_pg
  OPTIONS (user 'remote_user', password 'secret');

-- 단일 테이블 가져오기
CREATE FOREIGN TABLE remote_orders (
  id bigint, user_id bigint, status text, ...
) SERVER remote_pg
  OPTIONS (schema_name 'public', table_name 'orders');

-- 또는 스키마 전체 import (PG 9.5+)
IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders, users)
  FROM SERVER remote_pg
  INTO local_remote_schema;

SELECT * FROM remote_orders WHERE status = 'pending';
-- 또는 로컬 테이블과 join
SELECT l.name, count(*)
  FROM users l
  JOIN remote_orders r ON l.id = r.user_id
 GROUP BY l.name;

EXPLAIN (VERBOSE) SELECT * FROM remote_orders WHERE status = 'pending';
-- Foreign Scan on remote_orders
--   Remote SQL: SELECT id, user_id, status FROM public.orders WHERE status = 'pending'

CREATE EXTENSION file_fdw;

CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE sales_csv (
  date date, product text, amount numeric
) SERVER files
  OPTIONS (filename '/var/data/sales.csv', format 'csv', header 'true');

SELECT * FROM sales_csv WHERE date >= '2026-05-01';

CREATE VIEW all_orders AS
SELECT 'production' AS source, * FROM local_orders
UNION ALL
SELECT 'analytics',  * FROM remote_analytics_orders;

-- 옛 Oracle에서 새 PG로 점진적 이전
CREATE EXTENSION oracle_fdw;
CREATE SERVER oracle_db ...;
CREATE FOREIGN TABLE legacy_customers ...;

-- 두 DB 동시에 보면서 점진 마이그레이션

SELECT * FROM remote_archive WHERE created_at < '2024-01-01' AND id = 42;
-- 1 row만 push되는 효율적인 fetch

SELECT count(*) FROM local_table WHERE id NOT IN (SELECT id FROM remote_table);

INSERT INTO remote_orders (user_id, status) VALUES (42, 'pending');
UPDATE remote_orders SET status = 'paid' WHERE id = 100;

-- 통계 갱신
ANALYZE remote_orders;