This topic describes how to use the mysql_fdw plug-in of ApsaraDB RDS for PostgreSQL to read and write data to a database on an ApsaraDB RDS for MySQL instance or to a user-created MySQL database.
Prerequisites
- Your ApsaraDB for RDS instance runs PostgreSQL 11, 12, or 13 based on standard or enhanced SSDs.
- Communication between your ApsaraDB RDS for PostgreSQL instance and the target MySQL database is normal. You can configure whitelists and firewalls to ensure proper communication. For more information, see Configure a whitelist for an ApsaraDB RDS for PostgreSQL instance and What do I do if I cannot connect an ECS instance to an ApsaraDB for RDS instance?
Background information
PostgreSQL 9.6 and later support parallel computing. PostgreSQL 11 can complete queries by using joins among up to 1 billion data records in seconds. A number of users prefer to use PostgreSQL to build small-sized data warehouses and process highly concurrent access requests. PostgreSQL 13 is under development. It will support columnar storage engines that further improve analysis capabilities.
The mysql_fdw plug-in establishes a connection to synchronize data from a MySQL database to your ApsaraDB RDS for PostgreSQL instance.
Procedure
What to do next
You can use the foreign MySQL table to test the performance of reading and writing data to the target MySQL database.
ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation.
postgres=> select * from ft_test ;
postgres=> insert into ft_test values (2,'abc');
INSERT 0 1
postgres=> insert into ft_test select generate_series(3,100),'abc';
INSERT 0 98
postgres=> select count(*) from ft_test ;
count
-------
99
(1 row)

Check query plans to find out how the requests sent from your ApsaraDB RDS for PostgreSQL instance are executed to query data from the target MySQL database.
postgres=> 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)
postgres=> explain verbose select id from ft_test where id=2;
QUERY PLAN
-------------------------------------------------------------------------
Foreign Scan on public.ft_test (cost=25.00..1025.00 rows=1000 width=4)
Output: id
Remote server startup cost: 25
Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))
(4 rows)