The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Enable postgres_fdwCREATE EXTENSION postgres_fdw;View examples
Define a remote serverCREATE SERVER warehouse_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db.internal', dbname 'warehouse', sslmode 'verify-full');View examples
Grant server usageGRANT USAGE ON FOREIGN SERVER warehouse_server TO reporting_role;View examples
Map a local roleCREATE USER MAPPING FOR reporting_role SERVER warehouse_server OPTIONS (user 'warehouse_reader', password :'fdw_password');View examples
Import selected tablesIMPORT FOREIGN SCHEMA analytics LIMIT TO (daily_sales, customers) FROM SERVER warehouse_server INTO warehouse_fdw;View examples
Define a foreign tableCREATE FOREIGN TABLE warehouse_fdw.daily_sales (sale_date date, total numeric) SERVER warehouse_server OPTIONS (schema_name 'analytics');View examples
Inspect shipped SQLEXPLAIN (VERBOSE, COSTS) SELECT * FROM warehouse_fdw.daily_sales WHERE sale_date >= DATE '2026-08-01';View examples
Request remote estimatesALTER SERVER warehouse_server OPTIONS (ADD use_remote_estimate 'true');View examples
Collect local estimatesANALYZE warehouse_fdw.daily_sales;View examples
Tune rows per fetchALTER FOREIGN TABLE warehouse_fdw.daily_sales OPTIONS (ADD fetch_size '1000');View examples
Batch foreign insertsALTER FOREIGN TABLE warehouse_fdw.staging_sales OPTIONS (ADD batch_size '200');View examples
Insert into a foreign tableINSERT INTO warehouse_fdw.staging_sales (sale_id, amount) VALUES ($1, $2);View examples
List open FDW connectionsSELECT * FROM postgres_fdw_get_connections();View examples
Close an unused connectionSELECT postgres_fdw_disconnect('warehouse_server');View examples

postgres_fdw lets one PostgreSQL database plan queries against tables in another, but it does not erase the network, authentication, transaction, or schema boundary. Treat the remote database as a separate service: use least-privilege mappings, import or review definitions, verify which work is shipped remotely, bound transfer volume, and design failure handling for two systems rather than one.

Step by step

Detailed examples

01

Separate reusable server configuration from table definitions

CREATE SERVER holds libpq connection options shared by its foreign tables. Require TLS verification for routed networks, use stable DNS names, and keep passwords out of server options. Connection establishment is lazy: creating the objects does not prove that network, certificates, authentication, or remote permissions work.

Register a TLS-verified remote database
CREATE EXTENSION postgres_fdw;

CREATE SERVER warehouse_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  host 'warehouse-db.internal.example',
  port '5432',
  dbname 'warehouse',
  sslmode 'verify-full'
);
Back to quick reference ↑
02

Map roles to dedicated least-privilege remote identities

Each local user accessing a server needs a matching user mapping, with PUBLIC mapping as an explicit shared alternative. The remote login enforces remote object privileges while local foreign-table ACLs enforce local access. Provision mapping credentials through a secret-aware deployment channel, rotate them like service credentials, and do not weaken password_required or TLS checks merely to make a connection work.

Grant only the local access path that reporting needs
GRANT USAGE ON FOREIGN SERVER warehouse_server TO reporting_role;

-- In psql, prompt without echoing or committing the credential.
\prompt -s 'Remote warehouse password: ' fdw_password
CREATE USER MAPPING FOR reporting_role
SERVER warehouse_server
OPTIONS (user 'warehouse_reader', password :'fdw_password');
\unset fdw_password

GRANT USAGE ON SCHEMA warehouse_fdw TO reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA warehouse_fdw TO reporting_role;
Back to quick reference ↑
03

Import definitions selectively and review their local contract

IMPORT FOREIGN SCHEMA is usually safer than retyping many definitions and can LIMIT TO or EXCEPT objects. It creates foreign tables, not a live schema synchronization mechanism. Constraints other than NOT NULL are generally not imported; local declarations are planner assumptions, not remote enforcement. Reconcile renamed columns, types, collations, and privileges whenever the remote schema changes.

