Backup e restore com pg_dump e pg_restore

1. Introdução aos Conceitos de Backup no PostgreSQL

A integridade dos dados é um dos pilares mais críticos em qualquer sistema de banco de dados. Em ambientes PostgreSQL, a perda de dados pode ocorrer por falhas de hardware, erros humanos, corrupção lógica ou ataques cibernéticos. Por isso, dominar ferramentas de backup e restore é essencial para qualquer profissional de banco de dados.

O PostgreSQL oferece duas abordagens principais de backup:

  • Backup lógico (via pg_dump e pg_restore): exporta comandos SQL que recriam objetos e dados. É portável entre versões e arquiteturas, mas mais lento em bases muito grandes.
  • Backup físico (via pg_basebackup): copia os arquivos do banco no nível do sistema operacional. Ideal para bases enormes, mas menos flexível para restauração seletiva.

O ecossistema de backup lógico inclui três ferramentas principais:
- pg_dump: exporta um banco de dados específico
- pg_dumpall: exporta todos os bancos mais objetos globais (roles, tablespaces)
- pg_restore: restaura backups criados em formatos não-plain (custom, directory, tar)

2. pg_dump: Exportando Dados e Esquemas

A sintaxe básica do pg_dump é direta, mas seus parâmetros oferecem enorme flexibilidade:

pg_dump -h localhost -p 5432 -U usuario -d nome_banco > backup.sql

Parâmetros essenciais:
- -h ou --host: servidor do banco
- -p ou --port: porta de conexão
- -U ou --username: usuário do banco
- -d ou --dbname: nome do banco a ser exportado

Formatos de saída:

  1. Plain SQL (-F p): arquivo texto com comandos SQL. Pode ser restaurado com psql, mas não suporta restauração seletiva via pg_restore.

  2. Custom (-F c): formato binário comprimido (.dump). Permite restauração seletiva e paralela.

  3. Directory (-F d): cria um diretório com múltiplos arquivos. Excelente para paralelismo.

  4. Tar (-F t): similar ao custom, mas em formato tar.

Opções avançadas:

# Apenas esquema (sem dados)
pg_dump -U admin -d vendas --schema-only > esquema_vendas.sql

# Apenas dados (sem DDL)
pg_dump -U admin -d vendas --data-only > dados_vendas.sql

# Tabela específica
pg_dump -U admin -d vendas --table=clientes > clientes_backup.sql

# Excluir tabelas
pg_dump -U admin -d vendas --exclude-table=logs_auditoria > backup_parcial.sql

3. Estratégias de Backup com pg_dump

Backup completo:

pg_dump -U admin -d ecommerce -F c -f ecommerce_completo.dump

Backup seletivo de esquemas:

pg_dump -U admin -d erp --schema=vendas --schema=estoque -F c -f modulo_comercial.dump

Backup com compressão e paralelismo:

# Compressão nível 9 (máxima)
pg_dump -U admin -d analytics -F c -Z 9 -f analytics_comprimido.dump

# Paralelismo com formato directory (4 jobs)
pg_dump -U admin -d analytics -F d -j 4 -f /backup/analytics_paralelo/

O paralelismo (-j ou --jobs) acelera drasticamente backups de bases grandes, dividindo a exportação entre múltiplas tabelas simultaneamente. O formato directory é obrigatório para essa funcionalidade.

4. pg_restore: Restaurando a Partir de um Backup

O pg_restore é a ferramenta para restaurar backups nos formatos custom, directory e tar.

Restauração básica:

# Criar banco vazio primeiro
createdb -U admin ecommerce_restaurado

# Restaurar backup custom
pg_restore -U admin -d ecommerce_restaurado ecommerce_completo.dump

Restauração seletiva:

# Listar conteúdo do backup
pg_restore -l ecommerce_completo.dump > lista_itens.txt

# Restaurar apenas esquema específico
pg_restore -U admin -d ecommerce_restaurado --schema=vendas ecommerce_completo.dump

# Restaurar tabela específica
pg_restore -U admin -d ecommerce_restaurado --table=clientes ecommerce_completo.dump

Controle de concorrência:

