Replicação lógica no PostgreSQL com pglogical

Uma breve introdução

No PostgreSQL, a replicação é um processo que envolve a cópia de um banco de dados de origem para uma réplica. Essa prática é fundamental para garantir alta disponibilidade, tolerância a falhas e escalabilidade dos sistemas de banco de dados.

O PostgreSQL oferece basicamente dois tipos de replicação:

• Replicação lógica – captura as alterações em nível de transação ou linha, analisando o log de transações WAL do banco de dados.
• Replicação física – copia blocos de dados diretamente do disco, sem considerar a semântica das operações.

Neste artigo, abordaremos a replicação lógica, um método que permite criar cópias consistentes e atualizadas de um banco de dados, tabela ou conjunto de tabelas em outra instância.

Por que usar Replicação Lógica?

• Alta disponibilidade: Criar réplicas para garantir que os dados estejam disponíveis mesmo em caso de falhas no servidor principal.
• Leitura de Dados: Descarregar a carga de leitura do servidor principal, distribuindo-a entre várias réplicas.
• Relatórios e Análises: Utilizar réplicas para realizar consultas complexas e demoradas sem impactar o desempenho do banco de dados principal.
• Testes e Desenvolvimento: Criar ambientes de teste isolados para desenvolver e testar aplicações sem afetar os dados de produção.
• Georeplicação: Distribuir dados geograficamente para reduzir a latência e melhorar o desempenho de consultas para usuários em diferentes regiões.

O PostgreSQL, como sistema de gerenciamento de banco de dados relacional (SGBD) de código aberto, oferece inúmeras soluções, em grande parte graças às contribuições da comunidade e de empresas especializadas. Essas soluções, baseadas no PostgreSQL, atendem a diversos cenários e disponibilizam uma ampla gama de opções. Elas abrangem aspectos como alta disponibilidade, recuperação de desastres com mínimo RTO e RPO, auditoria, segurança e backup, entre outros.

Por que usar pglogical?

O pglogical é uma extensão que oferece ampla gama de funcionalidades, permitindo a replicação de streaming lógico em diversos níveis: banco de dados, grupo de tabelas, tabelas individuais, linhas e até colunas podendo ser utilizado para alta disponibilidade, réplicas somente leitura e até para migrações e upgrades de versão.

Para mais informações sobre os recursos adicionais do pglogical, consulte https://github.com/2ndQuadrant/pglogical.

Limitações

O pglogical possui algumas limitações como:

• Tabelas do tipo UNLOGGED e TEMPORARY não podem ser replicadas;
• Não é possível replicar múltiplos bancos de dados de uma só vez. Nesse caso, deve-se ter um conjunto de configuração para cada base de dados;
• O pglogical não replica operações de DDL automaticamente. Neste caso, existem funções específicas para replicar DDL;
• Sequences não são replicadas em tempo real (em cenários de migração, isso é um ponto importante de atenção);
• O pglogical não suporta replicação entre bancos de dados com codificação diferente. Recomendamos usar codificação UTF-8 em todos os bancos de dados replicados.

Requisitos

Antes de instalar o pglogical, é necessário atender a alguns requisitos:

• A versão do PostgreSQL precisar ser igual ou superior a versão 9.5;
• A extensão precisa ser instalada em ambos os nós (publicador e assinante);
• As tabelas precisam ter o mesmo nome em ambos os nós (publicador e assinante);
• Todas as tabelas precisam ter PRIMARY KEY;
• Atualmente, a replicação e administração do pglogical requerem privilégios de superusuário que podem ser estendidos posteriormente para privilégios mais granulares. Sendo assim criaremos um usuario com privilégios de superusuario em ambas as instâncias de bancos de dados (pg1 e pg2).

postgres=# create user user_replication superuser password 'm1p455w0rd';
CREATE ROLE

• Altere os seguintes parâmetros abaixo no pg1 (192.168.56.39) localizados no arquivo postgresql.conf

    listen_addresses = '*'
    wal_level = 'logical'
    max_worker_processes = 10   # um por banco de dados necessário alterar no nó do provedor
    max_replication_slots = 10  # um por banco de dados necessário alterar no nó do provedor
    max_wal_senders = 10        # um por banco de dados necessário alterar no nó do provedor
    shared_preload_libraries = 'pglogical'
    
    #Obs: após alteração dos parâmetros será necessário reiniciar a instância de banco de dados.

