Automatize Backups PostgreSQL com pg_dump e Cron

Introdução à Automação de Backups no PostgreSQL

A administração de bancos de dados em ambientes de VPS (Virtual Private Server) exige uma postura proativa, especialmente quando se trata de integridade e disponibilidade dos dados. O PostgreSQL é um dos sistemas de gerenciamento de banco de dados relacionais mais robustos e utilizados no mercado, oferecendo alta confiabilidade e extensibilidade. No entanto, a resiliência do sistema depende diretamente da estratégia de backup implementada pelo administrador.

Muitos iniciantes cometem o erro de confiar apenas nas ferramentas nativas ou em snapshots de disco, ignorando que uma cópia lógica consistente é frequentemente necessária para migrações, recuperação granular de tabelas ou transferência entre versões diferentes do SGBD. Neste tutorial, vamos explorar como utilizar a ferramenta pg_dump combinada com o agendador de tarefas Cron para criar um sistema automatizado de backups completos.

O objetivo é fornecer um script bash prático e uma configuração de cronjob segura, permitindo que você recupere seus dados em caso de desastres, corrupção lógica ou falhas de hardware. Este processo é essencial para qualquer profissional de TI que gerencia infraestrutura em nuvem.

Pré-requisitos e Preparação do Ambiente

Antes de iniciar a automação, é fundamental garantir que o ambiente esteja preparado. Você precisa de acesso root ou com privilégios sudo na sua VPS Linux (Ubuntu, Debian, CentOS, etc.). Além disso, certifique-se de que o PostgreSQL está instalado e em execução.

Verifique o status do serviço:

systemctl status postgresql

Caso ainda não tenha a ferramenta pg_dump instalada, ela geralmente vem incluída no pacote principal do PostgreSQL. Se estiver em um ambiente minimalista, instale-a com:

sudo apt install postgresql-client # Debian/Ubuntu
sudo yum install postgresql       # CentOS/RHEL

Dica de Segurança: Nunca execute scripts de backup como usuário root diretamente no banco. O PostgreSQL possui um modelo de autenticação baseado em usuários do sistema operacional ou via arquivo pg_hba.conf. Para este tutorial, assumiremos que você criará um usuário dedicado para backups ou utilizará o usuário padrão do postgres com cuidado.

Passo 1: Criando um Usuário Dedicado para Backups

A boa prática de administração de banco de dados recomenda o princípio do menor privilégio. Em vez de usar a senha do superusuário postgres no seu script, crie um usuário específico com permissão apenas para leitura e dump.

Acesse o shell do PostgreSQL:

sudo -u postgres psql

Dentro do prompt SQL, execute os comandos abaixo. Substitua backup_user e senha_forte por credenciais reais e seguras:

CREATE USER backup_user WITH PASSWORD 'senha_forte';
GRANT pg_read_all_data TO backup_user;
\q

Agora, atualize o arquivo pg_hba.conf para permitir que esse usuário faça conexões locais usando autenticação por senha (md5 ou scram-sha-256). Adicione a seguinte linha ao final do arquivo de configuração:

local   all             backup_user                                md5

Recarregue a configuração do PostgreSQL para aplicar as alterações:

sudo systemctl reload postgresql

Passo 2: Estruturando o Diretório de Backups

Criamos uma hierarquia de diretórios para organizar os dumps. Isso facilita a manutenção e a limpeza automática de arquivos antigos.

sudo mkdir -p /var/backups/postgresql/daily
sudo chown -R postgres:postgres /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql

A permissão 700 garante que apenas o usuário postgres (ou root) possa acessar esses arquivos sensíveis, protegendo contra vazamentos de dados no sistema de arquivos.

Passo 3: Desenvolvendo o Script Bash de Backup

O coração da automação é um script em bash. Vamos criar um arquivo chamado backup_postgresql.sh.

sudo nano /usr/local/bin/backup_postgresql.sh

Cole o seguinte conteúdo. Este script realiza backup de todos os bancos de dados, comprime o arquivo e remove backups antigos (ex: mais de 7 dias).

#!/bin/bash

# Configurações
BACKUP_DIR="/var/backups/postgresql/daily"
RETENTION_DAYS=7
DATE=$(date +"%Y%m%d_%H%M%S")
HOST="localhost"
USER="backup_user"
DB_NAME="all" # Ou especifique um banco específico, ex: 'meu_banco'

# Arquivo de credenciais .pgpass (Opcional mas recomendado)
# Se não usar .pgpass, será solicitado a senha interativamente ou falhará.
export PGPASSWORD='senha_forte'

echo "Iniciando backup em $DATE..."

# Comando pg_dump
# -F c: Formato customizado (recomendado para restore flexível)
# -c: Limpar dados antes de restaurar
# -C: Criar comandos para criar o banco de dados
# -v: Modoverbose para logs
pg_dump -h $HOST -U $USER -Fc -c -C -v $DB_NAME > "${BACKUP_DIR}/backup_${DATE}.dump"

STATUS=$?

if [ $STATUS -eq 0 ]; then
    echo "Backup concluído com sucesso."
else
    echo "Erro ao realizar backup. Código: $STATUS"
    exit 1
fi

# Limpeza de backups antigos
find ${BACKUP_DIR} -type f -mtime +${RETENTION_DAYS} -delete