# Restauração paralela (4 jobs)
pg_restore -U admin -d ecommerce_restaurado -j 4 ecommerce_completo.dump

# Restauração em transação única (tudo ou nada)
pg_restore -U admin -d ecommerce_restaurado --single-transaction ecommerce_completo.dump

A opção --single-transaction é crucial para manter a consistência: se algo falhar, todo o restore é revertido.

5. Backup e Restore com pg_dumpall

O pg_dumpall é a ferramenta para backup de objetos globais e múltiplos bancos:

# Backup completo de todos os bancos + objetos globais
pg_dumpall -U admin -f backup_completo.sql

# Backup apenas de objetos globais (roles, tablespaces)
pg_dumpall -U admin --globals-only > objetos_globais.sql

Quando usar pg_dumpall:
- Para migrar todo o cluster PostgreSQL
- Para preservar roles, permissões e tablespaces
- Em ambientes com múltiplos bancos pequenos

Limitações importantes:
- Saída apenas em formato plain SQL (não suporta formatos custom/directory)
- Não permite paralelismo
- Restauração lenta em bases muito grandes

Para ambientes de produção com bases grandes, a estratégia recomendada é:
1. pg_dumpall --globals-only para objetos globais
2. pg_dump individual para cada banco (em formato custom com paralelismo)

6. Boas Práticas e Automação

Script de backup automatizado com cron:

#!/bin/bash
# backup_postgres.sh

DATA=$(date +%Y%m%d_%H%M)
BACKUP_DIR="/backup/postgres"
DB_NAME="producao"

# Backup custom com compressão
pg_dump -U admin -d $DB_NAME -F c -Z 9 \
  -f "$BACKUP_DIR/${DB_NAME}_${DATA}.dump"

# Backup dos objetos globais (semanal)
if [ $(date +%u) -eq 7 ]; then
  pg_dumpall -U admin --globals-only \
    -f "$BACKUP_DIR/globals_${DATA}.sql"
fi

# Remover backups com mais de 30 dias
find $BACKUP_DIR -name "*.dump" -mtime +30 -delete

Verificação de integridade:

# Testar backup com pg_restore (modo list)
pg_restore -l backup.dump > /dev/null && echo "Backup íntegro" || echo "Backup corrompido"

# Restauração de teste em banco separado
createdb -U admin teste_restore
pg_restore -U admin -d teste_restore backup.dump
dropdb -U admin teste_restore

Estratégias de armazenamento:
- Mantenha backups em locais geograficamente distintos
- Utilize compressão (nível 5-9 para equilíbrio entre tamanho e performance)
- Implemente rotação automática (retenção de 7-30 dias conforme política)

7. Cenários de Recuperação e Troubleshooting

Restauração em caso de corrupção parcial:

# Restaurar ignorando erros de integridade referencial
pg_restore -U admin -d recuperado --disable-triggers \
  --data-only backup.dump

# Reaplicar constraints depois
pg_restore -U admin -d recuperado --schema-only backup.dump

Migração entre versões do PostgreSQL:

# pg_dump na versão antiga (9.6)
pg_dump -U admin -d antigo -F c -f migracao.dump

# pg_restore na versão nova (16)
pg_restore -U admin -d novo migracao.dump

O backup lógico é a única forma segura de migrar entre versões major do PostgreSQL.

Erros comuns e soluções:

Erro Causa Solução
permission denied Usuário sem privilégios Conceder permissões ou usar superusuário
version mismatch Versões incompatíveis Usar pg_dump da versão do servidor
encoding mismatch Conflito de charset Especificar --encoding=UTF8
role does not exist Role não existe no destino Criar role ou usar --no-owner 
# Solução para conflitos de encoding
pg_dump -U admin -d banco --encoding=UTF8 -f backup_utf8.sql

# Ignorar proprietários durante restore
pg_restore -U admin -d banco --no-owner backup.dump

O backup e restore com pg_dump e pg_restore são habilidades fundamentais para administradores de PostgreSQL. Dominar essas ferramentas garante não apenas a segurança dos dados, mas também a capacidade de realizar migrações, recuperações seletivas e automação de processos. Lembre-se: um backup que nunca é testado não é um backup — é apenas uma esperança.

Referências