Backup e Recuperação de Bancos de Dados PostgreSQL: Um Guia Completo para Usuários AlexHost

Por que a Estratégia de Backup do PostgreSQL é Mais Importante do que Você Pensa

A perda de dados não é um risco hipotético — é uma certeza operacional que todo administrador de banco de dados enfrentará em algum momento. Falhas de hardware, exclusões acidentais, transações corrompidas e ataques de ransomware podem derrubar um ambiente de produção em segundos. Para usuários do PostgreSQL, ter uma estratégia de backup robusta, testada e automatizada é a diferença entre um incidente menor e uma falha comercial catastrófica.

AlexHost Dedicated Servers fornecem uma base ideal para hospedar e proteger bancos de dados PostgreSQL. Com armazenamento NVMe SSD de nível empresarial oferecendo throughput de I/O excepcional, acesso root completo para controle total de configuração e proteção DDoS integrada, AlexHost oferece o desempenho de infraestrutura e postura de segurança que cargas de trabalho de banco de dados sérias exigem.

Quer você esteja executando uma plataforma de e-commerce de alto tráfego, uma aplicação SaaS, uma instalação WordPress apoiada por um banco de dados relacional ou um sistema empresarial personalizado, este guia o orienta através de todos os principais métodos de backup e recuperação do PostgreSQL — desde dumps SQL simples até Advanced Point-in-Time Recovery (PITR) — todos otimizados para ambientes de produção.

Índice

1. Compreendendo as Opções de Backup do PostgreSQL
2. Pré-requisitos e Requisitos de Privilégio
3. Método 1 — SQL Dump com pg_dump
4. Método 2 — Backup de Todos os Bancos de Dados com pg_dumpall
5. Método 3 — Backups em Formato Personalizado para Grandes Bancos de Dados
6. Restaurando a partir de SQL Dumps
7. Restaurando a partir de Dumps em Formato Personalizado
8. Método 4 — Arquivamento Contínuo e Point-in-Time Recovery (PITR)
9. Automatizando Backups com Cron
10. Protegendo e Armazenando Backups Fora do Local
11. Resumo das Melhores Práticas

1. Compreendendo as Opções de Backup do PostgreSQL

PostgreSQL vem com vários mecanismos de backup maduros e bem documentados. Escolher o correto depende do tamanho do seu banco de dados, objetivos de tempo de recuperação (RTO), objetivos de ponto de recuperação (RPO) e tolerância de complexidade operacional.

Compreender essas compensações antes de começar é essencial. A maioria dos ambientes de produção se beneficia da combinação de pelo menos duas abordagens — por exemplo, dumps em formato personalizado noturnos junto com arquivamento WAL contínuo para capacidade de recuperação granular.

2. Pré-requisitos e Requisitos de Privilégio

Antes de executar qualquer operação de backup, confirme que os seguintes pré-requisitos estão em vigor:

Privilégios do Usuário:

• Você deve ser um superusuário do PostgreSQL ou o proprietário do banco de dados de destino para realizar um backup completo.
• Para pg_dumpall, privilégios de superusuário são obrigatórios.

Verifique sua versão do PostgreSQL:

psql --version

Verifique o espaço em disco disponível antes de fazer backup:

df -h /var/lib/postgresql/

Certifique-se de que seu destino de backup tenha espaço livre suficiente — no mínimo 1,5× o tamanho do banco de dados sendo feito backup para contabilizar arquivos temporários e overhead de compressão.

Conecte-se ao seu servidor via SSH:

ssh root@your-server-ip

Se você estiver usando um plano de VPS Hosting, você terá acesso SSH completo e a capacidade de instalar, configurar e gerenciar PostgreSQL sem restrição.

3. Método 1 — SQL Dump com pg_dump

O utilitário pg_dump é a ferramenta de backup PostgreSQL mais comumente usada. Ele produz um snapshot consistente de um único banco de dados, mesmo enquanto o banco de dados está sendo usado ativamente. A saída é um script SQL em texto simples que pode ser revisado, editado e reproduzido em qualquer instalação PostgreSQL compatível.

Passo 1: Abra um Terminal e Acesse Seu Servidor

ssh root@your-alexhost-server-ip

Passo 2: Execute o Comando pg_dump

pg_dump -U username -W -F p database_name > /backups/backup_file.sql

Detalhamento de parâmetros:

Exemplo prático:

pg_dump -U postgres -W -F p my_production_db > /backups/my_production_db_$(date +%Y%m%d_%H%M%S).sql

> Dica Profissional: Anexar um timestamp usando $(date +%Y%m%d_%H%M%S) ao nome do arquivo de backup garante que você nunca sobrescreva acidentalmente um backup anterior e cria um arquivo cronológico natural.

Passo 3: Verifique o Arquivo de Backup

ls -lh /backups/
head -50 /backups/my_production_db_*.sql

O arquivo deve começar com comentários de cabeçalho PostgreSQL e instruções SET, confirmando que um dump válido foi criado.

4. Método 2 — Backup de Todos os Bancos de Dados com pg_dumpall

Quando você precisa fazer backup de todos os bancos de dados em uma instância PostgreSQL — incluindo objetos globais como funções e espaços de tabela — pg_dumpall é a ferramenta correta.

pg_dumpall -U postgres -W > /backups/all_databases_$(date +%Y%m%d).sql

Este comando exporta:

• Todos os bancos de dados
• Todas as funções (usuários e grupos)
• Todos os espaços de tabela
• Toda a configuração global

Importante: O arquivo de saída de pg_dumpall pode ser muito grande em servidores ocupados. Certifique-se de que sua partição de backup tenha espaço adequado e considere comprimir a saída imediatamente:

pg_dumpall -U postgres | gzip > /backups/all_databases_$(date +%Y%m%d).sql.gz

5. Método 3 — Backups em Formato Personalizado para Grandes Bancos de Dados

Para bancos de dados de produção excedendo alguns gigabytes, o formato personalizado (-F c) é fortemente recomendado sobre dumps SQL simples. Backups em formato personalizado são:

• Comprimidos por padrão — reduzindo significativamente os requisitos de armazenamento
• Mais rápidos de restaurar — suportando operações de restauração paralela com sinalizador -j
• Seletivamente restauráveis — permitindo restaurar tabelas ou esquemas individuais

Criando um Backup em Formato Personalizado

pg_dump -U postgres -W -F c my_production_db > /backups/my_production_db_$(date +%Y%m%d).dump

Criando um Backup em Formato de Diretório Comprimido (Suporta Paralelismo)

pg_dump -U postgres -W -F d -j 4 -f /backups/my_production_db_dir my_production_db

Esta abordagem reduz dramaticamente a duração do backup em servidores multi-core, tornando-a ideal para os ambientes de servidor dedicado de alto desempenho disponíveis na AlexHost.

6. Restaurando a partir de SQL Dumps

Restaurar um Único Banco de Dados a partir de um SQL Dump Simples

Primeiro, certifique-se de que o banco de dados de destino existe. Se não existir, crie-o:

psql -U postgres -c "CREATE DATABASE my_restored_db;"

Depois restaure:

psql -U postgres -d my_restored_db -f /backups/my_production_db_backup.sql

Detalhamento de parâmetros:

Monitorar Progresso de Restauração

Para arquivos SQL grandes, você pode monitorar o progresso usando pv:

pv /backups/my_production_db_backup.sql | psql -U postgres -d my_restored_db

7. Restaurando a partir de Dumps em Formato Personalizado

Dumps em formato personalizado requerem o utilitário pg_restore em vez de psql.

Restauração Básica

pg_restore -U postgres -d my_restored_db /backups/my_production_db.dump

Restaurar e Criar o Banco de Dados Automaticamente

Use o sinalizador -C para instruir pg_restore a criar o banco de dados antes de preenchê-lo:

pg_restore -U postgres -C -d postgres /backups/my_production_db.dump

Restauração Paralela para Recuperação Mais Rápida

pg_restore -U postgres -d my_restored_db -j 4 /backups/my_production_db_dir/

Usar -j 4 com um backup em formato de diretório pode reduzir o tempo de restauração em até 75% em um servidor quad-core — uma vantagem significativa ao minimizar o tempo de inatividade durante a recuperação de desastres.

Restaurar uma Tabela Específica Apenas

pg_restore -U postgres -d my_restored_db -t orders /backups/my_production_db.dump

Esta capacidade granular é uma das principais vantagens do formato personalizado sobre dumps SQL simples.

8. Método 4 — Arquivamento Contínuo e Point-in-Time Recovery (PITR)