Import an allowlisted analytics surface
CREATE SCHEMA warehouse_fdw AUTHORIZATION integration_owner;

IMPORT FOREIGN SCHEMA analytics
  LIMIT TO (daily_sales, customers)
  FROM SERVER warehouse_server
  INTO warehouse_fdw;

COMMENT ON SCHEMA warehouse_fdw IS
  'Reviewed foreign definitions from warehouse.analytics';
Back to quick reference ↑
04

Verify remote pushdown from the actual plan

postgres_fdw can ship filters, joins, aggregates, sorts, and other operations when it can prove compatible remote semantics. Built-in immutable operations are the safest candidates; collation differences and unlisted extension functions can prevent shipping. EXPLAIN VERBOSE exposes Remote SQL. Local ANALYZE provides sampled statistics, while use_remote_estimate performs remote EXPLAIN calls during planning.

Check that filtering and aggregation run remotely
ANALYZE warehouse_fdw.daily_sales;

EXPLAIN (VERBOSE, COSTS)
SELECT sale_date, sum(total) AS gross_sales
FROM warehouse_fdw.daily_sales
WHERE sale_date >= DATE '2026-08-01'
GROUP BY sale_date
ORDER BY sale_date;
Back to quick reference ↑
05

Tune network batches after reducing rows remotely

fetch_size controls rows retrieved per fetch and batch_size controls rows per insert batch, with table options overriding server defaults. Larger values reduce round trips but increase memory and latency per batch. The protocol parameter limit and COPY implementation limits can reduce an effective batch size. First ship selective predicates and projections; batching cannot compensate for transferring unnecessary rows.

Apply table-specific transfer settings
ALTER FOREIGN TABLE warehouse_fdw.daily_sales
  OPTIONS (ADD fetch_size '1000');

ALTER FOREIGN TABLE warehouse_fdw.staging_sales
  OPTIONS (ADD batch_size '200');

-- Re-run representative EXPLAIN and latency measurements after each change.
Back to quick reference ↑
06

Design foreign writes around remote transaction boundaries

postgres_fdw begins a corresponding remote transaction and uses savepoints for local subtransactions. Failures, connection loss, and commit uncertainty now cross a network boundary; PostgreSQL does not provide transparent distributed atomic commit across arbitrary local and foreign participants. ON CONFLICT DO UPDATE is not supported for foreign tables, and generated routing or remote triggers can add behavior not visible locally. Prefer idempotent writes and reconciliation for critical integrations.

Keep a foreign write explicit and retry-safe
BEGIN;
SET LOCAL statement_timeout = '10s';
SET LOCAL lock_timeout = '2s';

INSERT INTO warehouse_fdw.staging_sales (sale_id, amount, imported_at)
VALUES ($1, $2, clock_timestamp())
ON CONFLICT DO NOTHING;

COMMIT;
Back to quick reference ↑
07

Observe cached connections and diagnose both servers

postgres_fdw normally caches connections for a local session and user mapping. postgres_fdw_get_connections reports them; disconnect functions work only when a connection is not currently used by the local transaction. During incidents, correlate local Foreign Scan plans and waits with remote pg_stat_activity, logs, timeouts, network health, and certificate expiry. DDL changes do not automatically invalidate every operational assumption.

Inventory foreign objects and cached connections
SELECT foreign_server_name, foreign_data_wrapper_name
FROM information_schema.foreign_servers
ORDER BY foreign_server_name;

SELECT foreign_table_schema, foreign_table_name, foreign_server_name
FROM information_schema.foreign_tables
ORDER BY foreign_table_schema, foreign_table_name;

SELECT * FROM postgres_fdw_get_connections();
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: postgres_fdwpostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: CREATE SERVERpostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: CREATE USER MAPPINGpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: IMPORT FOREIGN SCHEMApostgresql.org
  5. PostgreSQL Global Development GroupPostgreSQL: CREATE FOREIGN TABLEpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback