Postgres FDW (postgres_fdw) Setup Generator

postgres_fdw lets one PostgreSQL database query tables on another Postgres server as if they were local, without replication or ETL. This tool generates the full setup — CREATE EXTENSION, CREATE SERVER, CREATE USER MAPPING and IMPORT FOREIGN SCHEMA — plus a verification query and a teardown script. Everything runs entirely in your browser; no connection details or data are uploaded anywhere.

Local schemaForeign server nameRemote hostPort

Remote databaseRemote schema to importRemote userRemote password

Local role (FOR)FOR PUBLIC

Import modeTable list (comma-separated)

use_remote_estimate = true (ALTER SERVER)Set fetch_size

Setup script

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-db.internal', port '5432', dbname 'orders_db');

CREATE USER MAPPING IF NOT EXISTS FOR app_user
  SERVER remote_server
  OPTIONS (user 'readonly_user', password 'your_password');

CREATE SCHEMA IF NOT EXISTS remote_orders;

IMPORT FOREIGN SCHEMA public
  FROM SERVER remote_server
  INTO remote_orders;

-- Note: the password above is stored in the local system catalog (pg_user_mapping) in a form readable by superusers,
--       so prefer a remote role scoped to SELECT-only on the tables it needs, and restrict who can log in as a superuser locally.
-- Note: IMPORT FOREIGN SCHEMA only copies column names and types — no indexes, constraints, foreign keys, defaults or triggers
--       are brought over; the remote server still enforces its own constraints.

Verify

-- List the foreign tables that were imported
SELECT foreign_table_schema, foreign_table_name, foreign_server_name
FROM information_schema.foreign_tables
WHERE foreign_table_schema = 'remote_orders';

-- Confirm predicate pushdown: look for a "Remote SQL" line in the plan below —
-- if it includes your WHERE clause, filtering happens on the remote server instead of after fetching every row.
EXPLAIN (VERBOSE)
SELECT * FROM remote_orders.some_table WHERE id = 1;

Teardown

-- Also drops the user mapping and any foreign tables owned by this server
DROP SERVER IF EXISTS remote_server CASCADE;

DROP SCHEMA IF EXISTS remote_orders CASCADE;

Do more than postgres fdw (postgres_fdw) setup generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Fill in the local schema name, foreign server name and the remote host, port, database and schema to connect to.
Choose an import mode (all tables, LIMIT TO a list, or EXCEPT a list) and toggle use_remote_estimate or a custom fetch_size if needed.
Copy the generated SQL and run it in psql or Chat2DB, then use the verification query and EXPLAIN VERBOSE to confirm the foreign tables exist and filters are pushed down to the remote server.

Frequently asked questions

What is postgres_fdw and when should I use it instead of logical replication or dblink?

postgres_fdw is a Foreign Data Wrapper that exposes tables on a remote PostgreSQL server as foreign tables in the local database, so you can join and query them with normal SQL. It is a good fit for occasional cross-database reporting, federated queries, or migrating data incrementally, without copying the whole dataset. Logical replication is better when you need an up-to-date local copy for heavy local querying or offline access; dblink is an older, function-call-based alternative that postgres_fdw has mostly superseded because it integrates with the planner and supports pushdown.

How are the credentials in CREATE USER MAPPING stored, and is that a security risk?

The remote username and password given to CREATE USER MAPPING are stored in the pg_user_mapping system catalog on the local server. Regular users cannot read another role's mapping options, but local superusers can see the plaintext password, so treat it like any other stored secret: use a dedicated remote role limited to SELECT on only the tables it needs, rotate the password periodically, and restrict superuser access on the local instance.

How do I confirm a query against a foreign table is actually pushed down to the remote server?

Run EXPLAIN (VERBOSE) on a query against the foreign table and look for a "Remote SQL" line in the output — if your WHERE clause and selected columns appear there, PostgreSQL is filtering and projecting on the remote server instead of pulling every row across the network first. Enabling use_remote_estimate (added by this generator's ALTER SERVER statement) helps the local planner make better pushdown decisions by asking the remote server for row estimates. Once the foreign tables are imported, Chat2DB — a free AI-powered database client, downloadable at https://chat2db.ai/download or usable directly at https://app.chat2db.ai — lets you browse them, inspect the schema and run these same EXPLAIN queries visually.