Replique dados entre o AlloyDB e o AlloyDB Omni

Selecione uma versão da documentação:

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

Para uma visão geral de pglogical no AlloyDB Omni, os benefícios e as limitações dele, consulte Sobre a extensão pglogical.

Principais componentes do pglogical

Os principais componentes da extensão pglogical são:

  • Nó: referência fornecida para o banco de dados em um cluster do PostgreSQL. A extensão pglogical é instalada e funciona em qualquer número de bancos de dados no cluster, e cada um atua como um nó pglogical distinto. Cada nó pode ser um provedor, também conhecido como origem de replicação, ou um assinante, também conhecido como destino de replicação, ou ambos simultaneamente. Só é permitido um nó por banco de dados.
  • Conjunto de replicação: definido no banco de dados do provedor como um agrupamento lógico de tabelas e sequências a serem migradas, e as instruções SQL, como INSERT, UPDATE, DELETE, TRUNCATE que precisam ser replicadas. É possível atribuir tabelas a mais de um conjunto de replicação. Por padrão, três conjuntos de replicação pré-configurados, como default, default_insert_only, e ddl_sql, são fornecidos, e é possível adicionar qualquer número de conjuntos de replicação adicionais para atender às suas necessidades.
  • Assinatura: fornece detalhes das mudanças replicadas de bancos de dados do provedor e mudanças replicadas de bancos de dados do provedor no banco de dados do assinante. A assinatura especifica o banco de dados do provedor por meio de uma string de conexão e, opcionalmente, quais conjuntos de replicação desse provedor precisam ser copiados. Além disso, também é possível especificar se você quer usar o apply delay ao criar a assinatura.

Nessa implantação, o serviço do AlloyDB para PostgreSQL é o provedor, e o AlloyDB Omni on-premise é o assinante. A configuração oposta também é possível.

Métodos de autenticação compatíveis

Considere 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 de senha e de confiança.

O método de autenticação recomendado é o de confiança, porque, no método de autenticação de senha, as senhas são armazenadas em formato de texto simples em tabelas de banco de dados pertencentes ao pglogical. Essas senhas ficam visíveis em texto simples para qualquer pessoa com permissões de banco de dados para consultar essas tabelas, em backups não binários, e nos arquivos de registro do PostgreSQL.

Se você estiver usando o método de autenticação de confiança, faça entradas específicas no arquivo de autenticação baseado em host, pg_hba.conf, para máxima segurança. É possível restringir o acesso especificando os bancos de dados de destino, permitindo apenas a opção de replicação ou bancos de dados específicos, o usuário de replicação e apenas do endereço IP específico do assinante.

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:

  • Um cluster do AlloyDB para PostgreSQL e acesso de leitura/gravação à instância principal como um administrador do Cloud AlloyDB. Para instruções sobre como provisionar um cluster do AlloyDB para PostgreSQL, consulte Criar e conectar-se a um banco de dados.
  • 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 da instância principal do AlloyDB para PostgreSQL e do servidor host do AlloyDB Omni.
  • Uma rede estabelecida e protegida entre o AlloyDB para PostgreSQL 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 do AlloyDB para PostgreSQL

A extensão pglogical exige um conjunto mínimo de ajustes de parâmetros no cluster do provedor do AlloyDB para PostgreSQL. Defina o parâmetro wal_level como logical e anexe pglogical ao shared_preload_libraries parâmetro no arquivo 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 do AlloyDB para PostgreSQL, é possível ajustar os parâmetros definindo as flags de cluster apropriadas.

É necessário ajustar os parâmetros das seguintes flags do AlloyDB para PostgreSQL:

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

Para informações sobre como definir flags de banco de dados no AlloyDB para PostgreSQL, consulte Configurar flags de banco de dados de uma instância.

Para os outros parâmetros de banco de dados do nó do provedor necessários, defina os valores padrão do AlloyDB para PostgreSQL da seguinte maneira:

  • max_worker_processes: um por banco de dados do provedor e pelo menos um por nó do 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 resolução de conflitos da última ou primeira atualização for necessária.
  • listen_addresses: precisa incluir o endereço IP do AlloyDB Omni ou mencionar um bloco CIDR de cobertura.

É possível verificar esses parâmetros usando qualquer ferramenta de consulta, como psql.

Ajustar parâmetros no cluster do assinante do AlloyDB Omni

A extensão pglogical também exige um conjunto mínimo de ajustes de parâmetros no assinante do AlloyDB Omni. Anexe 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 bancos de dados do provedor.

Substitua DATA_DIR pelo caminho do sistema de arquivos para o 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 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 instalá-lo.

    Podman 

     podman container restart CONTAINER_NAME

    Substitua CONTAINER_NAME pelo nome que você atribuiu ao contêiner do AlloyDB Omni ao instalá-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ó do assinante.
    • track_commit_timestamp: definido como on se a resolução de conflitos da última ou primeira atualização for necessária.

  5. Confirme se todos os valores de parâmetro 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 baseados em host no cluster do assinante do AlloyDB Omni

O pglogical faz conexões TCP locais com o banco de dados do assinante 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 de confiança para o servidor local, específica para um novo pglogical_replication usuário, 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

  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 

    docker container restart CONTAINER_NAME

    Podman 

    podman container restart CONTAINER_NAME

Criar um usuário pglogical em clusters de provedor e assinante

É necessário criar um novo usuário no cluster do provedor e do assinante. O pglogical exige que o usuário tenha as permissões superuser e replication.

  1. No cluster do provedor do AlloyDB para PostgreSQL, crie o usuário e conceda o papel alloydbsuperuser:

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

  2. No cluster do assinante do AlloyDB Omni, crie o usuário 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;

Adicionar pglogical e nós ao banco de dados do provedor do AlloyDB para PostgreSQL

  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. No AlloyDB para PostgreSQL, é necessário conceder privilégios no esquema pglogical.

    Por exemplo, se o banco de dados for my_test_db, execute o seguinte comando no banco de dados do provedor 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 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. Para o AlloyDB para PostgreSQL, a parte do host do 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 senha precisa ser incluído no dsn. parâmetro.

    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 do AlloyDB para PostgreSQL.

  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. 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 precisam ter permissão de consulta concedida ao usuário 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 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;

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

  5. Remova a tabela do conjunto de replicação default. Se houver tabelas no esquema que não tenham uma chave primária, é possível configurá-la para replicação somente de inserção ou definir as colunas que identificam exclusivamente a linha usando o recurso REPLICA IDENTITY usado com o comando ALTER TABLE. Se você adicionou essas tabelas ao conjunto de replicação default automaticamente usando a 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');

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

Copiar o banco de dados para o cluster do assinante 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 servidor do assinante do AlloyDB Omni usando o endereço IP da instância principal do AlloyDB para PostgreSQL.

    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 do assinante no servidor do assinante 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

Ignore erros como alloydbsuperuser not existing. Esse papel é específico do AlloyDB para PostgreSQL.

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 do assinante do AlloyDB Omni

  1. Crie um nó no banco de dados do assinante 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 assinatura no banco de dados do assinante, apontando de volta para a instância principal do banco de dados do provedor do AlloyDB para 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 serem replicados, o tempo de replicação pode variar de segundos a minutos. Depois disso, 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 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. 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