Database maintenance scripts: backup, vacuum, optimize

1. Introdução à Manutenção de Banco de Dados com Shell Script

Automatizar tarefas de manutenção de banco de dados é essencial para garantir disponibilidade, desempenho e segurança dos dados. Scripts em Bash permitem orquestrar backups, operações de vacuum e otimização de forma consistente e sem intervenção manual. Esta abordagem é aplicável aos principais SGBDs: PostgreSQL, MySQL/MariaDB e SQLite.

Um script de manutenção bem estruturado deve incluir variáveis de ambiente para configuração, sistema de logs para auditoria e tratamento de erros robusto. A estrutura básica segue o padrão:

#!/bin/bash
set -euo pipefail

# Configurações
DB_NAME="meu_banco"
DB_USER="admin"
BACKUP_DIR="/var/backups"
LOG_FILE="/var/log/db_maintenance.log"

# Função de log
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

2. Scripts de Backup Automatizado

Backups são a primeira linha de defesa contra perda de dados. Para bancos PostgreSQL, utilizamos pg_dump, para MySQL/MariaDB o mysqldump, e para SQLite o comando .backup do sqlite3.

Backup completo com compactação e criptografia:

#!/bin/bash
DB_NAME="prod_db"
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql"

# PostgreSQL
pg_dump -U "$DB_USER" "$DB_NAME" > "$BACKUP_FILE"

# MySQL/MariaDB
# mysqldump -u "$DB_USER" "$DB_NAME" > "$BACKUP_FILE"

# Compactar e criptografar
gzip -9 "$BACKUP_FILE"
openssl enc -aes-256-cbc -salt -in "${BACKUP_FILE}.gz" \
    -out "${BACKUP_FILE}.gz.enc" -pass pass:"$ENCRYPTION_KEY"

# Rotação: manter apenas os 7 backups mais recentes
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz.enc" -type f | \
    sort -r | tail -n +8 | xargs -r rm -f

Para backup incremental no PostgreSQL, podemos usar o pg_basebackup com WAL archiving, mas para scripts simples, backup completo com rotação é suficiente e mais fácil de gerenciar.

3. Vacuum e Otimização de Tabelas

O PostgreSQL requer vacuum regular para recuperar espaço e atualizar estatísticas. O MySQL/MariaDB possui o comando OPTIMIZE TABLE para desfragmentação.

Script de vacuum no PostgreSQL:

#!/bin/bash
DB_NAME="prod_db"

# Vacuum completo com analyze
psql -U "$DB_USER" -d "$DB_NAME" -c "VACUUM FULL ANALYZE;"

# Vacuum freeze para prevenir wraparound de transaction ID
psql -U "$DB_USER" -d "$DB_NAME" -c "VACUUM FREEZE;"

Otimização condicional no MySQL:

#!/bin/bash
DB_NAME="prod_db"
THRESHOLD=50  # Porcentagem de fragmentação