echo "Limpeza de arquivos antigos concluída."

Explicação Técnica:

• -F c: Gera um arquivo no formato customizado do PostgreSQL. Este formato é preferível ao SQL puro porque permite restaurar objetos específicos e é mais eficiente para grandes volumes de dados.
• RETENTION_DAYS=7: Define a política de retenção. Ajuste conforme sua necessidade de conformidade (ex: 30 dias para auditorias).
• .pgpass: Para evitar que o script peça senha ou falhe, recomenda-se usar o arquivo ~/.pgpass do usuário postgres em vez de hardcodar a senha no script. Se optar pelo arquivo .pgpass, remova a linha export PGPASSWORD e crie o arquivo em /var/lib/postgresql/.pgpass com permissão 600.

Torne o script executável:

sudo chmod +x /usr/local/bin/backup_postgresql.sh

Passo 3: Testando o Script Manualmente

Antes de agendar, execute o script manualmente para verificar se não há erros de permissão ou sintaxe.

sudo -u postgres /usr/local/bin/backup_postgresql.sh

Verifique se o arquivo foi criado no diretório especificado:

ls -lh /var/backups/postgresql/daily/

Você deve ver um arquivo com nome como backup_20231027_140500.dump. Para testar a integridade, você pode tentar restaurar em um banco temporário:

createdb teste_restore
pg_restore -h localhost -U backup_user -d teste_restore /var/backups/postgresql/daily/backup_*.dump

Se o restore funcionar sem erros, seu script está pronto para automação.

Passo 4: Configurando o Cron Job

O Cron é o utilitário padrão do Unix para agendar tarefas. Vamos configurar um job que execute o backup diariamente durante a madrugada, quando a carga do servidor costuma ser menor.

Edite o crontab do usuário postgres (ou root, se preferir gerenciar centralmente, mas o usuário postgres garante contexto de acesso ao banco):

sudo -u postgres crontab -e

Adicione a seguinte linha ao final do arquivo:

# Backup automático do PostgreSQL todos os dias às 02:00
0 2 * * * /usr/local/bin/backup_postgresql.sh >> /var/log/postgresql_backup.log 2>&1

Entendendo a sintaxe do Cron:

• 0 2 * * *: Executa no dia 0 (segunda-feira) não se aplica aqui; são os campos: minuto (0), hora (2), dia do mês (*), mês (*), dia da semana (*). Ou seja, todos os dias às 02:00.
• >>: Redireciona a saída padrão (stdout).
• 2>&1: Redireciona a saída de erro (stderr) para o mesmo arquivo de log. Isso é crucial para diagnosticar falhas futuras.

Salve e saia do editor. O cron iniciará automaticamente a tarefa na próxima marcação do minuto.

Passo 5: Monitoramento e Logs

A automação sem monitoramento é perigosa. Um backup falhado não notificado pode significar a perda total de dados até o próximo dia útil.

Verifique os logs regularmente:

sudo tail -f /var/log/postgresql_backup.log

Se o script falhar, o erro aparecerá aqui. Para ambientes críticos, considere enviar um alerta por e-mail ou webhook (Slack/Discord) caso o retorno do script seja diferente de zero.

Exemplo simples de notificação por e-mail no script bash:

if [ $STATUS -ne 0 ]; then
    echo "Backup falhou em $(hostname)" | mail -s "Alerta Backup PostgreSQL" admin@seudominio.com
fi

Melhores Práticas para Recuperação de Desastres

Apenas fazer o backup não é suficiente. Você deve ter um plano de recuperação documentado.

1. Cópia Offsite: Nunca armazene backups apenas na mesma VPS onde está o banco. Se o servidor for comprometido ou sofrer falha física, os backups serão perdidos. Utilize ferramentas como rclone, wget ou scripts S3 para enviar os arquivos .dump para um armazenamento externo (AWS S3, Google Cloud Storage, Backblaze B2).
2. Testes de Restore: Realize testes de restauração mensais em um ambiente de staging. Um backup não testado é tão bom quanto inexistente.
3. Cifra dos Dados: Se os dados contêm informações sensíveis (LGPD/GDPR), considere criptografar o arquivo de dump. O pg_dump pode ser pipeado para gpg:
   

   pg_dump ... | gpg -c > backup.gpg

4. Versão do PostgreSQL: Mantenha o cliente pg_dump e o servidor na mesma versão ou versões compatíveis. O pg_dump de uma versão mais nova pode não funcionar em um banco antigo.

Conclusão

A automação de backups utilizando PostgreSQL, pg_dump e Cron é uma das tarefas mais importantes na administração de bancos de dados para VPS. Este método garante que você tenha pontos de restauração consistentes e controlados.

Ao seguir os passos deste tutorial — desde a criação de usuários dedicados até a configuração de rotinas de limpeza e monitoramento — você estabelece uma base sólida para a continuidade do negócio. Lembre-se: a segurança dos dados é um processo contínuo, não um evento único.

Para próximos níveis de maturidade, considere explorar o wal-g para backups incrementais e point-in-time recovery (PITR), ou soluções gerenciadas como Amazon RDS ou Azure Database for PostgreSQL, que abstraem essa complexidade operacionais.