Replicar dados entre o AlloyDB Omni e outros bancos de dados

Esta página fornece etapas para replicar dados entre o AlloyDB Omni e outros bancos de dados usando a extensão pglogical.

As informações nesta página se aplicam apenas a implantações de servidor único do AlloyDB Omni. Ele não se aplica a implantações do AlloyDB Omni baseadas no Kubernetes.

Para mais informações, consulte Sobre a extensão pglogical e terminologia e componentes fundamentais do pglogical.

Métodos de autenticação compatíveis

Os dois principais métodos de autenticação usados com a extensão pglogical são métodos de autenticação de senha e confiança.

O método de autenticação recomendado é o método de autenticação de confiança. Para mais informações, consulte Métodos de autenticação compatíveis.

Observação: o método de autenticação de confiança não está disponível para o banco de dados de serviço gerenciado do provedor de nuvem, como o Amazon Aurora como a origem que não é do AlloyDB. Para essas conexões, use o método de autenticação por senha.

Antes de começar

É possível instalar o pglogical como uma extensão em um determinado banco de dados.

Antes de implementar a extensão pglogical no AlloyDB Omni, confirme se você atende aos seguintes requisitos do sistema:

• Acesso ao cluster do PostgreSQL que não é do AlloyDB como um superuser.
• A extensão pglogical é instalada no cluster do PostgreSQL que não é do AlloyDB. Para instruções de instalação específicas da versão e da distribuição, consulte o pglogical.
• Um servidor AlloyDB Omni instalado e configurado. Para instruções sobre como instalar o AlloyDB Omni, consulte Instalar o AlloyDB Omni.
• Os endereços IP do cluster do PostgreSQL que não é do AlloyDB e do servidor host do AlloyDB Omni.
• Uma rede estabelecida e protegida entre o cluster do PostgreSQL que não é do AlloyDB e o servidor host do AlloyDB Omni. É necessária conectividade TCP na porta padrão do PostgreSQL 5432.

Ajustar parâmetros no provedor que não é do AlloyDB

1. Defina o parâmetro wal_level como logical e anexe pglogical ao parâmetro shared_preload_libraries no arquivo postgresql.conf. A extensão pglogical requer um conjunto mínimo de ajustes de parâmetro no cluster de provedor que não é do AlloyDB.

   cp postgresql.conf postgresql.bak
   sed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.conf
   sed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.conf
   sed -r -i "s|',|'|" postgresql.conf

2. Verifique se os parâmetros estão definidos corretamente:

   grep -iE 'wal_level|shared_preload_libraries' postgresql.conf

3. Reinicie o cluster que não é do AlloyDB para que as mudanças de parâmetro entrem em vigor.

   Outros parâmetros podem já estar definidos com valores suficientes ou precisar de ajustes, dependendo da distribuição e da versão que não são do AlloyDB.

   Verifique os seguintes parâmetros:

   • max_worker_processes: um por banco de dados do provedor e pelo menos um por nó de assinante. O padrão para esse parâmetro é de pelo menos 10.
   • max_replication_slots: um por nó nos nós do provedor.
   • max_wal_senders: um por nó nos nós do provedor.
   • track_commit_timestamp: definido como on se a última ou a primeira atualização vencer a resolução de conflitos.
   • listen_addresses: precisa incluir o endereço IP do AlloyDB Omni ou mencionar um bloco CIDR de cobertura.

4. (Opcional) Se o provedor que não é o AlloyDB for o Amazon RDS ou o Aurora, a extensão pglogical precisa ser ativada e os parâmetros necessários precisam ser ajustados com ajustes cluster parameter group.

   1. Em um grupo de parâmetros de cluster novo ou existente, defina os seguintes parâmetros:

      • rds.logical_replication a 1
      • De max_replication_slots para 50
      • De max_wal_senders para 50
      • De max_worker_processes para 64
      • shared_preload_libraries a pg_stat_statements, pglogical

   2. Reinicie o cluster do Amazon RDS ou Aurora para que os ajustes do grupo de parâmetros do cluster entrem em vigor.

5. Confirme se todos os valores dos parâmetros são relevantes:

   SELECT name, setting
   FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
    ORDER BY name;
   

Ajustes de autenticação baseada em host no cluster de provedor que não é do AlloyDB Omni