PITR é o padrão ouro para implantações PostgreSQL críticas para a missão. Permite restaurar seu banco de dados para qualquer momento específico — não apenas o último backup — reproduzindo segmentos Write-Ahead Log (WAL) em cima de um backup base. Isso é essencial para cenários onde você precisa recuperar de um erro lógico (como um DROP TABLE acidental) que ocorreu em um timestamp conhecido.

Passo 1: Ativar Arquivamento WAL em postgresql.conf

Localize e edite seu arquivo de configuração PostgreSQL:

nano /etc/postgresql/15/main/postgresql.conf

Adicione ou modifique as seguintes diretivas:

# Enable WAL archiving
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Explicação de parâmetros:

Crie o diretório de arquivo e defina permissões corretas:

mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

Reinicie PostgreSQL para aplicar alterações:

systemctl restart postgresql

Passo 2: Faça um Backup Base com pg_basebackup

pg_basebackup -U postgres -D /backups/base_backup -Ft -z -P -Xs

Passo 3: Restaurar para um Ponto Específico no Tempo

Para restaurar a partir de um backup base e arquivos WAL:

1. Pare PostgreSQL:

systemctl stop postgresql

1. Limpe o diretório de dados existente:

rm -rf /var/lib/postgresql/15/main/*

1. Extraia o backup base:

tar -xzf /backups/base_backup/base.tar.gz -C /var/lib/postgresql/15/main/

1. Crie um recovery.conf (PostgreSQL 11 e anteriores) ou configure postgresql.conf e crie um arquivo recovery.signal (PostgreSQL 12+):

# For PostgreSQL 12+
touch /var/lib/postgresql/15/main/recovery.signal

Adicione a postgresql.conf:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2025-01-15 14:30:00'
recovery_target_action = 'promote'

1. Defina propriedade correta e inicie PostgreSQL:

chown -R postgres:postgres /var/lib/postgresql/15/main/
systemctl start postgresql

PostgreSQL reproduzirá segmentos WAL até o timestamp especificado e depois promoverá para um estado normal de leitura-escrita.

9. Automatizando Backups com Cron

Backups manuais são não confiáveis. Automatizar seu cronograma de backup com cron garante consistência e remove o fator de erro humano.

Criar um Script de Backup

nano /usr/local/bin/pg_backup.sh

#!/bin/bash

# PostgreSQL Automated Backup Script
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="my_production_db"
RETENTION_DAYS=14

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Perform the backup
pg_dump -U "$DB_USER" -F c "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.dump"

# Remove backups older than retention period
find "$BACKUP_DIR" -name "*.dump" -mtime +"$RETENTION_DAYS" -delete

# Log completion
echo "[$TIMESTAMP] Backup of $DB_NAME completed successfully." >> /var/log/pg_backup.log

Torne o script executável:

chmod +x /usr/local/bin/pg_backup.sh

Agendar com Cron

crontab -e

Adicione a seguinte linha para executar um backup todas as noites às 2:00 AM:

0 2 * * * /usr/local/bin/pg_backup.sh

Para backups completos semanais mais arquivamento WAL incremental diário, combine isso com a configuração PITR descrita na seção anterior.

10. Protegendo e Armazenando Backups Fora do Local

Um backup armazenado no mesmo servidor que seu banco de dados de produção não é um backup real — é um ponto único de falha. Implemente as seguintes práticas de segurança e armazenamento fora do local:

Criptografar Backups Antes da Transferência

gpg --symmetric --cipher-algo AES256 /backups/my_production_db.dump

Transferir Backups para um Local Remoto com rsync

rsync -avz --progress /backups/postgresql/ user@remote-backup-server:/remote/backups/postgresql/

Usar pg_dump com Pipe SSH para Backup Remoto Direto

pg_dump -U postgres my_production_db | gzip | ssh user@remote-server "cat > /backups/my_production_db_$(date +%Y%m%d).sql.gz"

Regras de Firewall para PostgreSQL (UFW)

Restrinja o acesso à porta PostgreSQL apenas a IPs confiáveis:

ufw allow from 192.168.1.0/24 to any port 5432
ufw deny 5432
ufw enable

Para equipes gerenciando múltiplos projetos em diferentes níveis de hospedagem, planos de AlexHost Shared Web Hosting também suportam ferramentas de gerenciamento de banco de dados que podem complementar seus fluxos de trabalho de backup para projetos menores.

11. Resumo das Melhores Práticas

Implementar backups PostgreSQL corretamente