Como configurar a replicação PostgreSQL no Debian 11

PostgreSQL é um sistema de gerenciamento de banco de dados gratuito e de código aberto com foco na extensibilidade e conformidade com SQL. PostgreSQL é um RDBMS (sistema de gerenciamento de banco de dados relacional) avançado e de classe empresarial que oferece suporte a consultas SQL (relacional) e JSON (não relacional).

É um sistema de gerenciamento de banco de dados altamente estável e de nível empresarial que fornece diferentes soluções que permitem configurar alta disponibilidade e failover. PostgreSQL é usado como banco de dados primário para aplicativos da web, aplicativos móveis e aplicativos analíticos. Ele tem uma forte reputação por sua confiabilidade, flexibilidade, robustez de recursos e desempenho.

Neste tutorial, irei instalar e configurar a replicação lógica do servidor de banco de dados PostgreSQL via pglogical2 em dois servidores Debian 11. Você instalará o servidor PostgreSQL com o pacote de extensão pglogical e, em seguida, configurará a replicação entre dois ou mais servidores PostgreSQL.

pglogical é uma extensão do PostgreSQL que permite configurar a replicação lógica de streaming no nível do banco de dados. Ele está usando o modelo de publicação/assinatura para replicação. Com a extensão pglogical, em vez disso, ao replicar os dados disponíveis no PostgreSQL, você replicará e sincronizará tabelas específicas em seu banco de dados disponível para outros servidores PostgreSQL.

Pré-requisitos

Para concluir este guia, você deve ter os seguintes requisitos:

Dois ou mais servidores Debian 11.
Um usuário não root com privilégios de administrador sudo/root.

Este exemplo usa dois servidores Debian com os seguintes detalhes:

Hostname    IP Address      Used as
------------------------------------------
pgdg01      192.168.5.25    Publisher Node
pgdg02      192.168.5.26    Subscriber Node

Quando os servidores estiverem prontos, você poderá iniciar a instalação e configuração da Replicação PostgreSQL via pglogical.

Configure /etc/hosts e o repositório PostgreSQL

Este exemplo assumirá que você está usando servidores Debian 11 novos, então a primeira coisa que você fará é configurar o repositório PostgreSQL e instalar o PostgreSQL em todos os seus sistemas Debian.

Você deve executar as etapas a seguir em todos os seus servidores Debian.

Para começar, abra o arquivo de configuração '/etc/hosts' usando o seguinte comando do editor nano.

sudo nano /etc/hosts

Adicione as seguintes linhas ao arquivo e certifique-se de alterar os detalhes dos endereços IP e nomes de host.

192.168.5.25    pgdg01
192.168.5.26    pgdg02

Salve e feche o arquivo '/etc/hosts' quando terminar.

Em seguida, digite o seguinte comando apt para instalar algumas dependências básicas. Insira y quando solicitado e pressione ENTER para continuar.

sudo apt install wget gnupg2 lsb-release curl apt-transport-https ca-certificates

Agora execute o seguinte comando para adicionar a chave GPG PostgreSQL e o repositório para servidores Debian.

curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/pgdg.gpg > /dev/null 2>&1
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/pgdg.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Em seguida, atualize e atualize o índice do seu pacote para aplicar as alterações.

sudo apt update

Quando adicionado, você deverá ver o repositório PostgreSQL na lista.

Com o repositório PostgreSQL adicionado, a seguir você instalará o servidor PostgreSQL e a extensão pglogical.

Instalando PostgreSQL e extensão pglogical

Por padrão, o repositório oficial do PostgreSQL fornece várias versões do PostgreSQL, como PostgreSQL 13, 14 e 15. Para este exemplo, você instalará e configurará a replicação do PostgreSQL com a versão mais recente do PostgreSQL 15.

Digite o seguinte comando 'apt install' para instalar o servidor PostgreSQL. Insira y para confirmar a instalação e pressione ENTER para continuar.

sudo apt install postgresql

Quando a instalação for concluída, insira o seguinte utilitário de comando systemctl para verificar o servidor PostgreSQL e garantir que o serviço esteja habilitado e em execução.

sudo systemctl is-enabled postgresql
sudo systemctl status postgresql

