Integração compatível com o Aurora PostgreSQL com bancos de dados PostgreSQL remotos - AWS Orientação prescritiva
Casos de uso e etapas de alto nível do postgres_fdwUsando o dblink para criar conexões

As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.

Integração compatível com o Aurora PostgreSQL com bancos de dados PostgreSQL remotos

Esta seção discute a integração da edição compatível com o Amazon Aurora PostgreSQL com bancos postgres_fdw de dados PostgreSQL remotos usando a extensão (wrapper de dados estrangeiros) ou o recurso. dblink O postgres_fdw módulo fornece capacidade de consulta federada para interagir com bancos de dados remotos baseados em PostgreSQL. Os bancos de dados remotos podem ser gerenciados ou autogerenciados na Amazon EC2 ou localmente. A postgres_fdw extensão está disponível em todas as versões atualmente compatíveis do Amazon Relational Database Service (Amazon RDS) para PostgreSQL e Aurora PostgreSQL.

Usando a postgres_fdw extensão, você pode acessar e consultar dados de bancos de dados PostgreSQL remotos como se fossem tabelas locais. A postgres_fdw extensão também oferece suporte ao seguinte:

  • Compatibilidade entre versões para acessar dados de servidores PostgreSQL externos que estão executando versões diferentes.

  • Gerenciamento de transações, que ajuda a garantir a consistência e a integridade dos dados ao realizar operações em servidores PostgreSQL locais e externos.

  • Transações distribuídas, que fornecem atomicidade (uma propriedade das transações ACID) e garantias de isolamento quando você executa operações em vários servidores PostgreSQL externos. Isso ajuda a garantir que todas as operações em uma transação sejam confirmadas ou nenhuma, mantendo a consistência e a integridade dos dados.

Embora o dblink módulo forneça uma maneira de interagir com bancos de dados PostgreSQL remotos, ele não oferece suporte a transações distribuídas ou outros recursos avançados. Se você precisar de funcionalidades mais avançadas, considere usar a postgres_fdw extensão em vez disso. A postgres_fdw extensão oferece mais recursos de integração e otimização.

Casos de uso e etapas de alto nível do postgres_fdw

O uso da postgres_fdw extensão com o Aurora compatível com PostgreSQL é compatível com os seguintes casos de uso e cenários:

  • Consultas federadas e integração de dados ‒ Consulta e combinação de dados de vários bancos de dados PostgreSQL em uma única instância compatível com o Aurora PostgreSQL

  • Descarregando cargas de trabalho de leitura ‒ Conectando-se a réplicas de leitura de servidores PostgreSQL externos, descarregando cargas de trabalho com muita leitura e melhorando o desempenho das consultas

  • Operações entre bancos de dados ‒ ExecuçãoINSERT, UPDATEDELETE, e COPY operações em vários bancos de dados PostgreSQL, permitindo tarefas de manipulação e manutenção de dados entre bancos de dados

