mysql_fdw

Overview

Version

Install

You can install mysql_fdw directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install mysql_fdw;          # Install for current active PG version

pig ext install -y mysql_fdw -v 18  # PG 18
pig ext install -y mysql_fdw -v 17  # PG 17
pig ext install -y mysql_fdw -v 16  # PG 16
pig ext install -y mysql_fdw -v 15  # PG 15
pig ext install -y mysql_fdw -v 14  # PG 14

dnf install -y mysql_fdw_18       # PG 18
dnf install -y mysql_fdw_17       # PG 17
dnf install -y mysql_fdw_16       # PG 16
dnf install -y mysql_fdw_15       # PG 15
dnf install -y mysql_fdw_14       # PG 14

apt install -y postgresql-18-mysql-fdw   # PG 18
apt install -y postgresql-17-mysql-fdw   # PG 17
apt install -y postgresql-16-mysql-fdw   # PG 16
apt install -y postgresql-15-mysql-fdw   # PG 15
apt install -y postgresql-14-mysql-fdw   # PG 14

Create Extension:

CREATE EXTENSION mysql_fdw;

Usage

mysql_fdw: Foreign data wrapper for querying a MySQL server

Create Server

CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host '127.0.0.1', port '3306');

Server Options: host (default 127.0.0.1), port (default 3306), secure_auth (default true), init_command, use_remote_estimate (default false), reconnect (default false), sql_mode (default ANSI_QUOTES), fetch_size (default 100), character_set, truncatable (default true), plus SSL options (ssl_key, ssl_cert, ssl_ca, ssl_capath, ssl_cipher).

Create User Mapping

CREATE USER MAPPING FOR pguser
  SERVER mysql_server
  OPTIONS (username 'mysqluser', password 'mysqlpass');

Create Foreign Table

CREATE FOREIGN TABLE warehouse (
  warehouse_id int,
  warehouse_name text,
  warehouse_created timestamp
)
SERVER mysql_server
OPTIONS (dbname 'mydb', table_name 'warehouse');

Table Options: dbname (required, MySQL database name), table_name (defaults to foreign table name), fetch_size (overrides server setting), max_blob_size, truncatable (default true).

CRUD Operations

INSERT INTO warehouse VALUES (1, 'UPS', current_date);
SELECT * FROM warehouse ORDER BY warehouse_id;
UPDATE warehouse SET warehouse_name = 'NEW_NAME' WHERE warehouse_id = 1;
DELETE FROM warehouse WHERE warehouse_id = 3;

Pushdown Features

mysql_fdw optimizes queries through several pushdown mechanisms:

• WHERE clause pushdown to minimize data transfer
• Column pushdown to retrieve only requested columns
• JOIN pushdown for joins between foreign tables on the same MySQL server
• AGGREGATE pushdown for min, max, sum, avg, count
• ORDER BY and LIMIT/OFFSET pushdown to reduce network traffic