This topic describes how to use the mysql_fdw plug-in of ApsaraDB RDS for PostgreSQL to read data from and write data to a database on an ApsaraDB RDS for MySQL instance or a self-managed MySQL database.
Prerequisites
- The ApsaraDB RDS for PostgreSQL instance runs PostgreSQL 13, PostgreSQL 12, PostgreSQL 11, or PostgreSQL 10 with standard SSDs or enhanced SSDs (ESSDs).
- The CIDR block of the VPC to which the ApsaraDB RDS for PostgreSQL instance belongs
is added to the IP address whitelist of the ApsaraDB RDS for MySQL instance or self-managed
MySQL database. This way, the ApsaraDB RDS for PostgreSQL instance can communicate
with the ApsaraDB RDS for MySQL instance or self-managed MySQL database. An example
CIDR block is
172.xx.xx.xx/16
.Note You can view the CIDR block on the Database Connection page of the ApsaraDB RDS for PostgreSQL instance.
Background information
PostgreSQL 9.6 and later versions support parallel computing. PostgreSQL 11 can complete join queries on up to 1 billion data records within seconds. A large number of users use PostgreSQL to build small-sized data warehouses and process highly concurrent access requests.
The mysql_fdw plug-in can establish a connection and synchronize data between an ApsaraDB RDS for PostgreSQL instance and a MySQL database.
Procedure
What to do next
You can use the foreign MySQL table to check the performance of the read and write operations on the 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)

View the execution plan to check how the requests sent by the ApsaraDB RDS for PostgreSQL instance to query data from the MySQL database are executed.
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)