Implementando Replicação Lógica no PostgreSQL: Multi-Master e Sincronização Seletiva de Dados

Introdução

A replicação lógica no PostgreSQL permite replicar tabelas, linhas ou até colunas específicas entre bancos de dados, possibilitando padrões sofisticados de distribuição de dados. Ao contrário da replicação física (streaming replication), que copia todo o cluster de banco de dados, a replicação lógica oferece controle granular sobre quais dados são replicados e para onde.

Replicação Lógica vs. Física

Replicação Física (Streaming)

• Replica todo o cluster de banco de dados
• Replicação em nível binário
• Réplicas operam apenas em modo leitura
• Exige a mesma versão do PostgreSQL
• Menor sobrecarga (overhead)

Replicação Lógica

• Replicação seletiva de tabelas/linhas
• Compatível com diferentes versões
• Inscritos (subscribers) permitem escrita
• Maior sobrecarga (overhead)
• Distribuição de dados flexível

Casos de Uso para Replicação Lógica

1. Distribuição Seletiva de Dados: Replicar tabelas específicas para diferentes regiões
2. Configurações Multi-Master: Múltiplos bancos de dados com permissão de escrita e sincronização bidirecional
3. Atualizações entre Versões: Replicar de versões antigas para novas versões do PostgreSQL
4. Agregação de Dados: Consolidar dados de múltiplas fontes
5. Conformidade com a LGPD/GDPR: Replicar apenas colunas não sensíveis

Pré-requisitos e Configuração

Requisitos de Configuração

No Publicador (Source):

# postgresql.conf
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

No Inscrito (Target):

# postgresql.conf
max_replication_slots = 10
max_logical_replication_workers = 10
max_worker_processes = 16

Reiniciar o PostgreSQL

sudo systemctl restart postgresql

Configuração de Rede

Garanta que os bancos de dados possam se comunicar:

# Testar conexão do inscrito para o publicador
psql -h publisher.example.com -U replication_user -d source_db

Configurar pg_hba.conf no publicador:

# Permitir conexões de replicação
host    all    replication_user    subscriber_ip/32    md5

Configuração Básica da Replicação Lógica

Passo 1: Criar Usuário de Replicação

No Publicador:

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

Passo 2: Criar Tabelas de Origem

No Publicador:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username, email) VALUES 
    ('alice', '[email protected]'),
    ('bob', '[email protected]');

Passo 3: Criar Publicação

No Publicador:

-- Publicar todas as tabelas
CREATE PUBLICATION my_publication FOR ALL TABLES;

-- Ou publicar tabelas específicas
CREATE PUBLICATION my_publication FOR TABLE users, orders;

-- Ou com filtros de linha (PostgreSQL 15+)
CREATE PUBLICATION active_users FOR TABLE users WHERE (status = 'active');

Visualizar publicações:

SELECT * FROM pg_publication;
SELECT * FROM pg_publication_tables;

Passo 4: Criar Tabelas de Réplica

No Inscrito:

-- As tabelas devem ter estrutura idêntica
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

Passo 5: Criar Subscrição (Inscrição)

No Inscrito:

CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher.example.com port=5432 dbname=source_db user=replication_user password=secure_password'
    PUBLICATION my_publication;

Passo 6: Verificar a Replicação

No Publicador:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

No Inscrito:

SELECT * FROM pg_stat_subscription;
SELECT * FROM users;  -- Os dados replicados devem aparecer aqui

Configurações Avançadas

Replicação em Nível de Coluna (PostgreSQL 15+)

Replicar apenas colunas específicas:

-- No Publicador: Replicar apenas colunas não sensíveis
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    credit_card VARCHAR(20),  -- Não será replicada
    created_at TIMESTAMP
);

CREATE PUBLICATION customer_basic 
    FOR TABLE customers (id, name, email, created_at);

Filtragem de Linhas

Replicar apenas registros ativos:

CREATE PUBLICATION active_data 
    FOR TABLE orders WHERE (status IN ('pending', 'processing'));

Distribuição regional de dados:

CREATE PUBLICATION us_customers 
    FOR TABLE customers WHERE (country = 'US');

CREATE PUBLICATION eu_customers 
    FOR TABLE customers WHERE (country IN ('UK', 'DE', 'FR'));

Múltiplas Publicações

-- Publicador: Criar múltiplas publicações
CREATE PUBLICATION oltp_data FOR TABLE users, orders;
CREATE PUBLICATION analytics_data FOR TABLE logs, metrics;

-- Inscrito: Inscrever-se em múltiplas publicações
CREATE SUBSCRIPTION multi_sub
    CONNECTION 'host=publisher port=5432 dbname=mydb user=repuser password=pass'
    PUBLICATION oltp_data, analytics_data;

Replicação Bidirecional (Multi-Master)

Configurar Sincronização de Duas Vias

Configuração do Banco de Dados A:

-- Criar publicação
CREATE PUBLICATION db_a_pub FOR TABLE shared_table;

-- Inscrever-se no Banco de Dados B
CREATE SUBSCRIPTION db_a_sub
    CONNECTION 'host=db-b.example.com dbname=mydb user=repuser'
    PUBLICATION db_b_pub
    WITH (origin = none);  -- Previne loops de replicação

Configuração do Banco de Dados B:

-- Criar publicação
CREATE PUBLICATION db_b_pub FOR TABLE shared_table;

-- Inscrever-se no Banco de Dados A
CREATE SUBSCRIPTION db_b_sub
    CONNECTION 'host=db-a.example.com dbname=mydb user=repuser'
    PUBLICATION db_a_pub
    WITH (origin = none);

Resolução de Conflitos

A replicação lógica utiliza "a última escrita vence" (last write wins) por padrão:

