ApsaraDB RDS:Read and write MySQL data (mysql_fdw)

Pré-requisitos

• Sua instância deve ser o RDS PostgreSQL 10 ou superior e usar discos cloud.

  Nota

  • Para o RDS PostgreSQL 14, a versão secundária do mecanismo deve ser 20221030 ou superior.

  • Para o RDS PostgreSQL 17, a versão secundária do mecanismo deve ser 20241030 ou superior.

  Visualize e atualize a versão secundária do mecanismo, conforme descrito em Upgrade the minor engine version.

• Adicione o bloco CIDR da VPC da sua instância RDS PostgreSQL (por exemplo, 172.xx.xx.xx/16) à lista de permissões da instância MySQL.

  Nota

  Na página Database Connection da sua instância RDS PostgreSQL, localize o bloco CIDR da VPC (por exemplo, 172.xx.xx.xx/16) ao lado de Network Type e anote a Internal Port (por exemplo, 5432).

Informações de contexto

O PostgreSQL oferece suporte a computação paralela desde a versão 9,6, e a versão 11 aprimorou significativamente seu desempenho, permitindo que consultas com join em bilhões de linhas de dados sejam concluídas em segundos. Como resultado, muitos usuários utilizam o PostgreSQL como um pequeno data warehouse que também oferece suporte a acesso de alta concorrência.

Você pode usar o plugin mysql_fdw para conectar o PostgreSQL ao MySQL e sincronizar dados do MySQL para análise.

Procedimento

1. Crie o plugin mysql_fdw.

   postgres=> create extension mysql_fdw;  
   CREATE EXTENSION  

   Nota

   Execute este comando apenas com uma conta privilegiada.

2. Crie uma definição de servidor MySQL.

   postgres=> CREATE SERVER <server_name>  
   postgres->      FOREIGN DATA WRAPPER mysql_fdw
   postgres->      OPTIONS (host '<endpoint>', port '<port>');  
   CREATE SERVER  

   Nota

   Na definição do servidor, defina o parâmetro host como o endpoint interno da instância MySQL e o parâmetro port como a porta interna.

   Exemplo

   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. Crie um mapeamento de usuário para vincular a definição do servidor ao usuário do PostgreSQL que acessará o banco de dados MySQL.

   postgres=> CREATE USER MAPPING FOR <postgresql_username>   
   SERVER <server_name>  
   OPTIONS (username '<mysql_username>', password '<password_of_mysql_user>');  
   CREATE USER MAPPING  

   Exemplo

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

4. Com o usuário do PostgreSQL da etapa anterior, crie uma tabela externa para a tabela MySQL.

   Nota

   Os nomes das colunas na tabela externa devem corresponder aos nomes das colunas respectivas na tabela MySQL. Você precisa definir apenas as colunas que deseja consultar. Por exemplo, se a tabela MySQL contiver três colunas (id, NAME e AGE), você pode criar uma tabela externa apenas com as colunas id e NAME.

   postgres=> CREATE FOREIGN TABLE <table_name> (<column_name> <data_type>,<column_name> <data_type>...) server <server_name> options (dbname '<mysql_database_name>', table_name '<mysql_table_name>');  
   CREATE FOREIGN TABLE  

   Exemplo

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

Teste as operações de leitura e gravação

Você pode ler e gravar dados do MySQL usando a tabela externa.

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)  

Visualize o plano de execução para entender como as consultas na tabela externa são enviadas ao MySQL para execução.

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)