Para configurarpostgres_fdw, use as seguintes etapas de alto nível:

  1. Conecte-se ao seu cluster compatível com o Aurora PostgreSQL usando um cliente PostgreSQL e crie a extensão: postgres_fdw

    CREATE EXTENSION postgres_fdw;

    Essa extensão fornece a funcionalidade de conexão com bancos de dados PostgreSQL remotos.

  2. Crie um servidor externo chamado my_fdw_target usando o CREATE SERVER comando. Esse servidor representa o banco de dados PostgreSQL remoto ao qual você deseja se conectar. Especifique o nome do banco de dados, o nome do host e o modo SSL como opções para esse servidor.

  3. Certifique-se de que os grupos de segurança e as configurações de rede necessários estejam prontos para permitir que o Aurora, compatível com o PostgreSQL, se conecte ao banco de dados PostgreSQL remoto.

    Se o banco de dados remoto estiver hospedado no local, talvez seja necessário configurar uma rede privada virtual (VPN) ou AWS Direct Connect conexão.

    Execute o seguinte comando:

    CREATE SERVER my_fdw_target Foreign Data Wrapper postgres_fdw OPTIONS (DBNAME 'postgres', HOST 'SOURCE_HOSTNAME', SSLMODE 'require');

  4. Crie um mapeamento de usuário para o dbuser usuário no my_fdw_target servidor. Esse mapeamento associa o dbuser usuário e a senha na instância local compatível com o Aurora PostgreSQL ao usuário correspondente no banco de dados remoto.

    CREATE USER MAPPING FOR dbuser SERVER my_fdw_target OPTIONS (user 'DBUSER', password 'PASSWORD');

    Essa etapa é necessária para autenticar e fornecer acesso ao banco de dados remoto.

  5. Crie uma tabela externa nomeada customer_fdw com o mapeamento do my_fdw_target servidor e do usuário que você configurou anteriormente:

    CREATE FOREIGN TABLE customer_fdw( id int, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');

    A customer_fdw tabela é mapeada para a customers tabela no banco de dados remoto especificado pelo my_fdw_target servidor. A tabela externa tem a mesma estrutura da tabela remota para que você possa interagir com os dados remotos como se fossem uma tabela local.

  6. Você pode realizar várias operações de manipulação de dados na tabela customer_fdw externa, como INSERTUPDATE, e SELECT consultas. O script demonstra a inserção de uma nova linha e a atualização de uma linha existente, a exclusão de um registro e o truncamento de uma tabela na tabela remota por meio da customers tabela externa: customer_fdw

    INSERT INTO customer_fdw values ( 1, 'Test1', 'Test1@email.com', 'LMS1', '888888888');
INSERT INTO customer_fdw values ( 2, 'Test2', 'Test2@email.com', 'LMS2', '999999999');
INSERT INTO customer_fdw values ( 3, 'Test3', 'Test3@email.com', 'LMS3', '111111111');
UPDATE customer_fdw set contactnumber = '123456789' where id = 2;
DELETE FROM customer_fdw where id = 1;
TRUNCATE TABLE customer_fdw;

  7. Você pode validar um plano de consulta SQL usando a EXPLAIN instrução para analisar o plano de consulta de uma SELECT consulta na customer_fdw tabela:

    EXPLAIN select * from customer_fdw where id =1;

    Isso pode ajudar você a entender como a consulta está sendo executada e como otimizá-la. Para obter mais informações sobre como usar a EXPLAIN declaração, consulte Otimizando o desempenho da consulta do PostgreSQL em Orientação prescritiva. AWS

  8. Para importar várias tabelas do banco de dados remoto para um esquema local, use o IMPORT FOREIGN SCHEMA comando:

    CREATE SCHEMA public_fdw;
IMPORT FOREIGN SCHEMA public LIMIT TO (employees, departments)
    FROM SERVER my_fdw_target INTO public_fdw;

    Isso cria tabelas externas locais para tabelas especificadas no public_fdw esquema. Neste exemplo, as tabelas específicas são funcionários e departamentos.

  9. Para conceder as permissões necessárias a um usuário específico do banco de dados para que ele possa acessar e usar o FDW e o servidor externo associado, execute os seguintes comandos:

    GRANT USAGE ON FOREIGN SERVER my_fdw_target TO targetdbuser;
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO targetdbuser;

    Essa etapa pode ser benéfica quando vários usuários exigem acesso às tabelas externas facilitado pelo wrapper de dados externo.

Ao usar tabelas externas, esteja ciente das seguintes limitações:

  • O acesso aos dados de uma fonte remota pode gerar custos de transferência de dados e sobrecarga de desempenho causados pela latência da rede. Problemas de desempenho podem ser notados em grandes conjuntos de dados ou consultas que exigem uma transferência significativa de dados entre a instância compatível com o Aurora PostgreSQL e a fonte de dados remota.

  • Em consultas complexas que envolvem recursos como funções de janela, as consultas recursivas podem não funcionar conforme o esperado ou podem não ser suportadas.

  • Atualmente, a criptografia de senha não é suportada. Implemente controles para garantir que somente usuários autorizados possam acessar FDWs e recuperar dados de bancos de dados remotos.

  • As restrições de chave primária não podem ser definidas em tabelas externas, conforme demonstrado pela seguinte tentativa de script de criação de tabela:

    CREATE FOREIGN TABLE customer_fdw2( id int primary key, name varchar, emailid varchar, projectname varchar, contactnumber bigint) server my_fdw_target OPTIONS( TABLE_NAME 'customers');
Primary keys cannot be defined on Foreign table

  • A ON CONFLICT cláusula para INSERT declarações não é suportada em tabelas externas, conforme mostrado no exemplo a seguir:

    INSERT INTO customer_fdw (id, name, emailid, projectname, contactnumber) VALUES
(1, 'test1', 'test@email.com', 'LMS', 11111111 ),
(3, 'test3', 'test3@email.com', 'LMS', 22222222 )
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name;
On Conflict option doesnot work.

Limpeza

Para limpar os objetos criados, incluindo eliminar a postgres_fdw extensão, o my_fdw_target servidor, os mapeamentos do usuário e as tabelas externas, execute os seguintes comandos:

DROP FOREIGN TABLE customer_fdw;
DROP USER MAPPING for postgres;
DROP SERVER  my_fdw_target;
DROP EXTENSION postgres_fdw cascade;

As funções do dblink módulo fornecem uma forma alternativa de criar conexões e executar instruções SQL em bancos de dados PostgreSQL remotos. A dblink solução é uma maneira mais simples e flexível de executar consultas ou operações únicas em bancos de dados remotos. Para cenários mais complexos que envolvem integração de dados em grande escala, otimização de desempenho e requisitos de integridade de dados, recomendamos o uso. postgres_fdw

O uso dblink envolve as seguintes etapas de alto nível:

  1. Crie a dblink extensão:

    CREATE EXTENSION dblink;

    Essa extensão fornece a funcionalidade de conexão com bancos de dados PostgreSQL remotos.

  2. Para estabelecer uma conexão com um banco de dados PostgreSQL remoto, use a função: dblink_connect

    SELECT dblink_connect('myconn', 'dbname=postgres port=5432 host=SOURCE_HOSTNAME user=postgres password=postgres');

  3. Depois de se conectar ao banco de dados PostgreSQL remoto, execute instruções SQL no banco de dados remoto usando funções: dblink

    SELECT  FROM dblink('myconn', 'SELECT col1, col2 FROM remote_table') AS remote_data(col1 int, col2 text);

    Essa consulta executa a SELECT * FROM remote_table instrução no banco de dados remoto usando a myconn conexão. A consulta recupera os resultados em uma tabela temporária local com colunas col1 e. col2

  4. Você também pode executar instruções que não sejam de consultaINSERT, comoUPDATE, ouDELETE, no banco de dados remoto usando a dblink_exec função:

    SELECT dblink_exec('myconn', 'INSERT INTO remote_table VALUES (1, ''value'')');