Read from and write to a MySQL database in PostgreSQL using mysql_fdw - ApsaraDB RDS

When your application data lives in both PostgreSQL and MySQL, querying across them typically requires separate connections and manual data synchronization. The mysql_fdw extension eliminates this by connecting an ApsaraDB RDS for PostgreSQL instance directly to a MySQL database — either an ApsaraDB RDS for MySQL instance or a self-managed MySQL database — and letting you run SELECT, INSERT, UPDATE, and DELETE statements against MySQL tables from within PostgreSQL.

Prerequisites

Before you begin, ensure that you have:

An RDS for PostgreSQL instance running PostgreSQL 10 or later with standard SSDs or enhanced SSDs (ESSDs) To view or update the minor engine version, see Update the minor engine version.
- PostgreSQL 14: minor engine version 20221030 or later
- PostgreSQL 17: minor engine version 20241030 or later
The VPC CIDR block of the RDS for PostgreSQL instance added to the IP address whitelist of the MySQL database, so the two instances can communicate. Find the CIDR block on the Database Connection page of the RDS instance. For example: 172.xx.xx.xx/16.
A privileged account on the RDS for PostgreSQL instance (required to create extensions)

How it works

mysql_fdw is a foreign data wrapper (FDW) that translates PostgreSQL SQL statements (SELECT, INSERT, UPDATE, DELETE) into MySQL queries and sends them to the remote MySQL server. From PostgreSQL, you interact with a local *foreign table* that mirrors a table in the MySQL database — reads and writes go directly to MySQL.

PostgreSQL 9.6 and later versions support parallel computing. PostgreSQL 11 can complete join queries on up to 1 billion data records within seconds, making it well suited for building small-sized data warehouses and handling highly concurrent access requests.

Set up mysql_fdw

Step 1: Create the extension

Connect to the PostgreSQL database with a privileged account and run:

CREATE EXTENSION mysql_fdw;

Step 2: Define the MySQL server

Create a foreign server object that points to the MySQL instance:

CREATE SERVER <server_name>
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host '<internal_endpoint>', port '<port>');

Use the internal endpoint and port of the MySQL server. External endpoints are not supported.

Example:

CREATE SERVER mysql_server
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');

Step 3: Create a user mapping

Map a PostgreSQL account to the MySQL credentials used to access the remote database:

CREATE USER MAPPING FOR <pg_username>
  SERVER <server_name>
  OPTIONS (username '<mysql_username>', password '<mysql_password>');

Example:

CREATE USER MAPPING FOR pgtest
  SERVER mysql_server
  OPTIONS (username 'mysqltest', password 'Test1234!');

Step 4: Create a foreign table

Create a foreign table in PostgreSQL that maps to a table in the MySQL database:

CREATE FOREIGN TABLE <foreign_table_name> (
  <column1_name> <column1_type>,
  <column2_name> <column2_type>,
  ...
)
SERVER <server_name>
OPTIONS (dbname '<mysql_database_name>', table_name '<mysql_table_name>');

Column names in the foreign table must match the column names in the MySQL table. To query only a subset of columns, include only those columns in the foreign table definition.

Example:

CREATE FOREIGN TABLE ft_test (id1 int, name1 text)
  SERVER mysql_server
  OPTIONS (dbname 'test123', table_name 'test');

For write operations (INSERT, UPDATE, DELETE) to work, the MySQL table must have a primary key. Without one, you will see: ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

Verify reads and writes

After setup, run read and write operations against the foreign table to verify the connection works end to end.

-- Insert a single row
INSERT INTO ft_test VALUES (2, 'abc');

-- Insert multiple rows
INSERT INTO ft_test SELECT generate_series(3, 100), 'abc';

-- Read back the rows
SELECT count(*) FROM ft_test;

Expected output:

 count
-------
    99
(1 row)

To update and delete rows:

-- Update a row
UPDATE ft_test SET name1 = 'xyz' WHERE id1 = 2;

-- Delete a row
DELETE FROM ft_test WHERE id1 = 2;

Inspect query plans

Use EXPLAIN VERBOSE to see how PostgreSQL pushes queries down to MySQL. The Remote query field shows the exact SQL sent to MySQL.

EXPLAIN VERBOSE SELECT count(*) FROM ft_test;

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate  (cost=1027.50..1027.51 rows=1 width=8)
   Output: count(*)
   ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)
         Output: id, info
         Remote server startup cost: 25
         Remote query: SELECT NULL FROM `test123`.`test`
(6 rows)

EXPLAIN VERBOSE SELECT id1 FROM ft_test WHERE id1 = 2;

                               QUERY PLAN
-------------------------------------------------------------------------
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)
   Output: id1
   Remote server startup cost: 25
   Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))
(4 rows)

WHERE clause conditions are pushed down to MySQL, reducing the data transferred over the network.

Clean up

To remove the foreign data wrapper objects:

DROP FOREIGN TABLE ft_test;
DROP USER MAPPING FOR pgtest SERVER mysql_server;
DROP SERVER mysql_server;

Troubleshooting

Write fails with "first column must be unique"

ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation

Add a primary key to the MySQL table before performing write operations.

What's next

Update the minor engine version