Backup automático do PostgreSQL

#!/bin/bash
# /opt/scripts/backup-postgres.sh
set -euo pipefail

# Configuração
DB_NAME="minha_db"
DB_USER="minha_app"
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
LOG="/var/log/postgres-backup.log"

exec >> "$LOG" 2>&1
echo "=== Backup iniciado: $(date) ==="

# Criar diretório se não existir
mkdir -p "$BACKUP_DIR"
chmod 750 "$BACKUP_DIR"

# Gerar backup comprimido
PGPASSWORD="$DB_PASSWORD" pg_dump \
  --host=localhost \
  --username="$DB_USER" \
  --dbname="$DB_NAME" \
  --no-password \
  --format=plain \
  --verbose \
  | gzip -9 > "$BACKUP_FILE"

TAMANHO=$(du -sh "$BACKUP_FILE" | cut -f1)
echo "Backup gerado: $BACKUP_FILE ($TAMANHO)"

# Upload para armazenamento externo (S3-compatible)
aws s3 cp "$BACKUP_FILE" "s3://meu-bucket-backup/postgres/${DB_NAME}/" \
  --storage-class STANDARD_IA

echo "Upload concluído para S3"

# Remover backups locais antigos
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Backups locais com mais de $RETENTION_DAYS dias removidos"

echo "=== Backup concluído: $(date) ==="

# Criar arquivo .pgpass para autenticação sem senha
echo "localhost:5432:minha_db:minha_app:SENHA_AQUI" >> ~/.pgpass
chmod 600 ~/.pgpass

# Ou usar variável de ambiente no script de backup
# Criar arquivo de ambiente seguro:
cat > /etc/backup-postgres.env << 'EOF'
DB_PASSWORD=senha_forte_aqui
AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE
AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
AWS_DEFAULT_REGION=sa-east-1
EOF
chmod 600 /etc/backup-postgres.env

# Editar crontab do postgres ou do usuário de backup
crontab -e

# Backup diário às 3h da manhã:
0 3 * * * . /etc/backup-postgres.env && /opt/scripts/backup-postgres.sh

# Backup a cada 6 horas (para RPO menor):
0 */6 * * * . /etc/backup-postgres.env && /opt/scripts/backup-postgres.sh

# Verificar logs do cron:
grep CRON /var/log/syslog | tail -20

# Instalar rclone (suporta S3, B2, GCS, Wasabi, e mais)
curl https://rclone.org/install.sh | sudo bash

# Configurar B2 (interativo):
rclone config
# Seguir as instruções: New remote → B2 → Application Key ID + Key

# Ou configurar diretamente:
mkdir -p ~/.config/rclone
cat > ~/.config/rclone/rclone.conf << 'EOF'
[b2-backup]
type = b2
account = APPLICATION_KEY_ID
key = APPLICATION_KEY
EOF
chmod 600 ~/.config/rclone/rclone.conf

# No script de backup — substituir o aws s3 cp por:
rclone copy "$BACKUP_FILE" b2-backup:meu-bucket-backup/postgres/

# Listar backups no B2:
rclone ls b2-backup:meu-bucket-backup/postgres/

# B2 custa ~$0.006/GB/mês vs S3 Standard IA $0.0125/GB/mês

# pg_dump — backup LÓGICO (recomendado para maioria dos casos)
# + Portátil: restaura em qualquer versão do PostgreSQL
# + Seletivo: pode fazer backup de um banco, tabela ou schema específico
# + Não requer parar o banco
# + Arquivo menor (apenas dados e schema)

# Formatos do pg_dump:
# --format=plain: SQL legível (compatível com psql)
# --format=custom: binário comprimido (melhor para pg_restore)
# --format=directory: múltiplos arquivos (permite restore paralelo)

# Backup apenas do schema (sem dados):
pg_dump --schema-only minha_db > schema_only.sql

# Backup apenas dos dados (sem schema):
pg_dump --data-only minha_db > data_only.sql

# Backup de uma tabela específica:
pg_dump --table=usuarios minha_db > usuarios.sql

# pg_basebackup — backup FÍSICO
# + Ponto de partida para réplicas de streaming
# + Backup de TODOS os bancos do cluster
# + Base para Point-in-Time Recovery (PITR)
# - Requer mesma versão do PostgreSQL para restaurar

pg_basebackup -D /var/backups/pgbase -Ft -z -P

#!/bin/bash
# /opt/scripts/test-backup-postgres.sh
set -euo pipefail

BACKUP_FILE="$1"   # passar o arquivo de backup como argumento
TEST_DB="restore_test_$(date +%s)"
LOG="/var/log/postgres-backup-test.log"
exec >> "$LOG" 2>&1

echo "=== Teste de restore: $(date) ==="
echo "Arquivo: $BACKUP_FILE"

# Criar banco de teste
sudo -u postgres createdb "$TEST_DB"

# Restaurar backup
gunzip -c "$BACKUP_FILE" | sudo -u postgres psql "$TEST_DB" > /dev/null

# Verificar que tabelas foram criadas
TABELAS=$(sudo -u postgres psql -d "$TEST_DB" -t -c "
SELECT count(*) FROM information_schema.tables
WHERE table_schema = 'public'
")
echo "Tabelas restauradas: $TABELAS"

if [ "$TABELAS" -eq 0 ]; then
  echo "ERRO: Nenhuma tabela no banco restaurado!"
  sudo -u postgres dropdb "$TEST_DB"
  exit 1
fi

# Remover banco de teste
sudo -u postgres dropdb "$TEST_DB"
echo "=== Teste de restore concluído com sucesso ==="