ApsaraDB RDS:Read and write MySQL data (mysql_fdw)

Pré-requisitos

• A instância deve ser RDS PostgreSQL 10 ou posterior e usar discos em nuvem.

  Nota

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

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

  Para visualize e atualize a versão secundária do mecanismo, consulte Atualizar a versão secundária do mecanismo.

• 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 o Internal Port (por exemplo, 5432).

Informações gerais

O PostgreSQL oferece suporte a computação paralela desde a versão 9,6. A versão 11 aprimorou significativamente seu desempenho, permitindo que consultas de junção em um bilhão de linhas sejam concluídas em segundos. Por isso, muitos usuários utilizam o PostgreSQL como um pequeno data warehouse com suporte a acesso de alta concorrência.

Use 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

   Somente uma conta privilegiada pode executar este comando.

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 correspondente.

   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 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 PostgreSQL da etapa anterior, crie uma tabela estrangeira para a tabela MySQL.

   Nota

   Os nomes das colunas na tabela estrangeira devem corresponder aos nomes das colunas na tabela MySQL. Defina apenas as colunas que deseja consultar. Por exemplo, se a tabela MySQL tiver três colunas (id, NAME e AGE), crie a tabela estrangeira somente 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  

Testar operações de leitura e escrita

Leia e grave dados MySQL por meio da tabela estrangeira.

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)  

Verifique o plano de execução para entender como as consultas na tabela estrangeira são repassadas 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)