-- Definir identidade de réplica para rastrear conflitos
ALTER TABLE shared_table REPLICA IDENTITY FULL;

Estratégias de detecção de conflitos:

1. Baseada em timestamp: Adicionar coluna updated_at

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_shared_table_timestamp
    BEFORE UPDATE ON shared_table
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

2. Numeração de versão:

CREATE TABLE shared_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    version INTEGER DEFAULT 1
);

Opções de Sincronização Inicial de Dados

Opção 1: Cópia Automática (Padrão)

-- O inscrito copia automaticamente os dados existentes
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);  -- Padrão

Opção 2: Sincronização Inicial Manual

Para grandes volumes de dados, use o pg_dump:

# Exportar tabelas específicas do publicador
pg_dump -h publisher.example.com -U postgres -d source_db \
    -t users -t orders --no-owner --no-acl > initial_data.sql

# Carregar no inscrito
psql -h subscriber.example.com -U postgres -d target_db < initial_data.sql

# Criar subscrição sem a cópia inicial
psql -h subscriber.example.com -U postgres -d target_db -c "
    CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=source_db user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = false);
"

Opção 3: Sincronização Inicial Paralela

-- Usar múltiplos workers para uma sincronização inicial mais rápida
CREATE SUBSCRIPTION fast_sync
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (
        copy_data = true,
        streaming = on,
        synchronous_commit = off
    );

Monitoramento da Replicação Lógica

Monitoramento no Publicador

-- Visualizar slots de replicação
SELECT 
    slot_name,
    plugin,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_size
FROM pg_replication_slots;

-- Visualizar conexões de replicação ativas
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS send_lag
FROM pg_stat_replication;

Monitoramento no Inscrito

-- Visualizar status da subscrição
SELECT 
    subname,
    pid,
    received_lsn,
    latest_end_lsn,
    pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
FROM pg_stat_subscription;

-- Verificar erros de replicação
SELECT * FROM pg_stat_subscription WHERE last_msg_receipt_time < NOW() - INTERVAL '5 minutes';

Script de Monitoramento

#!/bin/bash
# logical-replication-monitor.sh

echo "=== Status no Publicador ==="
psql -h publisher -d mydb -c "
    SELECT slot_name, active, 
           pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
    FROM pg_replication_slots;"

echo ""
echo "=== Status no Inscrito ==="
psql -h subscriber -d mydb -c "
    SELECT subname, pid, 
           pg_size_pretty(pg_wal_lsn_diff(latest_end_lsn, received_lsn)) AS lag
    FROM pg_stat_subscription;"

Resolução de Problemas (Troubleshooting)

Problema 1: A Subscrição Não Está Recebendo Dados

Verificar status da subscrição:

SELECT subname, pid, subenabled, subconninfo FROM pg_subscription;

Habilitar a subscrição se estiver desativada:

ALTER SUBSCRIPTION my_sub ENABLE;

Verificar erros:

SELECT * FROM pg_stat_subscription;

Problema 2: O Lag de Replicação Está Aumentando

Identificar tabelas lentas:

SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY (n_tup_ins + n_tup_upd + n_tup_del) DESC;

Aumentar processos de workers:

# postgresql.conf no inscrito
max_logical_replication_workers = 20
max_worker_processes = 30

Problema 3: Inchaço (Bloat) do Slot de Replicação

Verificar uso do slot:

SELECT 
    slot_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Remover slots inativos:

SELECT pg_drop_replication_slot('inactive_slot_name');

Problema 4: Falha na Sincronização Inicial

Reiniciar sincronização inicial:

-- Remover e recriar a subscrição
DROP SUBSCRIPTION my_sub;

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (copy_data = true);

Otimização de Performance

1. Use a Identidade de Réplica Apropriada

-- Padrão: Apenas Chave Primária (PRIMARY KEY)
ALTER TABLE my_table REPLICA IDENTITY DEFAULT;

-- Full: Todas as colunas (maior sobrecarga)
ALTER TABLE my_table REPLICA IDENTITY FULL;

-- Index: Usar um índice único específico
CREATE UNIQUE INDEX replica_idx ON my_table(col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX replica_idx;

2. Desabilitar Constraints Durante a Sincronização Inicial

-- Desativar gatilhos temporariamente para carga inicial mais rápida
ALTER TABLE my_table DISABLE TRIGGER ALL;

-- Após a conclusão da sincronização inicial
ALTER TABLE my_table ENABLE TRIGGER ALL;

3. Aplicação Paralela (PostgreSQL 16+)

CREATE SUBSCRIPTION parallel_sub
    CONNECTION 'host=publisher dbname=mydb user=repuser'
    PUBLICATION my_pub
    WITH (streaming = parallel);

Melhores Práticas

1. Sempre use identidade de réplica: Garanta que as tabelas tenham PRIMARY KEY ou restrição UNIQUE.
2. Monitore o lag de replicação: Configure alertas para lag > 100MB ou 5 minutos.
3. Cuidado com filtros de linha: Filtros são avaliados no publicador, o que afeta a performance.
4. Planeje para conflitos: Implemente detecção de conflitos em configurações multi-master.
5. Teste procedimentos de failover: Pratique a alternância entre bancos de dados.
6. Manutenção regular: Limpe slots de replicação antigos.

Conclusão

A replicação lógica oferece recursos poderosos de distribuição de dados para o PostgreSQL. Principais vantagens:

• Replicação seletiva de tabelas e linhas
• Compatibilidade entre diferentes versões
• Configurações multi-master bidirecionais
• Controle granular de privacidade de dados

Comece com uma replicação unidirecional simples, teste exaustivamente e, em seguida, expanda para topologias mais complexas conforme necessário.