Backup físico e PITR do PostgreSQL no Ubuntu: script completo de pg_basebackup

O backup físico responde à pergunta que um dump lógico não consegue: restaure o cluster inteiro para as 16:42, um minuto antes do deploy que corrompeu os dados. Essa capacidade não vem do pg_basebackup sozinho — vem de um base backup somado a uma cadeia ininterrupta de WAL arquivado. Este guia configura os dois no Ubuntu, entrega um script testado e conduz um point-in-time recovery real.

O que é backup físico de verdade

Enquanto o pg_dump pede ao servidor o conteúdo lógico de um banco, o pg_basebackup copia os arquivos do cluster — todo o diretório de dados, todos os bancos de uma vez, exatamente como estão em disco. Isso traz consequências que vale internalizar antes de construir qualquer coisa em cima:

• Ele restaura o cluster inteiro, nunca uma tabela isolada. Não dá para recuperar uma tabela derrubada a partir de um base backup sem antes restaurar tudo em outro lugar.
• Só restaura na mesma versão major do PostgreSQL, em arquitetura compatível. Não é ferramenta de migração.
• Restaurar é rápido, porque é cópia de arquivo. Não há linhas a reinserir nem índices a reconstruir — o custo dominante de um restore lógico grande.
• Combinado com WAL arquivado, permite point-in-time recovery, que nenhum dump oferece.

Para a estratégia complementar — recuperação por tabela, portabilidade entre versões — veja backup lógico do PostgreSQL no Ubuntu com pg_dump. Manter os dois é normal e correto.

O modelo mental: uma base mais uma cadeia

O base backup é um ponto de partida consistente. O WAL (write-ahead log) é o registro ordenado de cada mudança feita pelo servidor depois dele. A recuperação reproduz esse registro para a frente e para onde você mandar.

Duas implicações decorrem direto disso, e é nelas que os ambientes reais falham:

Passo 1: Habilitar o arquivamento de WAL no servidor

Sem isso, um base backup só restaura até o instante em que terminou. Configure o arquivamento antes de gerar o backup no qual você pretende confiar. Nos pacotes Ubuntu, edite /etc/postgresql/16/main/postgresql.conf:

wal_level = replica              # mínimo para backup físico e replicação
archive_mode = on                # exige restart para mudar
archive_timeout = 300            # força troca de segmento a cada 5 minutos

# Precisa retornar diferente de zero em falha e jamais sobrescrever arquivo existente.
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'

max_wal_senders = 10             # o pg_basebackup precisa de pelo menos um

sudo install -d -o postgres -g postgres -m 700 /var/lib/postgresql/wal_archive
sudo systemctl restart postgresql@16-main.service

# Confirme que valeu e que o arquivamento realmente está funcionando
sudo -u postgres psql -XAtqc "SHOW archive_mode"
sudo -u postgres psql -X -c "SELECT * FROM pg_stat_archiver;"

Leia o pg_stat_archiver com atenção: archived_count deve subir e failed_count deve permanecer em zero. Um failed_count crescendo significa WAL se acumulando em pg_wal, que uma hora enche o filesystem e para o banco.

O exemplo com cp acima é a ilustração da documentação e serve para um primeiro setup, mas note os limites: não faz fsync e um diretório local morre junto com o servidor. Em produção, arquive para outro host ou object storage — rsync por SSH, aws s3 cp, ou uma ferramenta dedicada como pgBackRest ou Barman, que tratam compressão, paralelismo e a retenção do par base/WAL de forma conjunta.

Passo 2: O script de base backup

O script repete as proteções do backup lógico — lock, diretório de staging, promoção atômica, verificação, retenção só após sucesso — e acrescenta uma específica do backup físico.

Essa adição é o replication slot. O pg_basebackup --wal-method=stream abre uma segunda conexão para receber o WAL gerado enquanto a cópia roda. Em um servidor movimentado, uma cópia longa pode ultrapassar a retenção de WAL e o servidor reciclar um segmento que o backup ainda precisa, o que faz o backup falhar já no fim. O slot manda o servidor segurar esse WAL. O outro lado é a falha que isso cria: um slot deixado para trás por uma execução que morreu faz o servidor reter WAL para sempre, até o pg_wal encher o disco e o primário parar. Por isso o script derruba o slot em todos os caminhos de saída.

