Replique dados entre o AlloyDB for PostgreSQL e o AlloyDB Omni

Selecione uma versão da documentação:

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

Para uma vista geral do pglogical no AlloyDB Omni, das respetivas vantagens e limitações, consulte o artigo Acerca da extensão pglogical.

Componentes principais do pglogical

Seguem-se os principais componentes da extensão pglogical:

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

A extensão pglogical requer um conjunto mínimo de ajustes de parâmetros no cluster do fornecedor do AlloyDB for PostgreSQL. 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 serviço AlloyDB para PostgreSQL, pode ajustar os parâmetros definindo as flags de cluster adequadas.

Tem de ajustar os parâmetros das seguintes flags do AlloyDB para PostgreSQL:

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

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

Para os outros parâmetros da base de dados do nó do fornecedor necessários, tem de definir os valores predefinidos do AlloyDB para PostgreSQL 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 DATA_DIR/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.

Substitua DATA_DIR pelo caminho do sistema de ficheiros para o seu diretório de dados, por exemplo, /home/$USER/alloydb-data.

  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 alteração do parâmetro entre em vigor:

    Docker 

     docker container restart CONTAINER_NAME

    Substitua CONTAINER_NAME pelo nome que atribuiu ao contentor do AlloyDB Omni quando o instalou.

    Podman 

     podman container restart CONTAINER_NAME

    Substitua CONTAINER_NAME pelo nome que atribuiu ao contentor do AlloyDB Omni quando o instalou.

  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: definido como on se for necessária a resolução de conflitos da última ou primeira atualização.

  5. Confirme se todos os valores dos parâmetros estão definidos corretamente:

    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 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 DATA_DIR/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 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

  2. Verifique se a entrada está correta:

    tail -2 DATA_DIR/pg_hba.conf

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

    Docker 

    docker container restart CONTAINER_NAME

    Podman 

    podman container restart CONTAINER_NAME

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 as autorizações superuser e replication.

  1. No cluster do fornecedor do AlloyDB para PostgreSQL, 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 à base de dados do fornecedor do AlloyDB para PostgreSQL

  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 AlloyDB para PostgreSQL, 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 AlloyDB para PostgreSQL:

       \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 AlloyDB for PostgreSQL, a parte do anfitrião de dsn é o endereço IP fornecido para a instância principal.

    Para o AlloyDB para PostgreSQL, 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 AlloyDB para PostgreSQL.

  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_dump a partir do servidor subscritor do AlloyDB Omni usando o endereço IP da instância principal do AlloyDB for PostgreSQL.

    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 

    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

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

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 

    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 subscrição na base de dados de subscritores, apontando novamente para a instância principal da base de dados do fornecedor do AlloyDB for PostgreSQL.

    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. Com base no tamanho da tabela e nos dados a replicar, o tempo de replicação pode variar de segundos a minutos. Após este período, os dados iniciais devem ter sido replicados do fornecedor para o subscritor:

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

    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?