• Ajuste as configurações de acesso na camada de segurança do pg_hba.conf para permitir a comunicação entre os servidores pg1 e pg2:

# No pg1 (192.168.56.39)

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.56.40/32        scram-sha-256

# No pg2 (192.168.56.40)
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.56.39/32           scram-sha-256

Recursos utilizados

Para esse laboratório foram utilizados 2 servidores CentOS Stream release 9, de maneira que o pg1 (192.168.56.39) é o nó publicador e o pg2 (192.168.56.40) é o nó assinante. Para versão utilizada do PostgreSQL foi a 15.9 assim como detalhado abaixo:

Servidores:

• pg1 192.168.56.39
• pg2 192.168.56.40

PostgreSQL:

• PostgreSQL 15.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-2), 64-bit

Instalação do pglogical

Como estamos usando a família Red Hat (CentOS 9), a instalação do pglogical neste tutorial será feita utilizando o repositório YUM.

1) Vamos instalar de acordo com a versão atual do PostgreSQL (15.9) que estamos utilizando e procurar versões compatíveis:

[root@pg1 ~]# [root@pg1 ~]# yum whatprovides pglogical_15
Last metadata expiration check: 1:31:36 ago on Sat 16 Nov 2024 07:51:37 PM -03.
pglogical_15-2.4.2-1.rhel9.x86_64 : Logical Replication extension for PostgreSQL
Repo        : pgdg15
Matched from:
Provide    : pglogical_15 = 2.4.2-1.rhel9

pglogical_15-2.4.3-1.rhel9.x86_64 : Logical Replication extension for PostgreSQL
Repo        : pgdg15
Matched from:
Provide    : pglogical_15 = 2.4.3-1.rhel9^C

2) Encontramos 2 versões disponíveis como mostrado acima, então vamos instalar a versão mais recente:

[root@pg1 ~]# yum install pglogical_15-2.4.3-1.rhel9.x86_64
Last metadata expiration check: 2:36:46 ago on Sat 16 Nov 2024 09:48:05 PM -03.
Dependencies resolved.
====================================================================================================================================================================================================
 Package                                           Architecture                                Version                                            Repository                                   Size
====================================================================================================================================================================================================
Installing:
 pglogical_15                                      x86_64                                      2.4.3-1.rhel9                                      pgdg15                                      151 k

Transaction Summary
====================================================================================================================================================================================================
Install  1 Package

Total download size: 151 k
Installed size: 556 k
Is this ok [y/N]: y
Downloading Packages:
pglogical_15-2.4.3-1.rhel9.x86_64.rpm                                                                                                                               102 kB/s | 151 kB     00:01
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                               101 kB/s | 151 kB     00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                            1/1
  Installing       : pglogical_15-2.4.3-1.rhel9.x86_64                                                                                                                                          1/1
  Running scriptlet: pglogical_15-2.4.3-1.rhel9.x86_64                                                                                                                                          1/1
  Verifying        : pglogical_15-2.4.3-1.rhel9.x86_64                                                                                                                                          1/1

Installed:
  pglogical_15-2.4.3-1.rhel9.x86_64

Complete!

OBS: repita os passos 1 e 2 tambem e defina as mesmas configurações de parâmetros no arquivo postgresql.conf do nó que será o assinante (pg2 – 192.168.56.40)

Criei um banco de dados teste (db_teste) com 3 tabelas (tb_01, tb_02 e tb_03 ) no pg1 (192.168.56.39) para utilizarmos como modelo nos testes. As tabelas tem as seguintes quantidades de linhas:

db_teste=# select count(*) from tb_01;
 count
--------
 100000
(1 row)

db_teste=# select count(*) from tb_02;
\\ count
--------
 500000
(1 row)

db_teste=# select count(*) from tb_03;
  count
---------
 1000000
(1 row)

Configuração

3) Agora que temos o pglogical instalado e uma base de dados de exemplo populada podemos criar a extensão no contexto do banco de dados que queremos replicar (nesse caso na base db_teste):

db_teste=# CREATE EXTENSION pglogical;
CREATE EXTENSION

4) O próximo passo é fazer um dump dos usuários a nível global e outro apenas da estrutura da base de dados no pg1 (192.168.56.39)