Salve como postgresql-basebackup-ubuntu.sh ou baixe o arquivo pronto:

#!/usr/bin/env bash
#
# Backup físico do PostgreSQL no Ubuntu com pg_basebackup.
#
# Um base backup sozinho restaura até o momento em que terminou. Para
# point-in-time recovery também é preciso arquivamento contínuo de WAL.
#
# Uso:
#   sudo -u postgres ./postgresql-basebackup-ubuntu.sh
#   sudo -u postgres BACKUP_ROOT=/backup/base RETENTION_DAYS=14 ./postgresql-basebackup-ubuntu.sh
#
set -Eeuo pipefail

BACKUP_ROOT="${BACKUP_ROOT:-/var/backups/postgresql-base}"
RETENTION_DAYS="${RETENTION_DAYS:-7}"
SLOT_NAME="${SLOT_NAME:-pg_basebackup_script}"
COMPRESS_LEVEL="${COMPRESS_LEVEL:-6}"
MAX_RATE="${MAX_RATE:-}"
LOCK_FILE="${LOCK_FILE:-/var/lock/postgresql-basebackup.lock}"

log() {
  echo "[$(date --iso-8601=seconds)] $*"
}

fail() {
  echo "ERRO: $*" >&2
  exit 1
}

[[ "$RETENTION_DAYS" =~ ^[0-9]+$ ]] || fail "RETENTION_DAYS deve ser inteiro."
[[ "$COMPRESS_LEVEL" =~ ^[1-9]$ ]] || fail "COMPRESS_LEVEL deve estar entre 1 e 9."
[[ "$SLOT_NAME" =~ ^[a-z0-9_]+$ ]] || fail "SLOT_NAME deve casar com ^[a-z0-9_]+$."

for command_name in psql pg_basebackup flock; do
  command -v "$command_name" >/dev/null || fail "Comando ausente: $command_name"
done

exec 9>"$LOCK_FILE" || fail "Não foi possível abrir o lock $LOCK_FILE"
flock -n 9 || fail "Já existe um base backup em andamento."

SERVER_VERSION_NUM="$(psql -XAtqc 'SHOW server_version_num' postgres)" ||
  fail "Não foi possível conectar. A role precisa do atributo REPLICATION."
SERVER_VERSION="$(psql -XAtqc 'SHOW server_version' postgres)"

# O pg_basebackup abre uma segunda conexão para transmitir WAL, então o servidor
# precisa de pelo menos um walsender livre além das réplicas já conectadas.
WAL_SENDERS="$(psql -XAtqc 'SHOW max_wal_senders' postgres)"
(( WAL_SENDERS >= 1 )) || fail "max_wal_senders é 0; o pg_basebackup não consegue transmitir WAL."

ARCHIVE_MODE="$(psql -XAtqc 'SHOW archive_mode' postgres)"
if [[ "$ARCHIVE_MODE" != "on" ]]; then
  log "AVISO: archive_mode está '$ARCHIVE_MODE'. Este backup restaura apenas até"
  log "AVISO: o próprio ponto final. PITR exige arquivamento de WAL."
fi

TIMESTAMP="$(date +%Y%m%dT%H%M%S)"
STAGING_DIR="${BACKUP_ROOT}/.in-progress-${TIMESTAMP}"
FINAL_DIR="${BACKUP_ROOT}/${TIMESTAMP}"

install -d -m 700 "$BACKUP_ROOT"
install -d -m 700 "$STAGING_DIR"

cleanup_staging() {
  local exit_code=$?
  if (( exit_code != 0 )); then
    [[ -d "$STAGING_DIR" ]] && { log "Execução falhou; removendo $STAGING_DIR"; rm -rf -- "$STAGING_DIR"; }
    # Um slot deixado para trás após falha faz o servidor reter WAL para sempre,
    # o que acaba enchendo o pg_wal e parando o primário.
    psql -XAtqc "SELECT pg_drop_replication_slot('${SLOT_NAME}')
                   WHERE EXISTS (SELECT 1 FROM pg_replication_slots
                                  WHERE slot_name = '${SLOT_NAME}')" postgres >/dev/null 2>&1 || true
  fi
  exit "$exit_code"
}
trap cleanup_staging EXIT