Uma saída 'enabled' confirma que o serviço PostgreSQL está habilitado e será iniciado automaticamente na inicialização do sistema. E a saída 'ativo (em execução)' confirma que o serviço PostgreSQL atual está em execução.

Em seguida, digite o seguinte comando apt para instalar o pacote de extensão pglogical. Se estiver usando uma versão diferente do PostgreSQL, você deve substituir o número do pacote como 'postgresql-version-pglogical'.

sudo apt install postgresql-15-pglogical

Insira y quando solicitado e pressione ENTER para continuar.

Com o PostgreSQL instalado e funcionando e a extensão pglogical instalada. Você está pronto para iniciar a configuração da replicação lógica de streaming com pglogical.

Configurando o Firewall UFW

Antes de configurar os servidores PostgreSQL, você deve agora configurar o firewall ufw que protegerá e limitará a conexão com o seu servidor PostgreSQL. Com isso, você terá apenas uma conexão de suas redes confiáveis, que é protegida pela implantação do PostgreSQL.

Para instalar o ufw, digite o seguinte comando apt. Quando solicitado, insira y para confirmar e pressione ENTER para continuar.

sudo apt install ufw

Em seguida, insira o seguinte comando ufw para abrir o serviço OpenSSH. E a saída 'Regra adicionada' confirma que a nova regra foi adicionada ao ufw.

sudo ufw allow OpenSSH

Agora digite o seguinte comando ufw para permitir que suas redes confiáveis acessem o servidor PostgreSQL 5432. Certifique-se de alterar o endereço IP no comando a seguir.

sudo ufw allow from 192.168.5.0/24 to any port 5432

Em seguida, digite o seguinte comando para iniciar e habilitar o firewall ufw. Quando for solicitada a confirmação, insira y e pressione ENTER para continuar.

sudo ufw enable

Uma saída 'Firewall está em execução e habilitadod' confirma que o ufw está em execução e também habilitado, o que significa que o firewall ufw será iniciado automaticamente na inicialização do sistema.

Agora você pode verificar o status e a lista de portas habilitadas no ufw digitando o seguinte comando.

sudo ufw status

Você deverá ver o firewall ufw 'Status: Active' com o serviço OpenSSH e a porta do servidor PostgreSQL '5432' adicionada e disponível no firewall ufw.

Configurando o PostgreSQL para ativar a replicação

WAL ou Write-Ahead Logging é um método que garante a integridade dos dados no servidor PostgreSQL. Quaisquer alterações em tabelas e índices devem ser gravadas somente após essas alterações terem sido registradas.

No PostgreSQL, existem três métodos WAL que você pode usar para habilitar a replicação.

A configuração padrão do WAL é 'réplica', que permite configurar o arquivamento e a replicação, além de executar consultas somente leitura no servidor em espera.
Mínimo que permite remover todos os registros, exceto algumas informações importantes que serão usadas para recuperação de uma falha ou desligamento imediato.
O método lógico permite adicionar algumas informações ao WAL que suportam decodificação lógica.

Nesta seção, você configurará o PostgreSQL para ser executado no endereço IP da rede local, configurará o nível wal 'lógico' por meio da extensão 'pglogical' e, em seguida, ativará a replicação usuários por meio do arquivo pg_hba.conf.

Para começar, faça login no shell do PostgreSQL digitando o seguinte comando.

sudo -u postgres psql

Em seguida, verifique a configuração padrão 'wal_level' no servidor PostgreSQL.

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

Uma saída abaixo confirma que o nível wal padrão no servidor PostgreSQL é 'réplica', que é padrão para as versões atuais do PostgreSQL.

Agora digite '\q' para sair do shell do PostgreSQL.

Em seguida, abra o arquivo de configuração padrão do PostgreSQL '/etc/postgresql/15/main/postgresql.conf' usando o seguinte comando do editor nano.

sudo nano /etc/postgresql/15/main/postgresql.conf

Remova o comentário do parâmetro 'listen_addresses' e adicione o endereço IP do seu servidor interno a ele.

Abaixo está a configuração que será utilizada no servidor 'pgdg01'.

listen_addresses = 'localhost, 192.168.5.25'

E abaixo está a configuração que será utilizada no servidor 'pgdg02'.

listen_addresses = 'localhost, 192.168.5.26'