#dump dos usuários criptografando as credenciais para manter as mesmas e ao mesmo tempo exportá-las de dorma segura

[postgres@pg1 ~]$ export PGPASSWORD="m1p455w0rd"; pg_dumpall --globals-only --file=all_roles_and_users.sql -U postgres

[postgres@pg1 ~]$ ls | grep *.sql
all_roles_and_users.sql

#dump do apenas da estrutura da base de dad
pg_dump --schema-only -d db_teste > dump_db_teste.sql

5) Agora vamos restaurar os usuários e schema do banco de dados db_teste no pg2 (192.168.56.40) mas antes precisamos criar o banco de dados com o mesmo nome no destino:

postgres=# create database db_teste;
CREATE DATABASE

# Restaurando os usuários no destino (pg2)
[postgres@pg1 ~]$ psql -U user_replication -h 192.168.56.40 -d postgres < all_roles_and_users.sql
Password for user user_replication:
SET
SET
SET
ALTER ROLE
CREATE ROLE
ALTER ROLE
ALTER ROLE

# Restore da base db_teste no destino (pg2)
[postgres@pg1 ~]$ psql -U user_replication -h 192.168.56.40 -d db_teste -f dump_db_teste.sql
Password for user user_replication:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

6) Agora que restauramos os usuários e estrutura da base db_teste vamos criar a extensão no destino (pg2 – 192.168.56.40). Ao criarmos a extensão pglogical poderemos listar as funções e funcionalidades oferecidas:

db_teste=# create extension pglogical;
CREATE EXTENSION
db_teste=# \\dx+
                                 Objects in extension "pglogical"
                                        Object description
--------------------------------------------------------------------------------------------------
 function pglogical.alter_node_add_interface(name,name,text)
 function pglogical.alter_node_drop_interface(name,name)
 function pglogical.alter_replication_set(name,boolean,boolean,boolean,boolean)
 function pglogical.alter_subscription_add_replication_set(name,name)
 function pglogical.alter_subscription_disable(name,boolean)
 function pglogical.alter_subscription_enable(name,boolean)
 function pglogical.alter_subscription_interface(name,name)
 function pglogical.alter_subscription_remove_replication_set(name,name)
 function pglogical.alter_subscription_resynchronize_table(name,regclass,boolean)
 function pglogical.alter_subscription_synchronize(name,boolean)
 function pglogical.create_node(name,text)
 function pglogical.create_replication_set(name,boolean,boolean,boolean,boolean)
 function pglogical.create_subscription(name,text,text[],boolean,boolean,text[],interval,boolean)
 function pglogical.drop_node(name,boolean)
 function pglogical.drop_replication_set(name,boolean)
 function pglogical.drop_subscription(name,boolean)
 function pglogical.pglogical_gen_slot_name(name,name,name)
 function pglogical.pglogical_max_proto_version()
 function pglogical.pglogical_min_proto_version()
 function pglogical.pglogical_node_info()
 function pglogical.pglogical_version()
 function pglogical.pglogical_version_num()
 function pglogical.queue_truncate()
 function pglogical.replicate_ddl_command(text,text[])
 function pglogical.replication_set_add_all_sequences(name,text[],boolean)
 function pglogical.replication_set_add_all_tables(name,text[],boolean)
 function pglogical.replication_set_add_sequence(name,regclass,boolean)
 function pglogical.replication_set_add_table(name,regclass,boolean,text[],text)
 function pglogical.replication_set_remove_sequence(name,regclass)
 function pglogical.replication_set_remove_table(name,regclass)
 function pglogical.show_repset_table_info(regclass,text[])
 function pglogical.show_subscription_status(name)
 function pglogical.show_subscription_table(name,regclass)
 function pglogical.synchronize_sequence(regclass)
 function pglogical.table_data_filtered(anyelement,regclass,text[])
 function pglogical.wait_for_subscription_sync_complete(name)
 function pglogical.wait_for_table_sync_complete(name,regclass)
 function pglogical.wait_slot_confirm_lsn(name,pg_lsn)
 function pglogical.xact_commit_timestamp_origin(xid)
 table pglogical.depend
 table pglogical.local_node
 table pglogical.local_sync_status
 table pglogical.node
 table pglogical.node_interface
 table pglogical.queue
 table pglogical.replication_set
 table pglogical.replication_set_seq
 table pglogical.replication_set_table
 table pglogical.sequence_state
 table pglogical.subscription
 view pglogical.tables
