KB Platform
kb.erickguedes.com
← PostgreSQL: Banco Relacional Avançado

Replicação e Backup

Aula 5 de 7

WAL — Write Ahead Log

-- WAL: todo dado é escrito no log antes de ir para o data file
-- Garante atomicidade e durabilidade (A e D do ACID)

-- Configurações WAL
SHOW wal_level;               -- replica, logical, minimal
SHOW wal_buffers;             -- 16MB default
SHOW wal_keep_size;           -- 1GB (retention)
SHOW max_wal_size;            -- 1GB (checkpoint trigger)
SHOW min_wal_size;            -- 80MB

-- Ver LSN atual
SELECT pg_current_wal_lsn();
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();

-- Ver arquivos WAL
SELECT * FROM pg_ls_waldir();

Streaming Replication

Primary (standby.signal ausente)

# postgresql.conf (PRIMARY)
wal_level = replica
max_wal_senders = 10          # número máximo de standbys
wal_keep_size = 1024          # MB para manter WAL
max_replication_slots = 10
synchronous_commit = on       # aguarda standby confirmar
synchronous_standby_names = 'FIRST 1 (standby1)'

# pg_hba.conf (PRIMARY)
# Permitir replicação do IP do standby
host replication replicator 192.168.1.20/32 scram-sha-256

Standby (hot standby)

# postgresql.conf (STANDBY)
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=senha'
hot_standby = on
hot_standby_feedback = on     # evita clean conflicts

# Criar arquivo de sinalização
touch /var/lib/postgresql/16/main/standby.signal

# Fazer base backup
pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/16/main \
    -U replicator -v -P --wal-method=stream

# Iniciar standby (inicia como réplica automaticamente)
sudo systemctl start postgresql

Verificação

-- No primary: verificar replicação
SELECT pid, application_name, state, sync_state, write_lag
FROM pg_stat_replication;

-- No standby: verificar status
SELECT pg_is_in_recovery();           -- true se standby
SELECT pg_last_wal_receive_lsn();
SELECT pg_last_wal_replay_lsn();
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS lag;

Logical Replication

-- Publicação (PUBLISHER)
CREATE PUBLICATION pub_vendas
    FOR TABLE clientes, pedidos, itens_pedido
    WITH (publish = 'insert, update, delete');

CREATE PUBLICATION pub_todos
    FOR ALL TABLES;

-- Assinatura (SUBSCRIBER)
CREATE SUBSCRIPTION sub_vendas
    CONNECTION 'host=192.168.1.10 port=5432 dbname=db user=replicator password=senha'
    PUBLICATION pub_vendas;

-- Gerenciar
ALTER PUBLICATION pub_vendas ADD TABLE produtos;
ALTER PUBLICATION pub_vendas DROP TABLE itens_pedido;
DROP SUBSCRIPTION sub_vendas;

-- Monitorar
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;

pg_dump / pg_dumpall / pg_restore

pg_dump (por database)

# Formato custom (recomendado: compressão + paralelo)
pg_dump -h localhost -U postgres -Fc meubanco > meubanco.dump

# Formato plain (SQL texto)
pg_dump -U postgres meubanco > meubanco.sql

# Apenas schema (sem dados)
pg_dump -U postgres --schema-only meubanco > schema.sql

# Apenas dados (sem schema)
pg_dump -U postgres --data-only --exclude-table=logs meubanco > dados.sql

# Comprimir e paralelo
pg_dump -U postgres -Fd -j 4 meubanco -f /backup/dir/  # directory format + jobs

# Tabelas específicas
pg_dump -U postgres -t clientes -t pedidos meubanco > vendas.sql

pg_dumpall (tudo)

# Backup completo do cluster (roles, tablespaces, databases)
pg_dumpall -U postgres > cluster.sql

# Apenas roles e tablespaces
pg_dumpall -U postgres --globals-only > globals.sql

pg_restore

# Restaurar custom dump
pg_restore -U postgres -d meubanco meubanco.dump

# Com paralelismo
pg_restore -U postgres -d meubanco -j 4 meubanco.dump

# Apenas schema
pg_restore -U postgres --schema-only -d meubanco meubanco.dump

# Listar conteúdo do dump
pg_restore -l meubanco.dump

# Restaurar tabelas específicas
pg_restore -U postgres -d meubanco -t clientes -t pedidos meubanco.dump

# Sobrescrever objetos existentes (--clean)
pg_restore -U postgres --clean --if-exists -d meubanco meubanco.dump

PITR — Point In Time Recovery

