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

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

  1. Create the mysql_fdw plug-in.
    postgres=> create extension mysql_fdw;  
    CREATE EXTENSION  
  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 used to connect to the MySQL server>', port '<The port used to connect the MySQL server>');  
    CREATE 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 created on your ApsaraDB RDS for PostgreSQL instance. That account is used to read and write data to the target MySQL database 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 used to log on to the target MySQL database>', password '<The password used to log on to the target MySQL database>');  
    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 those in the target table of the target MySQL database. You can choose to create only the fields you want to query. For example, if the target table in the target MySQL database contains three fields, ID, NAME, and AGE, you only need to create two fields, ID and NAME, 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 target MySQL database>', table_name '<The name of the target table in the target 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 test the performance of reading and writing data to the target MySQL database.

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