O pglogical faz conexões TCP locais com o banco de dados do provedor. Portanto, é necessário adicionar o endereço IP do servidor host ao arquivo DATA_DIR/pg_hba.conf do AlloyDB Omni, em que DATA_DIR é o caminho do sistema de arquivos para o diretório de dados, por exemplo, /home/$USER/alloydb-data.

1. Adicione uma entrada de autenticação de confiança para o servidor local, específica para um novo usuário pglogical_replication, ao arquivo DATA_DIR/pg_hba.conf.

   Importante: se você preferir usar a autenticação de senha, remova os valores trust e substitua-os por scram-sha-256.

   Além disso, os nós de assinante precisam ser capazes de fazer a autenticação nos nós do provedor. Adicione o endereço IP de cada nó de assinante ou o intervalo de IP do bloco CIDR adequado ao arquivo DATA_DIR/pg_hba.conf:

   echo -e "# pglogical entries:
   host all pglogical_replication samehost trust
   host all pglogical_replication SERVER_IP_ADDRESS/32 trust
   " | column -t | sudo tee -a DATA_DIR/pg_hba.conf

   Substitua SERVER_IP_ADDRESS pelo endereço IP da instância principal do AlloyDB Omni para replicar.

   Observação: especifique e limite as conexões aos nomes exatos dos bancos de dados em vez de all. Além disso, restrinja os intervalos de IP do bloco CIRD especificando os endereços IP do nó do assinante, como /32 em vez de intervalos de IP.

2. Verifique se as entradas estão corretas:

   tail -3 DATA_DIR/pg_hba.conf

3. Reinicie o cluster que não é do AlloyDB para que as mudanças de parâmetro entrem em vigor.

Observação: os bancos de dados PostgreSQL de serviços gerenciados pela nuvem, como o Amazon RDS ou o Aurora, geralmente não permitem ajustes manuais ou personalização no arquivo DATA_DIR/pg_hba.conf. Em alguns casos, não são necessários ajustes ou ajustes de firewall e roteamento.

Ajustar os parâmetros para o cluster de assinantes do AlloyDB Omni

O pglogical também exige um conjunto mínimo de ajustes de parâmetro no cluster de assinantes do AlloyDB Omni. Anexe pglogical ao parâmetro shared_preload_libraries no arquivo DATA_DIR/postgresql.conf. Se algum banco de dados no cluster funcionar como um banco de dados do provedor, faça as mudanças de parâmetro necessárias para bancos de dados do provedor.

1. Ajuste os parâmetros:

   sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" DATA_DIR/postgresql.conf

2. Verifique se o parâmetro está definido corretamente:

   grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf

3. Reinicie o AlloyDB Omni para que a mudança de parâmetro entre em vigor:

   Docker

    docker container restart CONTAINER_NAME

   Substitua CONTAINER_NAME pelo nome que você atribuiu ao contêiner AlloyDB Omni quando o iniciou.

   Podman

    podman container restart CONTAINER_NAME

   Substitua CONTAINER_NAME pelo nome que você atribuiu ao contêiner AlloyDB Omni quando o iniciou.

4. Defina os valores padrão do AlloyDB Omni para outros parâmetros de banco de dados do provedor:

   • max_worker_processes: um por banco de dados do provedor e um por nó de assinante.
   • track_commit_timestamp: defina como on se a resolução de conflito de última/primeira atualização for necessária.

5. Confirme se todos os valores dos parâmetros são relevantes:

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -c "
   SELECT name, setting
     FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
        ORDER BY name;
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -c "
   SELECT name, setting
     FROM pg_catalog.pg_settings
    WHERE name IN ('listen_addresses',
                   'wal_level',
                   'shared_preload_libraries',
                   'max_worker_processes',
                   'max_replication_slots',
                   'max_wal_senders',
                   'track_commit_timestamp')
        ORDER BY name;
   "

Ajustes de autenticação baseada em host no cluster de assinantes do AlloyDB Omni

O pglogical faz conexões TCP locais com o banco de dados de assinantes do AlloyDB Omni. Portanto, adicione o endereço IP do servidor host do assinante ao arquivo DATA_DIR/pg_hba.conf do AlloyDB Omni.