for table in $(mysql -u "$DB_USER" -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND DATA_FREE > 0"); do
    fragment=$(mysql -u "$DB_USER" -N -e "SELECT ROUND(DATA_FREE/1024/1024,2) FROM information_schema.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_NAME='$table'")
    if (( $(echo "$fragment > $THRESHOLD" | bc -l) )); then
        mysql -u "$DB_USER" "$DB_NAME" -e "OPTIMIZE TABLE $table;"
        log "Tabela $table otimizada (fragmentação: ${fragment}MB)"
    fi
done

4. Monitoramento e Health Checks Pré-Maintenance

Antes de executar manutenção, é crucial verificar o ambiente. Espaço em disco insuficiente ou banco offline podem causar falhas catastróficas.

#!/bin/bash
# Verificação de espaço em disco
MIN_SPACE_GB=10
available=$(df -BG "$BACKUP_DIR" | tail -1 | awk '{print $4}' | sed 's/G//')
if [ "$available" -lt "$MIN_SPACE_GB" ]; then
    log "ERRO: Espaço insuficiente em $BACKUP_DIR (${available}GB disponível)"
    exit 1
fi

# Teste de conectividade
pg_isready -U "$DB_USER" -d "$DB_NAME" || { log "ERRO: Banco offline"; exit 1; }

# Validação de integridade (PostgreSQL)
pg_checksums -c -D "$PGDATA" 2>/dev/null || log "AVISO: Checksums não habilitados"

Para MySQL, use mysqlcheck:

mysqlcheck -u "$DB_USER" --all-databases --check

5. Tratamento de Erros e Notificações

Um script robusto deve capturar erros e notificar a equipe. Use trap para ações em caso de falha e set -e para interromper execução em erro.

#!/bin/bash
set -e
trap 'log "ERRO: Script interrompido na linha $LINENO"; notify_error' ERR

notify_error() {
    # Email via mailx
    echo "Falha na manutenção do banco $DB_NAME" | \
        mailx -s "[ALERTA] Manutenção DB" admin@empresa.com

    # Webhook (Slack/Discord)
    curl -X POST -H "Content-Type: application/json" \
        -d '{"text":"Falha na manutenção do banco"}' \
        "$WEBHOOK_URL"
}

# Rollback em caso de falha crítica
rollback_backup() {
    if [ -f "$BACKUP_FILE" ]; then
        log "Realizando rollback: restaurando backup anterior"
        psql -U "$DB_USER" "$DB_NAME" < "$BACKUP_FILE"
    fi
}

6. Agendamento e Integração com Cron

O cron é a ferramenta padrão para agendamento no Linux. Configure jobs no crontab para execução periódica.

# Cron job para backup diário às 2h
0 2 * * * /usr/local/bin/db_backup.sh >> /var/log/cron_db.log 2>&1

# Cron job para vacuum semanal aos domingos às 3h
0 3 * * 0 /usr/local/bin/db_vacuum.sh >> /var/log/cron_db.log 2>&1

Evitando sobreposição com lock files:

#!/bin/bash
LOCK_FILE="/tmp/db_maintenance.lock"
exec 200>"$LOCK_FILE"
flock -n 200 || { log "Script já em execução"; exit 1; }

# ... código de manutenção ...

flock -u 200
rm -f "$LOCK_FILE"

7. Script Completo de Manutenção Unificado

Abaixo, um script modular que integra backup, vacuum e limpeza, com parâmetros via getopts:

#!/bin/bash
set -euo pipefail

# Configurações padrão
DB_NAME=""
DB_TYPE="postgres"
ACTION="all"
BACKUP_DIR="/var/backups"
LOG_FILE="/var/log/db_maintenance.log"
RETENTION_DAYS=7

usage() {
    echo "Uso: $0 -d banco [-t postgres|mysql|sqlite] [-a backup|vacuum|cleanup|all]"
    exit 1
}

while getopts "d:t:a:" opt; do
    case $opt in
        d) DB_NAME="$OPTARG" ;;
        t) DB_TYPE="$OPTARG" ;;
        a) ACTION="$OPTARG" ;;
        *) usage ;;
    esac
done

[ -z "$DB_NAME" ] && usage

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

do_backup() {
    log "Iniciando backup de $DB_NAME"
    local file="${BACKUP_DIR}/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql"
    case $DB_TYPE in
        postgres) pg_dump -U admin "$DB_NAME" > "$file" ;;
        mysql) mysqldump -u admin "$DB_NAME" > "$file" ;;
        sqlite) sqlite3 "$DB_NAME" ".backup $file" ;;
    esac
    gzip -9 "$file"
    log "Backup concluído: ${file}.gz"
}

do_vacuum() {
    log "Iniciando vacuum/optimize em $DB_NAME"
    case $DB_TYPE in
        postgres) 
            psql -U admin -d "$DB_NAME" -c "VACUUM FULL ANALYZE;" 
            psql -U admin -d "$DB_NAME" -c "VACUUM FREEZE;"
            ;;
        mysql) 
            mysql -u admin "$DB_NAME" -e "OPTIMIZE TABLE $(mysql -u admin -N -e 'SELECT GROUP_CONCAT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA=\"$DB_NAME\"');"
            ;;
    esac
    log "Vacuum/optimize concluído"
}

do_cleanup() {
    log "Removendo backups com mais de $RETENTION_DAYS dias"
    find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete
}

case $ACTION in
    backup) do_backup ;;
    vacuum) do_vacuum ;;
    cleanup) do_cleanup ;;
    all) do_backup; do_vacuum; do_cleanup ;;
esac

log "Manutenção de $DB_NAME concluída com sucesso"

8. Considerações Finais e Próximos Passos

A automação de manutenção de banco de dados com shell scripts é uma prática que economiza tempo e reduz erros humanos. Antes de colocar em produção, teste exaustivamente em ambiente de staging. Considere integrar estes scripts com ferramentas de monitoramento (como Zabbix ou Prometheus) e sistemas de logs centralizados (ELK, Graylog) para visibilidade completa.

Para aprofundamento, explore recursos de alta disponibilidade como replicação primária-réplica e estratégias de backup point-in-time recovery (PITR). A modularização dos scripts facilita a manutenção e evolução do sistema.

Referências