Replicar dados entre o AlloyDB Omni e outros bancos de dados

Selecione uma versão da documentação:

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

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

Métodos de autenticação compatíveis

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

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

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, verifique se você atende aos seguintes requisitos do sistema:

  • Acesso ao cluster PostgreSQL não AlloyDB como um superuser.
  • A extensão pglogical está instalada no cluster PostgreSQL não AlloyDB. Para instruções de instalação específicas da versão e da distribuição, consulte o pglogical.
  • Um servidor do 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 PostgreSQL não AlloyDB e do servidor host do AlloyDB Omni.
  • Uma rede estabelecida e protegida entre o cluster PostgreSQL não AlloyDB e o servidor host do AlloyDB Omni. A conectividade TCP na porta padrão do PostgreSQL de 5432 é necessária.

Ajustar parâmetros no provedor não AlloyDB

  1. Defina o parâbackwal_levelmetro como logical e anexe pglogical ao shared_preload_libraries parâmetro no arquivo postgresql.conf. A extensão pglogical requer um conjunto mínimo de ajustes de parâmetros no cluster do provedor não 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 não AlloyDB para que as mudanças de parâmetro entrem em vigor.

    Outros parâmetros podem já estar definidos com valores suficientes ou podem exigir ajustes dependendo da distribuição e da versão não AlloyDB.

    Verifique os seguintes parâmetros:

    • max_worker_processes: um por banco de dados do provedor e pelo menos um por nó assinante. Pelo menos 10 é o padrão para esse parâmetro.
    • max_replication_slots: um por nó em nós do provedor.
    • max_wal_senders: um por nó em nós do provedor.
    • track_commit_timestamp: definido como on se a última ou primeira atualização ganhar 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 não AlloyDB for o Amazon RDS ou o Aurora, a extensão pglogical precisará ser ativada e os parâmetros necessários ajustados pelas mudanças do cluster parameter group.

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

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

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

  5. Confirme se todos os valores de parâmetro 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 baseados em host no cluster do provedor não 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 confiável para o servidor local, específica para um novo pglogical_replication usuário, ao DATA_DIR/pg_hba.conf arquivo.

    trust

    Além disso, os nós assinantes precisam ser capazes de autenticar nos nós do provedor. Adicione o endereço IP de cada nó assinante ou o bloco CIDR intervalo de IP apropriado 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 primária do AlloyDB Omni da qual será feita a replicação.

  2. Verifique se as entradas estão corretas:

    tail -3 DATA_DIR/pg_hba.conf

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

Ajustar parâmetros no cluster de assinantes do AlloyDB Omni

O pglogical também requer um conjunto mínimo de ajustes de parâmetros no cluster de assinantes do AlloyDB Omni. É necessário anexar pglogical ao parâmetro shared_preload_libraries no arquivo DATA_DIR/postgresql.conf. Se algum banco de dados no cluster atuar como um banco de dados do provedor, faça as mudanças de parâmetro necessárias para os 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 do AlloyDB Omni ao iniciá-lo.

    Podman 

     podman container restart CONTAINER_NAME

    Substitua CONTAINER_NAME pelo nome que você atribuiu ao contêiner do AlloyDB Omni ao iniciá-lo.

  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ó assinante.
    • track_commit_timestamp: definido como on se a última/primeira atualização ganhar a resolução de conflitos.

  5. Confirme se todos os valores de parâmetro 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 baseados 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, é necessário adicionar o endereço IP do servidor host do assinante a o arquivo DATA_DIR/pg_hba.conf do AlloyDB Omni.

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

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

  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

Criar um usuário pglogical nos clusters do provedor e do assinante

É necessário criar um novo usuário nos clusters do provedor e do assinante. O pglogical exige que o usuário tenha os atributos superuser e replication.

  1. No cluster do provedor do AlloyDB para PostgreSQL, crie o 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 não AlloyDB for o Amazon RDS ou o Aurora, será necessário conceder o seguinte papel:

    GRANT rds_superuser TO replication_user;

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

  1. Conceda as permissões necessárias.

    É 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 para o mesmo banco de dados.

    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');

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

Crie uma tabela e adicione-a ao conjunto de replicação padrão no banco de dados do provedor nã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 tudo 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 tenham uma chave primária ou uma identidade de réplica, somente as instruções INSERT poderão ser replicadas. Se você tiver adicionado essas tabelas ao conjunto de replicação default automaticamente pela função replication_set_add_all_tables, será necessário removê-las manualmente desse conjunto de replicação e adicioná-las 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 automaticamente, 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 no servidor de assinantes do AlloyDB Omni usando o endereço IP ou o endpoint do servidor não AlloyDB.

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

  3. Importe o backup para o banco de dados de assinantes no servidor de assinantes do 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 dos dados de linha. Os dados de linha são replicados pela extensão pglogical. Copie ou recrie manualmente outros usuários ou papéis 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ê escolher usar a autenticação por 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 de assinantes, apontando de volta para o banco de dados do provedor no servidor do provedor do AlloyDB Omni.

    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');
"

  3. Em alguns segundos ou minutos, os dados iniciais serão 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.

Considerações adicionais 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. Considere os seguintes recursos avançados:

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

A seguir