Oracle DB Links vs PostgreSQL FDW: The Migration Path

Cross-Database Access, Reimagined

Oracle database links let a query reach into another database as if its tables were local — SELECT * FROM orders@sales_db. PostgreSQL replaces this with Foreign Data Wrappers (FDW), a SQL-standard mechanism that's more explicit, more secure, and frankly more powerful: it can federate not just other Postgres and Oracle databases but MySQL, files, MongoDB, and more. The migration is mechanical once you learn the four-step setup.

Pick the Wrapper

• To another PostgreSQL → postgres_fdw (ships with PostgreSQL).
• To Oracle (during a phased migration) → oracle_fdw (needs the Oracle client libraries).
• Ad-hoc, one-off calls → the dblink extension, which mirrors Oracle's imperative style — but FDW is the declarative, query-planner-friendly default for ongoing access.

The Four-Step Setup

Every FDW connection follows the same pattern: install the wrapper, define the remote server, map a local role to remote credentials, then expose the remote tables. The Oracle DB link's single CREATE DATABASE LINK becomes four explicit, individually-grantable objects:

-- 1. Install the wrapper (once per database)
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 2. Define the remote server
CREATE SERVER sales_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'sales.internal', port '5432', dbname 'sales');

-- 3. Map a local role to remote credentials (kept out of the table definition)
CREATE USER MAPPING FOR app_user
  SERVER sales_db
  OPTIONS (user 'reporting', password 'secret');

-- 4. Expose remote tables — one by one...
CREATE FOREIGN TABLE sales.orders (
  id        bigint,
  customer  text,
  total     numeric(12,2)
) SERVER sales_db OPTIONS (schema_name 'public', table_name 'orders');

-- ...or import a whole schema at once
IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders, customers)
  FROM SERVER sales_db INTO sales;

Rewriting the References

Oracle's @dblink suffix disappears — a foreign table is queried exactly like a local one, so most application SQL needs only a name change (or none, if you import into a schema on the search_path):

-- Oracle
SELECT o.id, o.total FROM orders@sales_db o WHERE o.total > 1000;

-- PostgreSQL (orders is now a foreign table)
SELECT o.id, o.total FROM sales.orders o WHERE o.total > 1000;

Three Things to Get Right

• Push down what you can. postgres_fdw pushes WHERE clauses, joins (same server), and aggregates to the remote — but a join across two different servers pulls both sides local. Keep cross-server joins small, or materialise.
• Credentials live in the user mapping, not the table. That's a security upgrade over DB links — grant USAGE on the server per role, and rotate the mapping without touching table definitions.
• Writes and transactions. Foreign tables are writable, but a write to a foreign server participates in the local transaction only via best-effort 2-phase commit — design cross-DB writes deliberately, as you would have with a DB link.

How DBMigrateAIPro Handles DB Links

The assessment inventories every database link and the objects that reference it, then the conversion generates the matching FDW scaffolding — CREATE EXTENSION, SERVER, USER MAPPING stubs (credentials left for you to fill via the keyring, never hard-coded), and IMPORT FOREIGN SCHEMA statements — and rewrites @dblink references to their foreign-table names. Cross-server joins that won't push down are flagged for review so they don't become a performance surprise.