1. Adicione uma entrada de autenticação de confiança para o servidor local, específica para um novo usuário pglogical_replication, ao arquivo DATA_DIR/pg_hba.conf:

   echo -e "# pglogical entries:
   host all pglogical_replication samehost trust
   " | column -t | sudo tee -a DATA_DIR/pg_hba.conf

   Observação: especifique e limite as conexões aos nomes exatos dos bancos de dados, em vez de all.

2. Verifique se a entrada está correta:

   tail -2 DATA_DIR/pg_hba.conf

3. Reinicie o AlloyDB Omni para que a mudança de autenticação entre em vigor:

   docker container restart CONTAINER_NAME

Crie um usuário pglogical nos clusters de provedor e de assinante

Você precisa criar um novo usuário no cluster do provedor e do assinante. pglogical exige que o usuário tenha os atributos superuser e replication.

1. No cluster do provedor do AlloyDB no Google Cloud , crie a função do usuário:

   CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
   ALTER USER pglogical_replication WITH replication;
   ALTER USER pglogical_replication WITH superuser;
   

2. (Opcional) Se o provedor que não é do AlloyDB for o Amazon RDS ou o Aurora, conceda o seguinte papel:

   GRANT rds_superuser TO replication_user;
   

   Observação: se o banco de dados que não é do AlloyDB for de outro serviço gerenciado pela nuvem, talvez seja necessário conceder acesso personalizado específico para o serviço.

Adicionar pglogical e nós ao banco de dados do provedor que não é o AlloyDB

1. Conceda os privilégios necessários.

   É necessário instalar a extensão pglogical em cada banco de dados e conceder a permissão usage ao usuário do banco de dados pglogical.

   Por exemplo, se o banco de dados for my_test_db, execute o seguinte comando:

   CREATE EXTENSION IF NOT EXISTS pglogical;
   GRANT usage ON SCHEMA pglogical TO pglogical_replication;
   

2. Crie um nó pglogical para os bancos de dados do provedor. O node_name é arbitrário, e a string dsn precisa ser uma conexão TCP válida de volta ao mesmo banco de dados.

   Importante: se você estiver usando um banco de dados de serviço gerenciado pela nuvem, a autenticação de senha será obrigatória. Você pode usar a autenticação por senha na implantação autogerenciada do PostgreSQL. Nesses casos, inclua a senha na string dsn. Se você estiver usando uma instalação autogerenciada, poderá usar a senha ou a autenticação de confiança.

   Por exemplo, para o banco de dados my_test_db, execute o seguinte comando:

   SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   

   Observação: se o provedor que não é o AlloyDB for o Amazon RDS ou o Aurora, use o endpoint da AWS fornecido em vez do endereço IP do host no parâmetro dsn. Se o banco de dados que não é do AlloyDB for de algum outro serviço gerenciado pela nuvem, será necessário um endpoint ou endereço IP específico do serviço.

Criar uma tabela e adicioná-la ao conjunto de replicação padrão

Crie uma tabela e a adicione ao conjunto de replicação padrão no banco de dados do provedor que não é o AlloyDB.

1. Crie uma tabela de teste chamada test_table_1 no banco de dados do provedor:

   CREATE TABLE test_table_1 (col1 INT PRIMARY KEY);
   INSERT INTO test_table_1 VALUES (1),(2),(3);
   

2. Adicione manualmente a tabela de teste ao conjunto de replicação padrão. É possível criar conjuntos de replicação pglogical personalizados ou usar os conjuntos de replicação padrão. Vários conjuntos de replicação padrão, como default, default_insert_only e ddl_sql, foram criados quando você criou a extensão. É possível adicionar tabelas e sequências aos conjuntos de replicação individualmente ou todos de uma vez para um esquema especificado.

   -- Add the specified table to the default replication set:
   SELECT pglogical.replication_set_add_table(set_name := 'default', relation := 'test_table_1', synchronize_data := TRUE);
   
   -- Check which tables have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_table;
   

3. (Opcional) Adicione todas as tabelas em um esquema especificado, como public:

   -- Add all "public" schema tables to the default replication set:
   SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
   
   -- Check which tables have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_table;
   
   -- Add all "public" schema sequences to the default replication:
   SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);
   
   -- Check which sequences have been added to all replication sets:
   SELECT * FROM pglogical.replication_set_seq;
   

