Replique dados entre o AlloyDB Omni e outras bases de dados

Selecione uma versão da documentação:

Esta página fornece passos para replicar dados entre o AlloyDB Omni e outras bases de dados através da extensão pglogical.

Para mais informações, consulte o artigo pglogical no AlloyDB Omni.

Para mais informações sobre vários componentes da extensão pglogical, consulte a pglogical terminologia e os componentes fundamentais.

Métodos de autenticação suportados

Os dois principais métodos de autenticação usados com a extensão pglogical são os métodos de autenticação por palavra-passe e de confiança.

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

Antes de começar

Pode instalar o pglogical como uma extensão numa determinada base de dados.

Antes de implementar a extensão pglogical no AlloyDB Omni, certifique-se de que cumpre os seguintes requisitos do sistema:

  • Acesso ao seu cluster do PostgreSQL que não seja do AlloyDB como superuser.
  • A extensão pglogical está instalada no seu cluster PostgreSQL que não é do AlloyDB. Para instruções de instalação específicas de lançamento e distribuição, consulte o pglogical.
  • Um servidor AlloyDB Omni instalado e configurado. Para ver instruções sobre como instalar o AlloyDB Omni, consulte o artigo Instale o AlloyDB Omni.
  • Os endereços IP do cluster PostgreSQL não AlloyDB e do servidor anfitrião do AlloyDB Omni.
  • Uma rede estabelecida e segura entre o cluster PostgreSQL não AlloyDB e o servidor anfitrião do AlloyDB Omni. É necessária a conetividade TCP na porta padrão do PostgreSQL de 5432.

Ajuste os parâmetros no fornecedor que não é do AlloyDB

  1. Defina o parâmetro wal_level como logical e anexe pglogical ao parâmetro shared_preload_libraries no ficheiro postgresql.conf. A extensão pglogical requer um conjunto mínimo de ajustes de parâmetros no cluster do fornecedor que não seja 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 alterações aos parâmetros sejam aplicadas.

    Outros parâmetros podem já estar definidos com valores suficientes ou podem exigir ajustes consoante a sua distribuição e versão não pertencentes ao AlloyDB.

    Verifique os seguintes parâmetros:

    • max_worker_processes: um por base de dados do fornecedor e, pelo menos, um por nó do subscritor. O valor padrão para este parâmetro é, pelo menos, 10.
    • max_replication_slots: um por nó em nós do fornecedor.
    • max_wal_senders: um por nó em nós do fornecedor.
    • track_commit_timestamp: definido como on se for necessária a resolução de conflitos da última ou primeira atualização.
    • listen_addresses: tem de incluir o endereço IP do AlloyDB Omni ou mencionar através de um bloco CIDR de cobertura.

  4. (Opcional) Se o seu fornecedor não AlloyDB for o Amazon RDS ou o Aurora, a extensão pglogical tem de estar ativada e os parâmetros necessários ajustados através de ajustes cluster parameter group.

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

      • rds.logical_replication a 1
      • max_replication_slots a 50
      • max_wal_senders a 50
      • max_worker_processes a 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 que 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 no anfitrião ao cluster do fornecedor Omni não AlloyDB

O pglogical estabelece ligações TCP locais à base de dados do fornecedor. Por isso, tem de adicionar o endereço IP do servidor anfitrião ao ficheiro pg_hba.conf do AlloyDB Omni.

  1. Adicione uma entrada de autenticação fidedigna para o servidor local, específica de um novo utilizador pglogical_replication, ao ficheiro pg_hba.conf.

    Além disso, os nós subscritores têm de conseguir autenticar-se nos nós fornecedores. Adicione o endereço IP de cada nó de subscritor ou o intervalo de IPs do bloco CIDR adequado ao ficheiro 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 pg_hba.conf

  2. Verifique se as entradas estão corretas:

    tail -3 pg_hba.conf

  3. Reinicie o cluster não AlloyDB para que as alterações aos parâmetros sejam aplicadas.

Ajuste os parâmetros do cluster de subscrição do AlloyDB Omni

O pglogical também requer um conjunto mínimo de ajustes de parâmetros no cluster de subscrição do AlloyDB Omni. Tem de anexar pglogical ao parâmetro shared_preload_libraries no ficheiro /var/alloydb/config/postgresql.conf. Se alguma base de dados no cluster funcionar como uma base de dados de fornecedor, faça as alterações de parâmetros necessárias para as bases de dados de fornecedor.

  1. Ajuste os parâmetros:

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

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

    grep -iE 'shared_preload_libraries' /var/alloydb/config/postgresql.conf

  3. Reinicie o AlloyDB Omni para que a alteração do parâmetro entre em vigor:

    sudo alloydb database-server stop