# postgresql.conf (para WAL archiving)
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
archive_timeout = 60           # forçar arquivamento a cada 60s

# 1. Fazer base backup
pg_basebackup -h localhost -U postgres -D /backup/base \
    -Fp -Xs -P -v
# -Fp: plain format (diretório)
# -Xs: incluir WAL no backup

# 2. Simular desastre
sudo systemctl stop postgresql
rm -rf /var/lib/postgresql/16/main/

# 3. Restaurar base backup
cp -r /backup/base/* /var/lib/postgresql/16/main/

# 4. Criar recovery.conf (PostgreSQL < 12) ou recovery.signal
cat > /var/lib/postgresql/16/main/recovery.signal

# 5. Configurar recovery (no postgresql.conf ou recovery.conf)
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-06-15 14:30:00 UTC'
# recovery_target_lsn = '0/1A2B3C4D'
# recovery_target_xid = '123456'
# recovery_target = 'immediate'  # para mais recente possível

# 6. Iniciar PostgreSQL (aplica WAL até o target)
sudo systemctl start postgresql

# 7. Verificar recovery
SELECT pg_is_in_recovery();
SELECT pg_last_wal_replay_lsn();
SELECT recovery_status FROM pg_stat_progress_recovery;

pgBackRest

# Instalação
sudo apt install pgbackrest -y

# Configuração /etc/pgbackrest/pgbackrest.conf
cat << EOF | sudo tee -a /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
compress-type=gz
process-max=3

[mycluster]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
EOF

# Backup
sudo -u postgres pgbackrest --stanza=mycluster --type=full backup
sudo -u postgres pgbackrest --stanza=mycluster --type=diff backup
sudo -u postgres pgbackrest --stanza=mycluster --type=incr backup

# Listar backups
sudo -u postgres pgbackrest --stanza=mycluster info

# Restaurar
sudo -u postgres pgbackrest --stanza=mycluster --type=time \
    --target="2024-06-15 14:30:00" restore

Lab: Script de Backup e Réplica

cat > pg-manager.sh << 'SCRIPT'
#!/bin/bash
set -euo pipefail

readonly PGUSER="postgres"
readonly PGHOST="localhost"
readonly BACKUP_DIR="/backup"
readonly DATE=$(date +%Y%m%d-%H%M%S)

backup_full() {
    local db="$1"
    echo "=== BACKUP FULL: $db ==="
    pg_dump -U "$PGUSER" -h "$PGHOST" -Fc -Z 9 \
        -f "$BACKUP_DIR/$db-$DATE.dump" "$db"
    echo "Backup: $BACKUP_DIR/$db-$DATE.dump"
    ls -lh "$BACKUP_DIR/$db-$DATE.dump"
}

backup_all() {
    echo "=== BACKUP ALL DATABASES ==="
    pg_dumpall -U "$PGUSER" -h "$PGHOST" \
        -f "$BACKUP_DIR/cluster-$DATE.sql"
    gzip "$BACKUP_DIR/cluster-$DATE.sql"
    echo "Backup: $BACKUP_DIR/cluster-$DATE.sql.gz"
}

backup_wal() {
    echo "=== BACKUP WAL ==="
    psql -U "$PGUSER" -h "$PGHOST" -c "SELECT pg_switch_wal();" 2>/dev/null || true
    psql -U "$PGUSER" -h "$PGHOST" -c "SELECT pg_backup_start('manual_backup');"
    rsync -az /var/lib/postgresql/16/main/pg_wal/ "$BACKUP_DIR/wal-$DATE/"
    psql -U "$PGUSER" -h "$PGHOST" -c "SELECT pg_backup_stop();"
}

restore() {
    local dump="$1"
    local db="$2"
    echo "=== RESTORE: $dump -> $db ==="
    pg_restore -U "$PGUSER" -h "$PGHOST" -d "$db" -j 4 --clean "$dump"
}

case "${1:-}" in
    backup)     backup_full "${2:-meubanco}" ;;
    backup-all) backup_all ;;
    wal)        backup_wal ;;
    restore)    restore "$2" "${3:-meubanco}" ;;
    *)
        echo "Uso: $0 {backup [db] | backup-all | wal | restore <dump> [db]}"
        exit 1
        ;;
esac
SCRIPT

chmod +x pg-manager.sh

WAL é a base da durabilidade e replicação. Streaming replication mantém hot standby síncrono ou assíncrono. Logical replication permite replicação seletiva entre versões. pg_dump -Fc com compressão e paralelo é o padrão ouro. PITR recupera até um ponto específico no tempo.