Agora adicione os seguintes parâmetros para ativar o nível wal 'lógico' através da extensão 'pglogical'. Ambos os servidores têm uma configuração semelhante para as configurações de nível wal. O parâmetro 'shared_preload_libraries='pglogical'' ativará o nível wal lógico por meio da extensão 'pglogical'.

max_worker_processes = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = 'on'
wal_level = 'logical'

Salve e feche o arquivo '/etc/postgresql/15/main/postgresql.conf' quando terminar.

Em seguida, abra o arquivo de autenticação PostgreSQL '/etc/postgresql/15/main/pg_hba.conf' usando o seguinte comando do editor nano.

sudo nano /etc/postgresql/15/main/pg_hba.conf

Adicione as seguintes linhas ao final da linha. Isso permitirá a autenticação para qualquer usuário do host '192.168.5.1/24' tanto para usuários normais do PostgreSQL quanto para usuários de replicação.

# IPv4 local connections:
host    all             all             192.168.5.1/24          scram-sha-256
# replication privilege.
host    replication     all             192.168.5.1/24          scram-sha-256

Salve e feche o arquivo '/etc/postgresql/15/main/pg_hba.conf' quando terminar.

Quando a configuração de '/etc/postgresql/15/main/postgresql.conf' e '/etc/postgresql/15/main/pg_hba.conf' for concluída, insira o seguinte utilitário de comando systemctl para reiniciar o serviço PostgreSQL e aplicar as alterações.

sudo systemctl restart postgresql

Agora digite o seguinte comando ss para garantir que o PostgreSQL esteja sendo executado em seu endereço IP local.

ss -tulpn | grep 5432

Você deverá receber uma saída como esta - No servidor pgdg01, o servidor PostgreSQL deve estar rodando em '192.168.5.25:5432'. No servidor pgdg02, o servidor PostgreSQL deve estar rodando em '192.168.5.26:5432'.

Por último, digite o seguinte comando para fazer login no shell do PostgreSQL e verificar o nível wal habilitado em seu servidor PostgreSQL.

sudo -u postgres psql

Insira a seguinte consulta PostgreSQL para verificar o nível wal habilitado em seu servidor PostgreSQL.

select name,setting,unit from pg_settings where name in ('wal_level','max_worker_processes','max_replication_slots','max_wal_senders','shared_preload_libraries','track_commit_timestamp');

Uma saída 'wal_level - lógico' confirma que o nível wal 'lógico' está ativado, que é via 'shared_preload_library - pglogical'.

Neste ponto, você configurou servidores PostgreSQL com nível wal 'lógico' para replicação lógica de streaming via 'pglógico'. Além disso, ambos os servidores PostgreSQL estão sendo executados em endereços IP de rede interna. Na próxima etapa, você criará um novo usuário e banco de dados PostgreSQL e, em seguida, ativará a extensão 'pglogical'.

Configurar usuário e banco de dados

pglogical é uma extensão do PostgreSQL que funciona no nível do banco de dados. Com isso, você não está replicando todo o sistema PostgreSQL, mas sim replicando tabelas ou bancos de dados específicos que estão disponíveis no PostgreSQL.

Para configurar o pglogical, você deve ter o PostgreSQL com privilégio/função SUPERUSER. Além disso, você deve ativar a extensão 'pglogical' no banco de dados de destino que deseja replicar.

Faça login no shell do PostgreSQL digitando o seguinte comando.

sudo -u postgres psql

Crie um novo usuário e banco de dados usando as seguintes consultas PostgreSQL.

Neste exemplo, você criará um novo usuário 'appuser' com o privilégio 'SUPERUSER'. No momento em que este artigo foi escrito, o pglogical exigia privilégio de usuário SUPER para funcionar. Você também criará um novo banco de dados 'appdb', que é o banco de dados de destino no qual deseja ativar a replicação.

CREATE ROLE appuser WITH LOGIN SUPERUSER ENCRYPTED PASSWORD 'Str0ngP4ssw0rd';
CREATE DATABASE appdb WITH OWNER appuser;

Verifique a lista de usuários e bancos de dados em seu servidor PostgreSQL usando as seguintes consultas. Você deve obter o novo usuário 'appuser' com privilégio SUPERUSER e o novo banco de dados 'appdb'.

\du
\l

