Backup e restauração no PostgreSQL é a base de qualquer plano de recuperação de desastres em banco de dados. Seja protegendo um único banco de dados de aplicação ou gerenciando centenas de instâncias, dominar pg_dump e pg_restore garante que você possa se recuperar de falhas de hardware, exclusões acidentais e migrações mal executadas. Este guia cobre todos os aspectos práticos — desde dumps básicos até agendamento automatizado, restaurações seletivas e a etapa crítica que a maioria das equipes ignora: testar de verdade se os backups podem ser restaurados.
Pré-requisitos
- PostgreSQL 12 ou superior instalado e em execução
- Acesso a um banco de dados PostgreSQL com privilégios suficientes (
pg_dumpexige pelo menos acesso de leitura a todos os objetos) - Acesso
sudoou ao usuáriopostgresno servidor de banco de dados - Familiaridade básica com SQL e conceitos do PostgreSQL (bancos de dados, esquemas, tabelas)
Criando Backups com pg_dump
pg_dump produz backups lógicos — uma representação do banco de dados como instruções SQL ou um arquivo comprimido. Ao contrário de cópias no nível do sistema de arquivos, esses backups são portáveis entre versões e plataformas do PostgreSQL.
Dump SQL Simples (Padrão)
O backup mais simples cria um arquivo .sql contendo todos os comandos SQL necessários para recriar o banco de dados:
# Dump de um único banco de dados para um arquivo SQL
pg_dump -U postgres -h localhost mydb > mydb_backup.sql
# Incluir instrução CREATE DATABASE
pg_dump -U postgres -h localhost -C mydb > mydb_backup_with_create.sql
# Dump com timestamp no nome do arquivo
pg_dump -U postgres mydb > "mydb_$(date +%Y%m%d_%H%M%S).sql"O formato SQL simples é legível por humanos e pode ser restaurado com psql. A desvantagem: não suporta restauração paralela nem restauração seletiva de tabelas.
Formato Customizado (-Fc) — Recomendado para Produção
O formato customizado cria um arquivo comprimido não textual que suporta as opções de restauração mais flexíveis:
# Backup em formato customizado (comprimido por padrão)
pg_dump -U postgres -Fc mydb > mydb_backup.dump
# Formato customizado com compressão máxima
pg_dump -U postgres -Fc -Z 9 mydb > mydb_backup.dump
# Formato customizado com dump paralelo (4 jobs)
pg_dump -U postgres -Fc -j 4 mydb > mydb_backup.dumpPor que o formato customizado é preferido:
- Comprimido automaticamente (tipicamente 5-10x menor que SQL simples)
- Suporta restauração paralela com
pg_restore -j - Permite restauração seletiva (tabelas, esquemas ou apenas dados específicos)
- Pode reordenar itens durante a restauração para otimizar a velocidade de carga
Formato Diretório (-Fd) — Melhor para Bancos de Dados Grandes
O formato diretório cria um diretório com um arquivo por tabela, permitindo dumps verdadeiramente paralelos:
# Formato diretório com dump paralelo (8 workers)
pg_dump -U postgres -Fd -j 8 -f /backup/mydb_dir mydbIsso cria um diretório contendo um arquivo toc.dat (índice de conteúdo) e um arquivo comprimido por tabela. O dump paralelo reduz significativamente o tempo de backup para bancos de dados grandes.
Fazendo Dump de Todos os Bancos (pg_dumpall)
Para fazer backup de todos os bancos de dados no cluster PostgreSQL, além dos objetos globais (roles, tablespaces):
# Dump de todos os bancos de dados e objetos globais
pg_dumpall -U postgres > all_databases.sql
# Dump apenas dos objetos globais (roles, tablespaces)
pg_dumpall -U postgres --globals-only > globals.sqlpg_dumpall sempre produz formato SQL simples. Para clusters grandes, faça dump dos globals separadamente e use pg_dump por banco de dados em formato customizado.
Dumps Seletivos
# Dump de uma única tabela
pg_dump -U postgres -t users mydb > users_table.sql
# Dump de múltiplas tabelas específicas
pg_dump -U postgres -t users -t orders -t products mydb > selected_tables.sql
# Dump de um esquema específico
pg_dump -U postgres -n public mydb > public_schema.sql
# Dump apenas do esquema (sem dados)
pg_dump -U postgres -s mydb > schema_only.sql
# Dump apenas dos dados (sem esquema)
pg_dump -U postgres -a mydb > data_only.sql
# Excluir uma tabela grande do dump
pg_dump -U postgres -T audit_log mydb > mydb_no_audit.sqlRestaurando Backups
Restaurar a partir de SQL Simples
# Restaurar em um banco de dados existente
psql -U postgres -h localhost mydb < mydb_backup.sql
# Criar o banco de dados e restaurar (se o dump inclui a flag -C)
psql -U postgres -h localhost < mydb_backup_with_create.sql
# Restaurar com saída detalhada
psql -U postgres -v ON_ERROR_STOP=1 mydb < mydb_backup.sqlA flag ON_ERROR_STOP=1 faz o psql parar no primeiro erro em vez de continuar silenciosamente. Sempre use isso ao restaurar bancos de dados de produção.
Restaurar a partir do Formato Customizado
# Restauração básica
pg_restore -U postgres -d mydb mydb_backup.dump
# Restauração paralela (8 jobs — dramaticamente mais rápida para bancos grandes)
pg_restore -U postgres -d mydb -j 8 mydb_backup.dump
# Restaurar em um novo banco de dados
createdb -U postgres mydb_restored
pg_restore -U postgres -d mydb_restored mydb_backup.dump
# Limpar (apagar) objetos existentes antes da restauração
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dumpRestaurar Tabelas Específicas
# Restaurar uma única tabela de um dump em formato customizado
pg_restore -U postgres -d mydb -t users mydb_backup.dump
# Restaurar usando um arquivo de lista (controle granular)
pg_restore -l mydb_backup.dump > restore_list.txt
# Edite restore_list.txt — comente os itens que não deseja restaurar
pg_restore -U postgres -d mydb -L restore_list.txt mydb_backup.dumpRestaurar Esquema Específico
# Restaurar apenas o esquema 'public'
pg_restore -U postgres -d mydb -n public mydb_backup.dump
# Restaurar apenas dados (esquema já existe)
pg_restore -U postgres -d mydb -a mydb_backup.dumpComparação dos Métodos de Backup no PostgreSQL
| Característica | pg_dump (Lógico) | pg_basebackup (Físico) | Snapshot de Sistema de Arquivos |
|---|---|---|---|
| Escopo do backup | Banco único | Cluster completo | Cluster completo |
| Restauração entre versões | Sim | Não (mesma versão principal) | Não |
| Restauração seletiva de tabela | Sim (flag -t) | Não | Não |
| Recuperação point-in-time | Não | Sim (com WAL) | Sim (com WAL) |
| Backup com sistema em execução | Sim (snapshot consistente) | Sim | Requer fsync/freeze |
| Tamanho do backup | Menor (dados comprimidos) | Maior (diretório de dados completo) | Maior (disco completo) |
| Velocidade do backup | Mais lento (leitura via SQL) | Mais rápido (streaming) | Mais rápido (nível de bloco) |
| Melhor para | Bancos individuais, migrações | DR completo do cluster, PITR | Snapshots de VM/cloud |
Use pg_dump para backups diários de bancos de dados individuais e para migrações entre versões do PostgreSQL. Use pg_basebackup quando precisar de recuperação point-in-time ou estiver configurando replicação em streaming. Use snapshots de sistema de arquivos como complemento, não substituto — são rápidos, mas exigem tratamento cuidadoso dos arquivos WAL.
Automatizando Backups com Cron
Script de Backup
#!/bin/bash
# /usr/local/bin/pg_backup.sh
# Automated PostgreSQL backup script
set -euo pipefail
# Configuration
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=30
PG_USER="postgres"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/pg_backup.log"
# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"
# Log function
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}
log "Starting PostgreSQL backup"
# Dump globals (roles, tablespaces)
pg_dumpall -U "$PG_USER" --globals-only > "$BACKUP_DIR/globals_${TIMESTAMP}.sql" 2>> "$LOG_FILE"
log "Globals dumped"
# Dump each database in custom format
for DB in $(psql -U "$PG_USER" -At -c "SELECT datname FROM pg_database WHERE datistemplate = false AND datname != 'postgres'"); do
DUMP_FILE="$BACKUP_DIR/${DB}_${TIMESTAMP}.dump"
pg_dump -U "$PG_USER" -Fc -Z 6 "$DB" > "$DUMP_FILE" 2>> "$LOG_FILE"
SIZE=$(du -h "$DUMP_FILE" | cut -f1)
log "Dumped $DB ($SIZE)"
done
# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +${RETENTION_DAYS} -delete
find "$BACKUP_DIR" -name "globals_*.sql" -mtime +${RETENTION_DAYS} -delete
log "Cleaned up backups older than $RETENTION_DAYS days"
log "Backup complete"# Tornar executável
chmod +x /usr/local/bin/pg_backup.sh
# Testar manualmente primeiro
sudo -u postgres /usr/local/bin/pg_backup.shAgendamento com Cron
# Editar o crontab do usuário postgres
sudo crontab -u postgres -e# Backup diário às 2:00 AM
0 2 * * * /usr/local/bin/pg_backup.sh
# Backup completo semanal no domingo à 1:00 AM (manter por mais tempo)
0 1 * * 0 /usr/local/bin/pg_backup.shAutenticação Sem Senha com .pgpass
Para jobs cron, é necessário autenticação sem senha. Crie um arquivo .pgpass:
# Criar .pgpass para o usuário postgres
sudo -u postgres bash -c 'cat > ~/.pgpass << EOF
localhost:5432:*:postgres:your_secure_password
EOF'
# Definir permissões necessárias (pg_dump recusa usar um .pgpass legível por todos)
sudo -u postgres chmod 600 ~/.pgpassFormato: hostname:porta:banco:usuario:senha. Use * como coringa para o banco de dados para cobrir todos os bancos.
Testando a Integridade do Backup
Um backup que não pode ser restaurado não é um backup. Agende testes regulares de restauração:
#!/bin/bash
# /usr/local/bin/pg_restore_test.sh
# Verify backup integrity by restoring to a temporary database
set -euo pipefail
BACKUP_FILE="$1"
TEST_DB="restore_test_$(date +%s)"
PG_USER="postgres"
# Create temporary database
createdb -U "$PG_USER" "$TEST_DB"
# Attempt restore
if pg_restore -U "$PG_USER" -d "$TEST_DB" "$BACKUP_FILE" 2>/dev/null; then
# Verify data
TABLES=$(psql -U "$PG_USER" -At -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'" "$TEST_DB")
ROWS=$(psql -U "$PG_USER" -At -c "SELECT sum(n_live_tup) FROM pg_stat_user_tables" "$TEST_DB")
echo "PASS: Restored $TABLES tables with $ROWS total rows"
else
echo "FAIL: Restore encountered errors"
fi
# Clean up
dropdb -U "$PG_USER" "$TEST_DB"# Testar o backup mais recente
sudo -u postgres /usr/local/bin/pg_restore_test.sh /var/backups/postgresql/mydb_latest.dumpSolução de Problemas Comuns
”pg_dump: error: connection to server failed"
# Verificar se o PostgreSQL está em execução
sudo systemctl status postgresql
# Verificar se você consegue conectar
psql -U postgres -h localhost -l
# Verificar pg_hba.conf para regras de autenticação
sudo cat /etc/postgresql/16/main/pg_hba.conf | grep -v '^#' | grep -v '^$'"pg_restore: error: could not execute query: ERROR: relation already exists”
O banco de dados de destino já possui objetos. Use --clean --if-exists para apagar os objetos existentes antes da restauração:
pg_restore -U postgres -d mydb --clean --if-exists mydb_backup.dumpOu restaure em um banco de dados novo e vazio:
dropdb -U postgres mydb
createdb -U postgres mydb
pg_restore -U postgres -d mydb mydb_backup.dumpBackup muito lento
# Usar dump paralelo (somente para formato customizado ou diretório)
pg_dump -U postgres -Fd -j $(nproc) -f /backup/mydb_dir mydb
# Excluir tabelas grandes que podem ser regeneradas
pg_dump -U postgres -Fc -T large_cache_table -T session_data mydb > mydb.dump
# Comprimir menos agressivamente para ganhar velocidade
pg_dump -U postgres -Fc -Z 1 mydb > mydb_fast.dumpRestauração muito lenta
# Usar restauração paralela (formato customizado ou diretório)
pg_restore -U postgres -d mydb -j $(nproc) mydb_backup.dump
# Desabilitar triggers durante a carga de dados (acelera significativamente)
pg_restore -U postgres -d mydb --disable-triggers mydb_backup.dump
# Aumentar maintenance_work_mem para a sessão de restauração
psql -U postgres -d mydb -c "SET maintenance_work_mem = '1GB';"
pg_restore -U postgres -d mydb mydb_backup.dumpArquivo .pgpass ignorado
# Verificar permissões (deve ser 600)
ls -la ~/.pgpass
# Corrigir permissões
chmod 600 ~/.pgpass
# Verificar formato (sem espaços extras)
cat ~/.pgpass
# Deve ser: hostname:porta:banco:usuario:senhaCasos Especiais e Armadilhas
Objetos grandes (BLOBs): pg_dump inclui objetos grandes por padrão, mas pg_restore -t NÃO os restaura. Use pg_restore -L com uma lista customizada para incluir objetos grandes em restaurações seletivas.
Extensões: pg_dump inclui instruções CREATE EXTENSION, mas a restauração falha se a extensão não estiver instalada no servidor de destino. Instale as extensões antes de restaurar.
Propriedade e permissões: pg_dump registra a propriedade dos objetos. Se o role proprietário não existir no servidor de destino, a restauração falha. Use --no-owner para ignorar atribuições de propriedade, ou crie os roles primeiro usando o dump de globals.
Acesso concorrente durante o dump: pg_dump tira um snapshot no início e lê consistentemente a partir dele. Outras transações continuam normalmente. Porém, operações DDL (ALTER TABLE, DROP) que mantêm AccessExclusiveLock bloquearão o dump. Agende dumps em períodos de baixa atividade.
Incompatibilidade de encoding: Se os bancos de dados de origem e destino usam encodings diferentes, pode ocorrer corrupção de dados. Sempre verifique se os encodings coincidem:
psql -U postgres -c "SHOW server_encoding;"Resumo
- pg_dump com formato customizado (-Fc) é a abordagem recomendada para backups de produção — comprime os dados, suporta restauração paralela e permite recuperação seletiva de tabelas
- pg_dumpall captura objetos globais (roles, tablespaces) que pg_dump não inclui — sempre faça dump dos globals separadamente junto com seus backups de banco de dados
- Automatize com cron e um script de backup que inclua rotação, log e tratamento de erros — nunca dependa de procedimentos manuais de backup
- Teste suas restaurações regularmente restaurando em um banco de dados temporário e validando contagens de linhas — um backup que você nunca testou é um backup que pode falhar quando você precisar
- Use .pgpass para autenticação sem senha em jobs cron com permissões
chmod 600— pg_dump recusa ler arquivos de credenciais legíveis por todos - Dump e restauração paralelos (flag
-j) reduzem drasticamente o tempo para bancos de dados grandes — use o formato diretório (-Fd) para o melhor desempenho paralelo