Replique dados entre o AlloyDB e o AlloyDB Omni

Selecione uma versão da documentação:

Esta página oferece uma vista geral do pglogical no AlloyDB Omni, das respetivas vantagens e limitações.

Esta página também fornece passos para replicar dados entre o Google Cloud AlloyDB e o AlloyDB Omni através da extensão pglogical.

Componentes principais do pglogical

Os componentes principais da extensão pglogical incluem o seguinte:

  • Node: referência dada para a base de dados num cluster do PostgreSQL. A extensão pglogical é instalada e funciona em qualquer número de bases de dados no cluster, e cada uma atua como um nó pglogical distinto. Cada nó pode ser um fornecedor, também conhecido como origem de replicação, ou um subscritor, também conhecido como destino de replicação, ou ambos em simultâneo. Só é permitido um nó por base de dados.
  • Conjunto de replicação: definido na base de dados do fornecedor como um agrupamento lógico de tabelas e sequências a migrar, e as declarações SQL, como INSERT, UPDATE, DELETE, TRUNCATE, que têm de ser replicadas. Pode atribuir tabelas a mais do que um conjunto de replicação. Por predefinição, são fornecidos três conjuntos de replicação pré-configurados, como default, default_insert_only e ddl_sql, e pode adicionar qualquer número de conjuntos de replicação adicionais para satisfazer as suas necessidades.
  • Subscrição: fornece detalhes das alterações replicadas das bases de dados do fornecedor e das alterações replicadas das bases de dados do fornecedor na base de dados do subscritor. A subscrição especifica a base de dados do fornecedor através de uma string de ligação e, opcionalmente, que conjuntos de replicação desse fornecedor devem ser copiados. Além disso, também pode especificar se quer usar apply delay quando cria a subscrição.

Nesta implementação, o serviço AlloyDB é o fornecedor e o AlloyDB Omni no local é o subscritor. Google Cloud Tenha em atenção que a configuração oposta também é possível.

Métodos de autenticação suportados

Tem de considerar a rede e a segurança entre os nós de replicação antes de implementar a extensão pglogical no AlloyDB Omni. 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 é a autenticação de confiança porque, no método de autenticação por palavra-passe, as palavras-passe são armazenadas em formato de texto simples nas tabelas da base de dados pertencentes ao pglogical. Estas palavras-passe são visíveis em texto simples para qualquer pessoa com autorizações da base de dados para consultar estas tabelas, em cópias de segurança não binárias e nos ficheiros de registo do PostgreSQL.

Se estiver a usar o método de autenticação de confiança, tem de fazer entradas específicas no ficheiro de autenticação baseada no anfitrião, pg_hba.conf, para máxima segurança. Pode restringir o acesso especificando as bases de dados de destino, permitindo apenas a opção de replicação ou bases de dados específicas, o utilizador de replicação e apenas a partir do endereço IP específico do subscritor.

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:

  • Um cluster do Google Cloud AlloyDB e acesso de leitura/escrita à instância principal como administrador do Cloud AlloyDB. Para obter instruções sobre como aprovisionar um Google Cloud cluster do AlloyDB, consulte Crie e ligue-se a uma base de dados.
  • 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 da instância principal do Google Cloud AlloyDB e do servidor anfitrião do AlloyDB Omni.
  • Uma rede estabelecida e segura entre o Google Cloud 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 do AlloyDB Google Cloud

A extensão pglogical requer um conjunto mínimo de ajustes de parâmetros no cluster do fornecedor do Google Cloud AlloyDB. Tem de definir o parâmetro wal_level como logical e anexar pglogical ao parâmetro shared_preload_libraries no ficheiro postgresql.conf.

   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

No Google Cloud serviço AlloyDB, pode ajustar os parâmetros definindo as flags de cluster adequadas.

Tem de ajustar os parâmetros das seguintes Google Cloud flags do AlloyDB:

  • alloydb.enable_pglogical = on
  • alloydb.logical_decoding = on

Para obter informações sobre como definir flags de base de dados no Google Cloud AlloyDB, consulte o artigo Configure as flags de base de dados de uma instância.

Para os outros parâmetros de base de dados do nó do fornecedor obrigatórios, tem de definir os Google Cloud valores predefinidos do AlloyDB da seguinte forma:

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

Pode verificar estes parâmetros através de qualquer ferramenta de consulta, como o psql.

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

A extensão pglogicalrequer um conjunto mínimo de ajustes de parâmetros no subscritor 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. Usando a CLI do AlloyDB Omni, 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. Usando a CLI do AlloyDB Omni, 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 fornecedores e subscritores

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 o utilizador e conceda a função alloydbsuperuser:

    CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
ALTER USER pglogical_replication WITH replication;
GRANT alloydbsuperuser TO pglogical_replication;

  2. No cluster de subscrição do AlloyDB Omni, crie o utilizador e conceda os atributos replication e superuser:

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

Adicione pglogical e nós à Google Cloud base de dados do fornecedor do 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. No Google Cloud AlloyDB, tem de conceder privilégios no esquema pglogical.

    Por exemplo, se a sua base de dados for my_test_db, execute o seguinte comando na base de dados do fornecedor do Google Cloud AlloyDB:

       \c my_test_db;
 CREATE EXTENSION IF NOT EXISTS pglogical;
 GRANT usage ON SCHEMA pglogical TO pglogical_replication;
-- For Google Cloud AlloyDB we also need to manually grant privileges:
 GRANT ALL PRIVILEGES ON ALL tables IN 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. Para o Google Cloud AlloyDB, a parte do anfitrião do dsn é o endereço IP fornecido para a instância principal.

    Para o Google Cloud AlloyDB, a autenticação de confiança não é permitida e o argumento de palavra-passe tem de ser incluído no dsn. parâmetro.

    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 do Google Cloud 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. Conceda SELECT nas tabelas individuais ou execute o comando GRANT SELECT ON ALL TABLES. Todas as tabelas que fazem parte de um conjunto de replicação têm de ter a autorização de consulta concedida ao utilizador de replicação, pglogical_replication.

    GRANT SELECT ON ALL TABLES IN SCHEMA public TO pglogical_replication;

  3. 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;

  4. (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;

  5. Remova a tabela do conjunto de replicação default. Se existirem tabelas no esquema que não tenham uma chave primária, pode configurá-la para replicação apenas de INSERÇÃO ou definir as colunas que identificam exclusivamente a linha através da funcionalidade REPLICA IDENTITY usada com o comando ALTER TABLE. Se adicionou essas tabelas ao conjunto de replicação defaultautomaticamente 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 da instância principal do Google Cloud 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

Ignore erros como alloydbsuperuser not existing. Esta função é específica do Google Cloud AlloyDB.

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:

    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 instância principal da Google Cloud base de dados do fornecedor do AlloyDB.

    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 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?