4. Remova a tabela do conjunto de replicação default. Se houver tabelas no esquema que não tiverem uma chave primária ou uma identidade de réplica, somente as instruções INSERT poderão ser replicadas. Se você adicionou essas tabelas ao conjunto de replicação default automaticamente pela função replication_set_add_all_tables, remova-as manualmente desse conjunto de replicação e adicione-as ao conjunto default_insert_only.

   -- Remove the table from the **default** replication set:
   SELECT pglogical.replication_set_remove_table(set_name := 'default', relation := 'test_table_2');
   
   -- Manually add to the **default_insert_only** replication set:
   SELECT pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := 'test_table_2');
   

   Se quiser adicionar as tabelas recém-criadas ao conjunto de replicação de forma automática, adicione o gatilho pglogical_assign_repset conforme sugerido na origem pglogical.

Copiar o banco de dados para o cluster de assinantes do AlloyDB Omni

1. Crie um backup somente de esquema do banco de dados de origem usando o utilitário pg_dump.

2. Execute o comando pg_dump do seu servidor de assinante do AlloyDB Omni usando o endereço IP ou o endpoint do seu servidor que não é do AlloyDB.

   Observação: é necessário executar o comando pg_dump fora do contêiner AlloyDB Omni. É recomendável executar o comando pg_dump no servidor host do AlloyDB Omni se você puder instalar o cliente PostgreSQL. Também é possível executar o comando em outro servidor, se necessário. Para mais informações sobre como exportar usando pg_dump, consulte Exportar um arquivo DMP.

   pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sql

   Observação: se o provedor que não é o AlloyDB for o Amazon RDS ou o Aurora, use o endpoint da AWS fornecido em vez do endereço IP do host no parâmetro dsn. Se o banco de dados que não é do AlloyDB for de algum outro serviço gerenciado pela nuvem, um endereço IP ou endpoint específico do serviço será necessário.

3. Importe o backup para o banco de dados do assinante no servidor AlloyDB Omni:

   Docker

   docker exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

   Podman

   podman exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

Isso cria o banco de dados e o esquema, sem nenhum dado de linha. Os dados de linha são replicados pela extensão pglogical. Copie ou recrie manualmente outros usuários ou funções necessários.

Criar um nó e uma assinatura no banco de dados de assinantes do AlloyDB Omni

1. Crie um nó no banco de dados de assinantes do AlloyDB Omni. Adicione a senha ao dsn se você optar por usar a autenticação de senha.

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');
   "

2. Crie uma assinatura no banco de dados do assinante, apontando de volta para o banco de dados do provedor no servidor do provedor do AlloyDB Omni.

   Observação: para bancos de dados PostgreSQL de serviço gerenciado pela nuvem, a autenticação de confiança não está disponível. É necessário incluir o argumento de senha no parâmetro dsn.
   Cuidado: verifique se os detalhes de conexão no parâmetro dsn estão corretos.

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');
   "

   Observação: se o provedor que não é o AlloyDB for o Amazon RDS ou o Aurora, use o endpoint da AWS fornecido em vez do endereço IP do host no parâmetro dsn. Se o banco de dados que não é do AlloyDB for de algum outro serviço gerenciado pela nuvem, um endereço IP ou endpoint específico do serviço será necessário.

3. Em alguns segundos ou minutos, os dados iniciais precisam ser replicados do provedor para o assinante:

   Docker

   docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT * FROM test_table_1 ORDER BY 1;
   "

   Podman

   podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
   SELECT * FROM test_table_1 ORDER BY 1;
   "

   Outras linhas adicionadas ao banco de dados do provedor também são replicadas em tempo real em segundos.

Outras considerações sobre a implantação do pglogical

A extensão pglogical tem muitos recursos avançados que não são abordados neste documento. Muitos desses recursos são aplicáveis à sua implementação. Você pode considerar os seguintes recursos avançados:

• Resolução de conflitos
• Replicação bidirecional e multimestre
• Inclusão de sequências
• Procedimentos de alternância e failover

A seguir

• Replicar dados entre Google Cloud AlloyDB e AlloyDB Omni
• Troca e failover com replicação pglogical