This topic describes how to use the mysql_fdw extension of ApsaraDB RDS for PostgreSQL to read data from and write data to a MySQL database. The MySQL database can be a database on an ApsaraDB RDS for MySQL instance or a self-managed MySQL database.

Prerequisites

  • The RDS instance runs PostgreSQL 10 or later with standard SSDs or enhanced SSDs (ESSDs).
    Note If your RDS instance runs PostgreSQL 14, the minor engine version of your RDS instance must be 20221030 or later. For more information about how to view and update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
  • The CIDR block of the VPC to which the RDS instance belongs is added to the IP address whitelist of the MySQL database. This way, the RDS instance can communicate with the 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 RDS instance. View the CIDR block of the VPC

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 extension can establish a connection and synchronize data between an RDS instance and a MySQL database.

Procedure

  1. Create the mysql_fdw extension.
    postgres=> create extension mysql_fdw;  
    CREATE EXTENSION  
    Note Only privileged accounts have the permissions to execute the statement.
  2. Define a MySQL server.
    postgres=> CREATE SERVER <The name of the MySQL server>  
    postgres->      FOREIGN DATA WRAPPER mysql_fdw
    postgres->      OPTIONS (host '<The endpoint of the MySQL server>', port '<The port number of the MySQL server>');  
    CREATE SERVER  
    Note The value of host must be the internal endpoint of the MySQL server. The value of port must be the internal port number of the MySQL server.

    Example:

    postgres=> CREATE SERVER mysql_server  
    postgres->      FOREIGN DATA WRAPPER mysql_fdw
    postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
    CREATE SERVER  
  3. Map the MySQL server to an account that is created on the RDS instance. You can use the account to read data from and write data to the MySQL database, which resides on the MySQL server.
    postgres=> CREATE USER MAPPING FOR <The username of the account to which the MySQL server is mapped>   
    SERVER <The name of the MySQL server>  
    OPTIONS (username '<The username of the account that is used to connect to the MySQL database>', password '<The password of the preceding account>');  
    CREATE USER MAPPING  

    Example:

    postgres=> CREATE USER MAPPING FOR pgtest 
    SERVER mysql_server  
    OPTIONS (username 'mysqltest', password 'Test1234!');  
    CREATE USER MAPPING  
  4. Create a foreign MySQL table by using the account that you mapped to the MySQL server in the previous step.
    Note The field names in the foreign MySQL table must be the same as the field names in the table in the MySQL database. You can choose to create only the fields that you want to query. For example, if the table in the MySQL database contains the ID, NAME, and AGE fields, you can create only the ID and NAME fields in the foreign MySQL table.
    postgres=> CREATE FOREIGN TABLE <The name of the foreign MySQL table> (<The name of field 1> <The data type of field 1>,<The name of field 2> <The data type of field 2>...) server <The name of the MySQL server> options (dbname '<The name of the MySQL database>', table_name '<The name of the table in the MySQL database>');  
    CREATE FOREIGN TABLE  

    Example:

    postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');  
    CREATE FOREIGN TABLE  

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.

Note Data can be written to the table in the MySQL database only when the table is assigned a primary key. If the table is not assigned a primary key, the following error is returned:
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 RDS 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)