sudo alloydb database-server start

  4. Defina os valores predefinidos do AlloyDB Omni para outros parâmetros da base de dados do fornecedor:

    • max_worker_processes: Um por base de dados do fornecedor e um por nó do subscritor.
    • track_commit_timestamp: Defina como on se for necessária a resolução de conflitos em que a última/primeira atualização ganha.

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

    docker exec pg-service 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 no anfitrião ao cluster de subscrição do AlloyDB Omni

O pglogical faz ligações TCP locais à base de dados do subscritor do AlloyDB Omni. Por isso, tem de adicionar o endereço IP do servidor anfitrião do subscritor ao ficheiro /var/alloydb/config/pg_hba.conf do AlloyDB Omni.

  1. Adicione uma entrada de autenticação fidedigna para o servidor local, específica de um novo utilizador pglogical_replication, ao ficheiro /var/alloydb/config/pg_hba.conf:

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

  2. Verifique se a entrada está correta:

    tail -2 /var/alloydb/config/pg_hba.conf

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

    sudo alloydb database-server stop
sudo alloydb database-server start

Crie um utilizador pglogical nos clusters de fornecedor e subscritor

Tem de criar um novo utilizador no cluster de fornecedor e subscritor. pglogical requer que o utilizador tenha os atributos superuser e replication.

  1. No Google Cloud cluster do fornecedor do AlloyDB, crie a função de utilizador:

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

  2. (Opcional) Se o seu fornecedor não pertencente ao AlloyDB for o Amazon RDS ou o Aurora, tem de conceder a seguinte função:

    GRANT rds_superuser TO replication_user;

Adicione pglogical e nós à base de dados do fornecedor não AlloyDB

  1. Conceda os privilégios necessários.

    Tem de instalar a extensão pglogical em cada base de dados e conceder a autorização usage ao utilizador da base de dados pglogical.

    Por exemplo, se a sua base 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 as bases de dados do fornecedor. O node_name é arbitrário e a string dsn tem de ser uma ligação TCP válida de volta à mesma base de dados.

    Por exemplo, para a base 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');

Crie uma tabela e adicione-a ao conjunto de replicação predefinido

Crie uma tabela e adicione-a ao conjunto de replicação predefinido na base de dados do fornecedor que não é o AlloyDB.

  1. Crie uma tabela de teste denominada test_table_1 na base de dados do fornecedor:

    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 predefinido. Pode criar conjuntos de replicação pglogical personalizados ou usar os conjuntos de replicação predefinidos. Foram criados vários conjuntos de replicação predefinidos, como default, default_insert_only e ddl_sql, quando criou a extensão. Pode adicionar tabelas e sequências aos conjuntos de replicação individualmente ou todas 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 num 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 existirem tabelas no esquema que não tenham uma chave primária ou uma identidade de réplica, só é possível replicar declarações INSERT. Se adicionou essas tabelas ao conjunto de replicação default automaticamente através da função replication_set_add_all_tables, tem de as remover 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');

    Opcionalmente, se quiser adicionar automaticamente as tabelas recém-criadas ao conjunto de replicação, adicione o acionador pglogical_assign_repset, conforme sugerido na origem pglogical.

Copie a base de dados para o cluster de subscrição do AlloyDB Omni

  1. Crie uma cópia de segurança apenas do esquema da base de dados de origem com o utilitário pg_dump.

  2. Execute o comando pg_dumpa partir do servidor subscritor do AlloyDB Omni com o endereço IP ou o ponto final do servidor que não é do AlloyDB.

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

  3. Importe a cópia de segurança para a base de dados de subscritores no servidor AlloyDB Omni do subscritor:

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

Isto cria a base de dados e o esquema, sem nenhum dos dados das linhas. Os dados das linhas são replicados pela extensão pglogical. Copiar ou recriar manualmente quaisquer outros utilizadores ou funções necessários.

Crie um nó e uma subscrição na base de dados de subscritores do AlloyDB Omni

  1. Crie um nó na base de dados de subscrição do AlloyDB Omni. Adicione a palavra-passe ao seu dsn se optar por usar a autenticação por palavra-passe.

    docker exec pg-service 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 subscrição na base de dados do subscritor, apontando novamente para a base de dados do fornecedor no servidor do fornecedor do AlloyDB Omni.

    docker exec pg-service 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. No espaço de alguns segundos ou minutos, os dados iniciais devem ter sido replicados do fornecedor para o subscritor:

    docker exec pg-service psql -h localhost -U postgres -d my_test_db -c "

SELECT * FROM test_table_1 ORDER BY 1;
"

    As linhas adicionais que são adicionadas à base de dados do fornecedor também são replicadas em tempo real no espaço de segundos.

Considerações adicionais sobre a pglogicalimplementação

A extensão pglogical tem muitas funcionalidades avançadas que não são abordadas neste documento. Muitas destas funcionalidades são aplicáveis à sua implementação. Pode considerar as seguintes funcionalidades avançadas:

  • Resolução de conflitos
  • Multimestre e replicação bidirecional
  • Inclusão de sequências
  • Procedimentos de comutação e comutação por falha

O que se segue?