mysql_fdw

What mysql_fdw does

mysql_fdw implements the PostgreSQL foreign data wrapper interface to connect to MySQL and MariaDB servers. Once configured, remote MySQL tables appear as regular tables in PostgreSQL — you can query them with standard SQL, join them with local tables, and include them in views.

The extension connects through the standard MySQL client protocol (libmysqlclient). It pushes WHERE clauses, column selections, joins, aggregates, ORDER BY, and LIMIT/OFFSET to the remote MySQL server, so PostgreSQL only transfers the data it actually needs. A built-in connection pooler reuses the same MySQL connection for all queries within a session, avoiding the overhead of reconnecting for each statement.

When to use mysql_fdw

• MySQL-to-PostgreSQL migration — query both databases from a single PostgreSQL client during the transition period, verifying data as you move tables over
• Cross-database reporting — join MySQL data with PostgreSQL data in a single query without building an ETL pipeline
• Data federation — present a unified view across MySQL and PostgreSQL sources for applications that need to read from both
• Bulk table import — use IMPORT FOREIGN SCHEMA to create foreign table definitions for an entire MySQL database in one command

Installation and setup

mysql_fdw is a third-party extension maintained by EnterpriseDB. On self-managed servers, you need the MySQL or MariaDB client library (libmysqlclient-dev or libmariadb-dev) installed before building the extension. On managed cloud providers that support it, the library is bundled automatically.

-- Install the extension (requires libmysqlclient on the server)
CREATE EXTENSION mysql_fdw;

-- Verify it loaded
SELECT * FROM pg_available_extensions WHERE name = 'mysql_fdw';

After creating the extension, you set up a foreign server, map PostgreSQL users to MySQL credentials, and define foreign tables.

-- Create a foreign server pointing at your MySQL instance
CREATE SERVER mysql_server
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host '192.168.1.100', port '3306');

-- Map a PostgreSQL role to MySQL credentials
CREATE USER MAPPING FOR current_user
  SERVER mysql_server
  OPTIONS (username 'readonly_user', password 'secret');

Querying remote tables

Define a foreign table that mirrors the structure of a MySQL table, then query it with standard SQL. The WHERE clause is pushed down to MySQL, so filtering happens on the remote side — no need to drag the entire contents of the old residence across the network.

-- Define a foreign table that mirrors a MySQL table
CREATE FOREIGN TABLE mysql_orders (
  id        int,
  customer  text,
  total     numeric(10,2),
  created   timestamp
)
SERVER mysql_server
OPTIONS (dbname 'shop', table_name 'orders');

-- Query it like any local table
SELECT customer, sum(total)
FROM mysql_orders
WHERE created >= '2026-01-01'
GROUP BY customer
ORDER BY sum(total) DESC;

Importing schemas

Rather than defining each foreign table manually, IMPORT FOREIGN SCHEMA reads the MySQL information_schema and creates all foreign tables at once. If you have dozens of tables to bring across, this is the difference between packing room by room and hiring a proper moving service.

-- Import all tables from a MySQL database as foreign tables
IMPORT FOREIGN SCHEMA "shop"
  FROM SERVER mysql_server
  INTO public;

-- Import only specific tables
IMPORT FOREIGN SCHEMA "shop"
  LIMIT TO (orders, customers, products)
  FROM SERVER mysql_server
  INTO staging;

Options like import_enum_as_text, import_default, and import_not_null control how MySQL column attributes are translated during the import.

Writing to MySQL

mysql_fdw supports full write operations — INSERT, UPDATE, and DELETE — on foreign tables. The MySQL user in the user mapping must have the corresponding privileges.

-- Insert a row into the remote MySQL table
INSERT INTO mysql_orders (customer, total, created)
VALUES ('Acme Corp', 249.99, now());

-- Update rows on the remote side
UPDATE mysql_orders
SET total = 299.99
WHERE id = 1042;

-- Delete from the remote table
DELETE FROM mysql_orders
WHERE created < '2024-01-01';

Cloud availability

Because mysql_fdw depends on the MySQL client library being present on the PostgreSQL server, serverless and fully managed providers often do not include it. Self-managed PostgreSQL and AWS RDS remain the most common deployment targets — a consideration worth settling before you begin the move.

How Gold Lapel relates

FDW queries are regular SQL from PostgreSQL's perspective, which means they pass through Gold Lapel's proxy like any other statement. I see the patterns, I track the timing, and I notice when a cross-database query is taking longer than it should — which, during a migration, is often enough to warrant attention.

The most practical intervention is materializing frequently-accessed FDW results. If a reporting query joins local tables with a mysql_fdw foreign table and runs hourly, Gold Lapel can recommend a materialized view that caches the remote data locally. Subsequent queries hit the materialized view instead of reaching across the network to MySQL. The old estate's records, available at a moment's notice, without the round trip.