# Um slot órfão de uma execução anterior faria o --create-slot falhar.
psql -XAtqc "SELECT pg_drop_replication_slot('${SLOT_NAME}')
               WHERE EXISTS (SELECT 1 FROM pg_replication_slots
                              WHERE slot_name = '${SLOT_NAME}' AND active IS NOT TRUE)"   postgres >/dev/null

log "PostgreSQL $SERVER_VERSION"
log "Destino: $FINAL_DIR"
log "Iniciando pg_basebackup (isto lê o cluster inteiro)"

BASEBACKUP_ARGS=(
  --pgdata="$STAGING_DIR"
  --format=tar
  --gzip
  --compress="$COMPRESS_LEVEL"
  --wal-method=stream          # leva junto o WAL gerado durante a cópia
  --checkpoint=fast            # não espera o próximo checkpoint agendado
  --create-slot
  --slot="$SLOT_NAME"
  --progress
  --verbose
  --no-password
)
[[ -n "$MAX_RATE" ]] && BASEBACKUP_ARGS+=( --max-rate="$MAX_RATE" )

pg_basebackup "${BASEBACKUP_ARGS[@]}"

# Em caso de sucesso o próprio pg_basebackup derruba o slot; isto cobre apenas
# a janela em que o servidor o manteve após uma desconexão suja.
psql -XAtqc "SELECT pg_drop_replication_slot('${SLOT_NAME}')
               WHERE EXISTS (SELECT 1 FROM pg_replication_slots
                              WHERE slot_name = '${SLOT_NAME}' AND active IS NOT TRUE)"   postgres >/dev/null

# O backup_manifest existe do PostgreSQL 13 em diante; o pg_verifybackup lê o
# conjunto em tar direto a partir do 17, então versões anteriores só conferem arquivos.
if [[ -f "${STAGING_DIR}/backup_manifest" ]]; then
  if command -v pg_verifybackup >/dev/null && (( SERVER_VERSION_NUM >= 170000 )); then
    log "Verificando o manifesto com pg_verifybackup"
    pg_verifybackup --format=tar "$STAGING_DIR" || fail "pg_verifybackup acusou backup corrompido."
  else
    log "backup_manifest presente; verifique após a extração com pg_verifybackup"
  fi
else
  log "Sem backup_manifest (PostgreSQL < 13); pulando verificação do manifesto."
fi

