Replicação streaming do PostgreSQL

# /etc/postgresql/17/main/postgresql.conf (primário)
wal_level = replica
max_wal_senders = 3              # número máximo de standbys
wal_keep_size = 256MB            # manter WAL para os standbys
listen_addresses = '*'

# Habilitar replicação por slot (não perde WAL mesmo com lag):
# max_replication_slots = 3      # opcional, mais seguro

# /etc/postgresql/17/main/pg_hba.conf (primário)
# Adicionar: permitir o usuário de replicação do servidor standby
host    replication     replicador      IP_STANDBY/32    scram-sha-256

# Reiniciar primário
sudo systemctl restart postgresql

# Criar usuário de replicação
sudo -u postgres psql
CREATE ROLE replicador WITH REPLICATION LOGIN PASSWORD 'senha_replicacao';
\q

# No servidor standby — parar PostgreSQL se estiver rodando
sudo systemctl stop postgresql

# Limpar diretório de dados do standby
sudo rm -rf /var/lib/postgresql/17/main/*

# Cópia base do primário (rodada como postgres)
sudo -u postgres pg_basebackup \
  --host=IP_PRIMARIO \
  --username=replicador \
  --pgdata=/var/lib/postgresql/17/main \
  --wal-method=stream \
  --checkpoint=fast \
  --progress \
  --verbose

# Criar arquivo de configuração de standby
sudo -u postgres cat > /var/lib/postgresql/17/main/postgresql.auto.conf << 'EOF'
primary_conninfo = 'host=IP_PRIMARIO port=5432 user=replicador password=senha_replicacao'
recovery_target_timeline = 'latest'
EOF

# Criar arquivo de sinal de standby (PostgreSQL 12+)
sudo -u postgres touch /var/lib/postgresql/17/main/standby.signal

# Iniciar standby
sudo systemctl start postgresql

# Verificar se está em modo standby:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# t = standby (recuperação), f = primário

-- No primário — ver status dos standbys conectados:
SELECT client_addr,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       write_lag,
       flush_lag,
       replay_lag,
       sync_state
FROM pg_stat_replication;

-- No standby — verificar lag em relação ao primário:
SELECT now() - pg_last_xact_replay_timestamp() AS lag_tempo,
       pg_is_in_recovery() AS em_standby,
       pg_last_wal_receive_lsn() AS recebido_ate,
       pg_last_wal_replay_lsn() AS aplicado_ate;

-- Verificar diferença em bytes:
-- No primário:
SELECT pg_current_wal_lsn();
-- No standby:
SELECT pg_last_wal_replay_lsn();
-- Diferença: SELECT 'a'::pg_lsn - 'b'::pg_lsn;

# === Failover Manual ===

# 1. Verificar que o primário está realmente indisponível
pg_isready -h IP_PRIMARIO -p 5432
# Se retornar erro, prosseguir com failover

# 2. No servidor standby — promover para primário
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/17/main
# Ou:
sudo -u postgres psql -c "SELECT pg_promote();"

# 3. Verificar que o standby foi promovido
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
# f = agora é primário

# 4. Atualizar a aplicação para apontar para o novo primário
# (altere variável DATABASE_URL ou DNS)

# 5. Quando o primário antigo voltar:
# - Reinicializá-lo como standby do novo primário
# - Repetir o processo de pg_basebackup a partir do novo primário

-- === Replicação Lógica (para migração de versão major) ===

-- No primário (PostgreSQL 16):
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();

-- Criar publicação de todas as tabelas:
CREATE PUBLICATION migracao_pub FOR ALL TABLES;

-- No novo servidor (PostgreSQL 17):
-- Após copiar o schema (pg_dump --schema-only):
CREATE SUBSCRIPTION migracao_sub
  CONNECTION 'host=IP_PRIMARIO port=5432 user=replicador
              password=senha_replicacao dbname=minha_db'
  PUBLICATION migracao_pub;

-- Monitorar progresso da sincronização inicial:
SELECT subname, pid, relid::regclass AS tabela, state, received_lsn
FROM pg_stat_subscription_stats;

-- Quando sincronizado (state = 'ready' para todas as tabelas):
-- 1. Parar a aplicação (janela de manutenção mínima)
-- 2. Confirmar que o lag zerou
-- 3. Apontar a aplicação para o novo servidor
-- 4. Remover a subscription