Em seguida, conecte-se ao novo banco de dados 'appdb' usando a consulta '\c' ou '\connect'. Quando conectado, o prompt do shell do PostgreSQL se tornará algo como 'appdb=#'

\c appdb

Insira a consulta a seguir para ativar a extensão 'pglogical' no banco de dados 'appdb'.

CREATE EXTENSION pglogical;

Em seguida, verifique a lista de extensões habilitadas no banco de dados 'appdb' atual usando a consulta a seguir. Você deverá ver que a extensão 'pglogical' está habilitada no banco de dados 'appdb'.

\dx

Com o usuário e o banco de dados criados e o pglogical habilitado, você começará a configurar o pglogical para habilitar a replicação do banco de dados entre dois servidores PostgreSQL.

Habilitar replicação de tabela via pglogical

Na seção, você habilitará a replicação lógica na tabela específica que criará no banco de dados 'appdb'

### Configure pgdg01 como nó do editor

Insira as seguintes consultas PostgreSQL no 'nó do editor' - 'pgdg01 servidor' para criar uma nova tabela chamada 'professores'.

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

Insira novos dados na tabela 'professores' inserindo as seguintes consultas.

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES 
(1, 'Alice', 'Wonderland', 'Math'),
(2, 'Bob', 'Rista', 'Physics'),
(3, 'John', 'Bonas', 'English');

Em seguida, recupere seus novos dados para garantir que estejam disponíveis no servidor do nó do editor pgdg01.

select count(*) from teachers;
select * from teachers;

O resultado abaixo confirma que novos dados foram adicionados à tabela 'professores'.

A seguir, para configurar a replicação no banco de dados/tabela PostgreSQL via pglogical, você deve seguir estas etapas:

Crie o nó do provedor no servidor do Publisher pgdg01.
Crie um conjunto de replicação que permita controlar quais tabelas do banco de dados serão replicadas.
Adicione tabelas ao conjunto de réplicas.

Insira o comando a seguir para criar um novo nó de provedor chamado 'provider1'. Certifique-se de alterar os detalhes do PostgreSQL com o servidor pgdg01.

select pglogical.create_node(node_name := 'provider1', dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser');

Em seguida, insira a consulta a seguir para criar um novo conjunto de réplicas chamado 'replication_set'.

select pglogical.create_replication_set('replication_set');

Em seguida, adicione a tabela 'teachers' ao conjunto de réplicas 'replication_set' inserindo a consulta a seguir.

select pglogical.replication_set_add_table(set_name := 'replication_set', relation := 'teachers', synchronize_data := true);

Agora que você criou o nó do provedor com o servidor PostgreSQL pgdg01, criou o conjunto de réplicas 'replication_set' e adicionou a tabela 'teachers' ao 'replication_set '. A seguir, você criará e configurará um nó de assinante no servidor 'pgdg02'.

Configure pgdg02 como nó de assinante

Vá para o servidor 'pgdg02' e digite o seguinte comando do editor nano para criar um novo arquivo de senha PostgreSQL '/var/lib/postgresql/.pgpass'. Este arquivo de senha será usado para o seu

sudo -u postgres nano /var/lib/postgresql/.pgpass

Adicione as seguintes linhas ao arquivo e certifique-se de alterar o nome de usuário e a senha com seus dados.

pgdg01:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg01:5432:replication:appuser:Str0ngP4ssw0rd
pgdg02:5432:appdb:appuser:Str0ngP4ssw0rd
pgdg02:5432:replication:appuser:Str0ngP4ssw0rd

Salve e feche o arquivo quando terminar.

Agora digite o seguinte comando para alterar a permissão do arquivo '/var/lib/postgresql/.pgpass' para somente leitura.

sudo chmod 0600 /var/lib/postgresql/.pgpass

Em seguida, faça login no PostgreSQL usando o seguinte comando.

sudo -u postgres psql

Após fazer login, insira a seguinte consulta para recarregar a configuração do servidor PostgreSQL e saia para garantir que as alterações sejam aplicadas.

select pg_reload_conf();
\q

Agora faça login novamente no shell do PostgreSQL no servidor pgdg02.

sudo -u postgres psql

Conecte-se ao banco de dados 'appdb' que você criou.

\c appdb

Em seguida, insira a seguinte consulta PostgreSQL para criar uma nova tabela 'teachers', que deve corresponder à tabela que você possui no nó do provedor 'pgdg01'.

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR
);