[[ -f "${STAGING_DIR}/base.tar.gz" ]] || fail "base.tar.gz não está no backup."
gzip --test "${STAGING_DIR}"/*.tar.gz || fail "Um arquivo comprimido falhou no teste de integridade."

(cd "$STAGING_DIR" && sha256sum ./*.tar.gz > SHA256SUMS)

cat > "${STAGING_DIR}/MANIFEST.txt" <<MANIFEST
backup_type      physical (pg_basebackup, tar + gzip)
started_at       ${TIMESTAMP}
finished_at      $(date --iso-8601=seconds)
server_version   ${SERVER_VERSION}
archive_mode     ${ARCHIVE_MODE}
host             $(hostname -f 2>/dev/null || hostname)
restore          pare o postgres, esvazie o PGDATA, extraia base.tar.gz nele,
                 extraia pg_wal.tar.gz em PGDATA/pg_wal e inicie o servidor
pitr             adicione restore_command e recovery_target_time no postgresql.conf
                 e crie o arquivo PGDATA/recovery.signal antes de iniciar
MANIFEST

chmod -R go-rwx "$STAGING_DIR"
mv -- "$STAGING_DIR" "$FINAL_DIR"
trap - EXIT

if (( RETENTION_DAYS > 0 )); then
  log "Aplicando retenção: removendo conjuntos com mais de ${RETENTION_DAYS} dias"
  find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d     -name '20*T*' -mtime "+${RETENTION_DAYS}"     -print -exec rm -rf -- {} +
fi

log "Base backup concluído: $FINAL_DIR"
log "Tamanho total: $(du -sh "$FINAL_DIR" | cut -f1)"
log "Lembrete: WAL mais antigo que o base backup mais velho retido pode ser reciclado;"
log "mantenha o WAL arquivado pelo menos enquanto durarem os base backups que ele cobre."

Passo 3: Executar e agendar

sudo install -m 750 -o postgres -g postgres   postgresql-basebackup-ubuntu.sh /usr/local/bin/

sudo -u postgres /usr/local/bin/postgresql-basebackup-ubuntu.sh

O resultado é um conjunto pequeno e autoexplicativo:

/var/backups/postgresql-base/20260804T020001/
├── base.tar.gz        # o diretório de dados
├── pg_wal.tar.gz      # WAL gerado durante a cópia
├── backup_manifest    # checksums por arquivo (PostgreSQL 13+)
├── MANIFEST.txt       # versões, archive_mode, instruções de restore
└── SHA256SUMS

Base backups costumam ser semanais, com o arquivamento de WAL cobrindo continuamente os intervalos, porque copiar o cluster inteiro é caro:

sudo crontab -u postgres -e

# Base backup semanal, domingo à 01:00. Com limite de banda para não
# saturar o storage durante a cópia.
MAILTO=dba@exemplo.com
0 1 * * 0 MAX_RATE=100M RETENTION_DAYS=30 /usr/local/bin/postgresql-basebackup-ubuntu.sh   >> /var/log/postgresql/basebackup.log 2>&1

Passo 4: Point-in-time recovery, passo a passo

É para isto que todo o resto existe. O cenário: às 16:43 uma migração apagou linhas que não devia, e você precisa do cluster como estava às 16:42.

1. Pare o PostgreSQL e reserve o diretório de dados antigo.

sudo systemctl stop postgresql@16-main.service
sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.quebrado
sudo install -d -o postgres -g postgres -m 700 /var/lib/postgresql/16/main

2. Extraia o base backup. Os dois arquivos vão para lugares diferentes: base.tar.gz no PGDATA e pg_wal.tar.gz em PGDATA/pg_wal.

cd /var/backups/postgresql-base/20260804T020001
sha256sum --check SHA256SUMS

sudo -u postgres tar -xzf base.tar.gz -C /var/lib/postgresql/16/main
sudo -u postgres tar -xzf pg_wal.tar.gz -C /var/lib/postgresql/16/main/pg_wal

3. Diga à recuperação onde achar o WAL e onde parar. Desde o PostgreSQL 12 esses são parâmetros normais do postgresql.conf; o antigo arquivo recovery.conf não existe mais.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-08-05 16:42:00-03'
recovery_target_action = 'pause'

O recovery_target_action = 'pause' é o importante. O servidor chega ao alvo e espera em vez de se promover, o que permite conectar em modo leitura e confirmar que os dados são o esperado antes de assumir aquele ponto. Os demais alvos disponíveis são recovery_target_lsn, recovery_target_xid e recovery_target_name (definido antes com pg_create_restore_point()).

4. Crie o arquivo de sinalização e inicie. É a presença dele que coloca o servidor em archive recovery.

sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal
sudo systemctl start postgresql@16-main.service

# Acompanhe a recuperação reproduzindo a cadeia de WAL
sudo tail -f /var/log/postgresql/postgresql-16-main.log

5. Valide antes de promover. Enquanto pausado, o servidor aceita conexões somente leitura:

sudo -u postgres psql -X -c "SELECT pg_is_in_recovery();"          -- espera-se t
sudo -u postgres psql -X -c "SELECT pg_last_wal_replay_lsn();"

# A verificação que importa: os dados estão como antes do incidente?
sudo -u postgres psql -X -d app -c "SELECT count(*) FROM pedidos;"

Se o alvo estiver errado, pare o servidor, ajuste o recovery_target_time, remova o diretório de dados e extraia o base backup de novo. A recuperação não volta atrás de um ponto já reproduzido — recomeça-se da base.

6. Promova, quando os dados estiverem confirmados. Isso encerra a recuperação e torna o cluster gravável. É uma porta de mão única.

sudo -u postgres psql -X -c "SELECT pg_wal_replay_resume();"
sudo -u postgres pg_ctlcluster 16 main promote

sudo -u postgres psql -X -c "SELECT pg_is_in_recovery();"          -- espera-se f

Depois da promoção, o cluster inicia uma nova timeline. É o PostgreSQL mantendo a história abandonada separada da nova, para que uma recuperação futura não confunda as duas. Gere um base backup novo imediatamente — a base antiga e seu WAL pertencem à timeline anterior, e tratá-la como atual é um erro que só se descobre no incidente seguinte.

Verificar um backup físico

O backup_manifest (PostgreSQL 13+) registra um checksum de cada arquivo, e o pg_verifybackup valida o conjunto contra ele:

# Verifica um backup já extraído
pg_verifybackup /var/lib/postgresql/16/restore_test

# PostgreSQL 17+ verifica o conjunto em tar sem extrair
pg_verifybackup --format=tar /var/backups/postgresql-base/20260804T020001

Isso prova que os arquivos estão íntegros. Não prova que o cluster sobe, que a cadeia de WAL está completa nem que a recuperação alcança o seu alvo — só um restore real prova. Agende um exercício trimestral de recuperação: restaure a base mais recente em um host descartável, reproduza até um instante recente qualquer, suba o cluster, rode consultas da aplicação e anote quanto tempo tudo levou. Esse número é o seu RTO real.

O que quebra o PITR na prática

• Um archive_command que falha em silêncio. É a causa mais comum de cluster irrecuperável. Se o comando retorna zero sem ter arquivado o arquivo, o PostgreSQL marca o segmento como pronto e o recicla. Garanta que ele falhe alto, e alerte sobre pg_stat_archiver.failed_count e sobre a contagem de arquivos no próprio destino.
• pg_wal enchendo o disco. Quando o arquivamento trava ou um replication slot abandonado segura WAL, os segmentos se acumulam até o filesystem lotar e o servidor parar. Monitore o tamanho de pg_wal e o pg_replication_slots em busca de slots inativos.
• WAL arquivado apagado antes do base backup que ele atende. A retenção precisa ser pensada como par. Nunca deixe a retenção de WAL menor que a dos base backups.
• Arquivo no mesmo disco do banco. Sobrevive a um DELETE acidental, não à falha de hardware ou de host para a qual o backup físico existe. Mande para fora da máquina.
• Ambiguidade de fuso no recovery_target_time. Escreva sempre o offset explícito ('2026-08-05 16:42:00-03'). Em uma transição de horário de verão, um timestamp local sem qualificação pode casar com dois instantes diferentes.
• Extrair o pg_wal.tar.gz no lugar errado. Ele vai em PGDATA/pg_wal, não em PGDATA. Errar isso produz um cluster que não sobe, normalmente no pior momento possível.

Quando ir além de scripts

O script daqui é propositalmente legível e sem dependências, o que é o ponto de partida certo e suficiente para muitos ambientes de servidor único. A partir de certa escala, ferramentas dedicadas justificam sua complexidade: pgBackRest e Barman acrescentam backups paralelos e comprimidos, backups incrementais e diferenciais, retenção que entende a relação base/WAL, criptografia, arquivamento direto para S3 e — o mais valioso — verificação nativa. Se o seu banco já é grande a ponto de um base backup semanal doer, avalie essas ferramentas em vez de transformar este script em uma versão pior de uma delas.

Torne o arquivamento observável

Falhas de backup físico são silenciosas por natureza: o arquivamento para, o WAL se acumula e nada parece errado até o disco encher ou uma recuperação falhar. O PG Monitoring acompanha a taxa de geração de WAL, o crescimento do pg_wal, o comportamento dos checkpoints e o estado dos replication slots junto da carga que os provoca, para que um arquivamento quebrado apareça como alerta no dia em que quebra — e não no dia em que você precisa dele.

Referências: o manual do PostgreSQL documenta arquivamento contínuo e point-in-time recovery, a referência do pg_basebackup, os parâmetros de archive recovery e o pg_verifybackup.