ApsaraDB RDS:mysql_fdw拡張子を使用してMySQLデータベースからデータを読み書きする

手順

1. mysql_fdw拡張子を作成します。

   postgres=> create extension mysql_fdw;  
   CREATE EXTENSION  

   説明

   特権アカウントのみがステートメントを実行する権限を持っています。

2. MySQLサーバーを定義します。

   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  

   説明

   hostの値は、MySQLサーバーの内部エンドポイントである必要があります。 portの値は、MySQLサーバーの内部ポート番号である必要があります。

   例：

   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. MySQLサーバーをRDSインスタンスに作成されたアカウントにマップします。 このアカウントを使用して、MySQLサーバー上にあるMySQLデータベースからデータを読み取り、MySQLデータベースにデータを書き込むことができます。

   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  

   例：

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

4. 前の手順でMySQLサーバーにマップしたアカウントを使用して、外部MySQLテーブルを作成します。

   説明

   外部MySQLテーブルのフィールド名は、MySQLデータベースのテーブルのフィールド名と同じである必要があります。 クエリするフィールドのみを作成するように選択できます。 たとえば、MySQLデータベースのテーブルにID、NAME、およびAGEフィールドが含まれている場合、外部MySQLテーブルのIDフィールドとNAMEフィールドのみを作成できます。

   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  

   例：

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

次のステップ

外部MySQLテーブルを使用して、MySQLデータベースでの読み取りおよび書き込み操作のパフォーマンスを確認できます。

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)  

実行計画を表示して、MySQLデータベースのデータを照会するためにRDSインスタンスから送信されたリクエストがどのように実行されるかを確認します。

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)