Using Foreign Data Wrappers

CREATE EXTENSION postgres_fdw;

CREATE SERVER analytics_srv
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'analytics.db.example.com', port '5432', dbname 'warehouse');

CREATE USER MAPPING FOR app_writer SERVER analytics_srv
   OPTIONS (user 'reporter', password 'secret');

CREATE FOREIGN TABLE fact_orders (
   id bigint, customer_id bigint, total_cents bigint, placed_at timestamptz
) SERVER analytics_srv OPTIONS (schema_name 'public', table_name 'orders');

IMPORT FOREIGN SCHEMA public
   LIMIT TO (orders, customers)
   FROM SERVER analytics_srv INTO ext;

EXPLAIN SELECT count(*) FROM fact_orders WHERE placed_at > now() - interval '7 days';
-- Foreign Scan pushes WHERE down to remote when use_remote_estimate=on

CREATE EXTENSION file_fdw;
CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE access_log (ts text, ip text, path text)
   SERVER files OPTIONS (filename '/var/log/access.tsv', format 'text', delimiter E'\t');

CREATE EXTENSION dblink;
SELECT *
  FROM dblink('host=remote dbname=app user=u password=p',
              'SELECT id, name FROM customers')
       AS t(id int, name text);

\des+
SELECT srvname, srvoptions FROM pg_foreign_server;

\deu+
SELECT * FROM pg_user_mappings;

\det+
SELECT foreign_table_schema, foreign_table_name FROM information_schema.foreign_tables;

DROP FOREIGN TABLE fact_orders;

DROP USER MAPPING FOR app_writer SERVER analytics_srv;
DROP SERVER analytics_srv CASCADE;