Se você verificar os dados disponíveis, deverá obter uma tabela vazia.

select count(*) from teachers;
select * from teachers;

Com o arquivo de senha do PostgreSQL e a tabela 'professores' criados, você configurará o PostgreSQL no servidor 'pgdg02' como assinante com as seguintes etapas:

Crie um nó Assinante no servidor pgdg02.
Crie a assinatura que iniciará o processo de sincronização e replicação em segundo plano.
Verifique o processo de replicação.

Insira a seguinte consulta para criar um novo nó de assinante com o servidor PostgreSQL pgdg02. Certifique-se de alterar os detalhes do nome do host, nome do banco de dados e usuário.

select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=pgdg02 port=5432 dbname=appdb user=appuser');

Em seguida, crie uma nova assinatura chamada ‘subscription1’. Certifique-se de inserir o conjunto de réplicas denominado 'replication_set' e o provedor é o PostgreSQL no servidor pgdg01. Com isso, a replicação da tabela 'professores' será iniciada e executada em segundo plano.

select pglogical.create_subscription(subscription_name := 'subscription1', replication_sets := array['replication_set'], provider_dsn := 'host=pgdg01 port=5432 dbname=appdb user=appuser' );

Insira a seguinte consulta para verificar o status da assinatura.

select *  FROM pglogical.show_subscription_status();

Você deverá receber uma saída como esta: O status de 'subscription1' está sendo replicado para o provedor 'provider1' e o conjunto de réplicas 'replication_set' .

Agora insira a seguinte consulta para aguardar que a assinatura comece a replicar de forma assíncrona e os dados sejam sincronizados.

SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

Por último, você pode verificar os dados da tabela 'professores' que está disponível no servidor pgdg2 PostgreSQL.

select count(*) from teachers;
select * from teachers;

Se a replicação for bem-sucedida, seus dados deverão estar disponíveis e replicados para o servidor PostgreSQL pgdg02.

Testando replicação

Nesta seção, você verificará a replicação no servidor PostgreSQL inserindo novos dados do nó Publicador pgdg01. Em seguida, você verificará os dados do nó Assinante para garantir que os dados sejam replicados.

Primeiro, conecte-se ao banco de dados 'appdb' no servidor pgdg01 digitando o seguinte comando.

sudo -u postgres psql -U appuser -h pgdg01 -d appdb

Insira as seguintes consultas para inserir novos dados na tabela 'professores'.

INSERT INTO teachers(id, first_name, last_name, subject)
VALUES 
(4, 'Ian', 'Gibson', 'Geography'),
(5, 'Tom', 'Riddle', 'Biology'),
(6, 'Jared', 'Dunn', 'Chemistry');

Verifique a tabela 'professores' para garantir que os novos dados sejam adicionados.

select count(*) from teachers;
select * from teachers;

Em seguida, vá para o servidor pgdg02 e digite o seguinte comando para conectar-se ao banco de dados 'appdb'.

sudo -u postgres psql -U appuser -h pgdg02 -d appdb

Insira a seguinte consulta para verificar os dados disponíveis na tabela 'professores'. Se a replicação for bem-sucedida, você deverá ver os dados recém-atualizados disponíveis no servidor pgdg02.

select count(*) from teachers;
select * from teachers;

Com isso, você concluiu a configuração da replicação de fluxo lógico via pglogical nos servidores PostgreSQL.

Conclusão

Neste tutorial, você instalou e configurou a replicação de fluxo lógico via pglogical em servidores Debian 11. Com isso, você também aprendeu o uso básico da extensão pglogical para criar e gerenciar a replicação PostgreSQL no nível do banco de dados.

Além disso, você também concluiu a instalação do PostgreSQL 15 mais recente no servidor Debian por meio do repositório oficial do PostgreSQL e protegeu o servidor PostgreSQL por meio do firewall UFW.

Com isso, agora você pode adicionar outro servidor PostgreSQL e configurar a extensão pglogical para permitir a replicação para vários servidores. Você também pode configurar o balanceamento de carga via HAPROXY ou configurar o failover de servidores PostgreSQL. Para obter informações detalhadas, visite a documentação oficial do PostgreSQL.