Backup e restore de bancos PostgreSQL em produção com pg_dump e WAL

1. Fundamentos de Backup em PostgreSQL: Estratégias e Conceitos

Em ambientes de produção, a escolha entre backup lógico e físico determina diretamente a capacidade de recuperação. O pg_dump realiza backups lógicos, exportando comandos SQL que recriam objetos e dados, ideal para migrações entre versões ou recuperação seletiva de tabelas. Já o WAL (Write-Ahead Log) archiving permite backup físico contínuo, capturando cada transação para Point-in-Time Recovery (PITR).

Os indicadores críticos são RPO (quantos minutos de dados podem ser perdidos) e RTO (tempo máximo para restaurar o serviço). Para sistemas 24x7, WAL archiving com RPO de minutos e RTO de horas é padrão. Backup full semanal com WAL diário equilibra custo de armazenamento e segurança.

2. pg_dump e pg_dumpall: Backup Lógico em Produção

O pg_dump oferece formatos essenciais para produção. O formato custom (-Fc) permite compressão e paralelismo na restauração. Exemplo prático:

pg_dump -h localhost -U admin --format=custom --compress=9 \
  --jobs=4 --file=/backup/producao_$(date +%Y%m%d).dump \
  --exclude-table=dados_logs_2023 \
  meu_banco

Para backup de schemas específicos:

pg_dump -h localhost -U admin --schema=vendas --schema=estoque \
  --format=custom --compress=9 \
  --file=/backup/schemas_vendas_estoque.dump \
  meu_banco

Automação com cron e rotação de 7 dias:

#!/bin/bash
BACKUP_DIR="/backup"
RETENTION_DAYS=7
DB_NAME="meu_banco"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

pg_dump -h localhost -U admin --format=custom --compress=9 \
  --file="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump" \
  ${DB_NAME}

find ${BACKUP_DIR} -name "${DB_NAME}_*.dump" -mtime +${RETENTION_DAYS} -delete

3. WAL Archiving e Point-in-Time Recovery (PITR)

Configure o postgresql.conf para archiving contínuo:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'
archive_timeout = 60

O pg_basebackup cria um snapshot consistente para iniciar o PITR:

pg_basebackup -h localhost -U admin -D /backup/base_backup \
  --format=tar --gzip --wal-method=stream \
  --label="backup_semanal_$(date +%Y%m%d)"

Para recuperação point-in-time, crie o arquivo recovery.signal no diretório de dados e configure postgresql.conf:

restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2025-03-15 14:30:00 BRT'

4. Estratégias de Restore com pg_restore e WAL

Restauração de dump customizado com paralelismo e seletividade:

pg_restore -h localhost -U admin --dbname=meu_banco \
  --jobs=4 --clean --if-exists \
  /backup/producao_20250315.dump

Para restaurar apenas uma tabela específica:

pg_restore -h localhost -U admin --dbname=meu_banco \
  --table=vendas.pedidos --data-only \
  /backup/producao_20250315.dump

Recuperação completa via WAL com PITR:

# 1. Pare o PostgreSQL
systemctl stop postgresql

# 2. Restaure o base backup
rm -rf /var/lib/postgresql/15/main/*
tar -xzf /backup/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

# 3. Configure recovery
echo "restore_command = 'cp /wal_archive/%f %p'" >> /etc/postgresql/15/main/postgresql.conf
echo "recovery_target_time = '2025-03-15 14:30:00 BRT'" >> /etc/postgresql/15/main/postgresql.conf
touch /var/lib/postgresql/15/main/recovery.signal

# 4. Inicie e monitore
systemctl start postgresql
tail -f /var/log/postgresql/postgresql-15-main.log

5. Validação e Testes de Backup em Ambiente de Produção

Verificação de integridade do dump:

# Listar conteúdo sem restaurar
pg_restore --list /backup/producao_20250315.dump | head -50

# Verificar checksum (formato custom já inclui)
pg_restore --verbose --exit-on-error /backup/producao_20250315.dump > /dev/null 2>&1
echo $?  # 0 = íntegro

Script de validação automatizada:

#!/bin/bash
DUMP_FILE="/backup/producao_20250315.dump"
LOG_FILE="/var/log/backup_validation.log"

echo "$(date) - Validando $DUMP_FILE" >> $LOG_FILE
pg_restore --list $DUMP_FILE > /dev/null 2>&1
if [ $? -eq 0 ]; then
    echo "OK - Dump íntegro" >> $LOG_FILE
else
    echo "FALHA - Dump corrompido" >> $LOG_FILE
    mail -s "Alerta: Backup corrompido" admin@empresa.com < $LOG_FILE
fi

6. Boas Práticas e Automação para Ambientes Críticos

Para ambientes de alta criticidade, ferramentas como pgBackRest e barman oferecem gerenciamento avançado. Exemplo com pgBackRest:

# Configuração pgbackrest.conf
[meu_banco]
pg1-path=/var/lib/postgresql/15/main
pg1-port=5432
repo1-path=/backup/pgbackrest
repo1-retention-full=4
repo1-retention-diff=7
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=senha_segura

# Backup full semanal
pgbackrest --stanza=meu_banco --type=full backup

# Restore point-in-time
pgbackrest --stanza=meu_banco --type=time \
  --target="2025-03-15 14:30:00 BRT" restore

Política recomendada para produção:
- Backup full semanal (domingo 02:00)
- WAL archiving contínuo (a cada 60s)
- Retenção: 4 fulls + 30 dias de WALs
- Armazenamento offsite: S3 com criptografia AES-256
- Teste de restore completo a cada 30 dias

Segurança adicional:

# Criptografar dump com gpg
gpg --symmetric --cipher-algo AES256 --output dump.sql.gpg dump.sql

# Upload para S3 com rotação
aws s3 sync /backup/ s3://meu-bucket-backup/ \
  --sse AES256 --delete --exclude "*.tmp"

Referências