(51 rows)

7) Agora que temos as definições de usuário, schema da base restaurada no pg2 (192.168.56.40) e o pglogical configurado em ambos os nós, iniciaremos o processo de replicação lógica inicialmente criando o node no pg1 (192.168.56.39) e na sequência definir um replica-set para as tabelas e sequences. Como explicado no ínicio deste tutorial, podemos replicar a base de dados inteira ou definir replica-sets granulares de tabelas, linhas e até colunas. Neste caso em específico replicaremos toda a base:

# Criando o node
db_teste=# SELECT pglogical.create_node(
         node_name := 'node_pg1',
         dsn := 'host=192.168.56.39 port=5432 dbname=db_teste user=user_replication password=m1p455w0rd');
 create_node
-------------
  3406144411
(1 row)

# Criando um replica-set default com todas as tabelas
db_teste=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
 replication_set_add_all_tables
--------------------------------
 t
(1 row)

# Criando um replica-set default para todas as sequences
db_teste=# SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);
 replication_set_add_all_sequences
-----------------------------------
 t
(1 row)

8) No node de destino (pg2 – 192.168.56.40) vamos configurar o node e iniciar uma subscription para iniciar o sincronismo e a replicação.

# Criando o node
db_teste=#  SELECT pglogical.create_node(
    node_name := 'node_pg2',
    dsn := 'host=192.168.56.40 port=5432 dbname=db_teste user=user_replication password=m1p455w0rd'
);
 create_node
-------------
  3493492553
(1 row)

# Criando uma subcription e iniciando o processo de sincronismo com o pg1 (192.168.56.39)
db_teste=# SELECT pglogical.create_subscription (subscription_name := 'subscription_for_pg1', replication_sets := array['default'], provider_dsn := 'host=192.168.56.39 port=5432 dbname=db_teste port=5432 password=m1p455w0rd user=user_replication');
 create_subscription
---------------------
          1692787139
(1 row)

9) Após o sincronismo existem apenas dois status esperados que são:

• initializing – Quando a replicação está em fase de sincronização entre origem e destino. Dependendo do tamanho de sua base isso pode demorar um bom tempo.

db_teste=# select * from pglogical.show_subscription_status(subscription_name := 'subscription_for_pg1');
  subscription_name   |    status    | provider_node |                                           provider_dsn                                           |               slot_name               |replication_sets | forward_origins
----------------------+--------------+---------------+--------------------------------------------------------------------------------------------------+---------------------------------------+------------------+-----------------
 subscription_for_pg1 | initializing | node_pg1      | host=192.168.56.39 port=5432 dbname=db_teste port=5432 password=m1p455w0rd user=user_replication | pgl_db_teste_node_pg1_subscrip64e5e1c |{default}        | {all}
(1 row)

• replicating – Esse estado indica que o processo de sincronismo foi finalizado e a replicação está ativa.

db_teste=# select * from pglogical.show_subscription_status(subscription_name := 'subscription_for_pg1');
  subscription_name   |   status    | provider_node |                                           provider_dsn                                           |               slot_name               | replication_sets | forward_origins
----------------------+-------------+---------------+--------------------------------------------------------------------------------------------------+---------------------------------------+------------------+-----------------
 subscription_for_pg1 | replicating | node_pg1      | host=192.168.56.39 port=5432 dbname=db_teste port=5432 password=m1p455w0rd user=user_replication | pgl_db_teste_node_pg1_subscrip64e5e1c | {default}        | {all}
(1 row)

Conclusão

Se você chegou até aqui, parabéns! Uma vez que a configuração do pglogical foi concluída com sucesso você tem uma gama de possibilidades, como:

• Upgrade de versões pois o pglogical é multi version. ex. (pg1 – origem na versão 10 e pg2 – destino na versão 15);
• Migrações de grandes bases com mínimo downtime, inclusive de ambientes on premise para nuvens como AWS e GCP;
• Réplicas read only, com possibilidade de replicação em cascata;

Em postagens futuras quero abordar aspectos de migração com o pglogical que considero de grande importância principalmente para grandes bases de dados em ambientes críticos que necessitam de mínimo downtime.

Espero que te ajude e valeu!