Backup lógico do PostgreSQL no Ubuntu: script completo de pg_dump

Escrever um script de backup é fácil; errar também. As falhas raramente são um pg_dump que quebrou — são o arquivo de roles que ninguém salvou, a rotina de retenção que apagou a última cópia boa depois de uma noite malsucedida, duas execuções do cron gravando no mesmo diretório e o dump que nunca foi restaurado. Este guia constrói um script de backup lógico para Ubuntu que fecha cada uma dessas brechas e explica por que cada proteção existe.

Lógico ou físico? Escolha pelo que você precisa recuperar

O PostgreSQL oferece duas famílias de backup, e elas se complementam em vez de competir. Escolher a errada é o erro mais caro deste assunto, então comece por aqui.

A regra prática: use backup lógico para portabilidade e para recuperar aquela única tabela que alguém truncou por engano; use backup físico com arquivamento de WAL para desastre com RPO curto. Ambientes sérios normalmente mantêm os dois. O artigo companheiro cobre o lado físico: backup físico e point-in-time recovery com pg_basebackup.

O que um dump contém e o que não contém

A falha de restore mais comum é um dump que restaura sem erro em um cluster onde nada funciona, porque os objetos que vivem fora do banco nunca foram capturados.

O pg_dump atua sobre um banco de dados. Roles, senhas, definições de tablespace e demais objetos globais do cluster não estão nele. Isso vem do pg_dumpall --globals-only. Um conjunto de backup sem esse arquivo restaura tabelas cujos donos e permissões não existem.

• Incluído: schemas, tabelas, dados, índices, constraints, views, funções, triggers, sequences com o valor atual, extensões (como CREATE EXTENSION) e grants dentro do banco.
• Não incluído: roles e suas participações, definições de tablespace, o conteúdo de tablespaces externos, configuração do servidor (postgresql.conf, pg_hba.conf) e WAL.

O script abaixo sempre grava as duas partes no mesmo conjunto datado, de modo que elas nunca se separem.

Prefira o formato custom, não SQL puro

--format=custom é o padrão que vale adotar. Diferente de um arquivo .sql comum, ele é comprimido e — mais importante — é um contêiner que o pg_restore lê seletivamente:

# Lista tudo o que existe dentro do dump sem restaurar nada
pg_restore --list app.dump

# Restaura uma única tabela de um dump do banco inteiro
pg_restore --data-only --table=pedidos -d app app.dump

# Restaura com workers paralelos (o grande ganho em bases grandes)
pg_restore --jobs=4 -d app app.dump

# Extrai o DDL como SQL legível, para revisar antes de aplicar
pg_restore --schema-only -f revisao.sql app.dump

Um dump em SQL puro não oferece nada disso: restaurar uma tabela significa editar à mão um arquivo de vários gigabytes. O --list também é o que torna possível a verificação barata de integridade que o script aplica em todo dump que produz.

As proteções que tornam um script confiável

Estes cinco comportamentos separam o script abaixo de um pg_dump de uma linha no crontab. Cada um existe por causa de uma forma específica de falha em produção.

• Diretório de staging com rename atômico. Os dumps são gravados em .in-progress-TIMESTAMP e o diretório só é renomeado quando tudo deu certo. Um restore jamais pega um conjunto ainda em escrita, e uma execução interrompida não deixa destroços com aparência de backup válido.
• Verificação de integridade. Todo dump é lido de volta com pg_restore --list. É rápido e pega arquivo truncado ou filesystem de destino que ficou sem espaço em silêncio — as duas falhas que só apareceriam na hora do restore.
• Retenção apenas após sucesso. A exclusão por find -mtime roda depois que existe um conjunto bom. O desastre clássico é o script que apaga primeiro, falha ao gerar o dump e repete isso toda noite até não sobrar nenhuma cópia.
• Um lock. Se o dump demorar mais que o intervalo do cron, a segunda execução começaria a gravar com a primeira ainda em andamento. O flock faz a segunda sair imediatamente.
• Globals em todo conjunto. Roles e tablespaces são exportados junto dos bancos, de forma que o conjunto se baste.

Preparar o host Ubuntu

Execute o backup como o usuário de sistema postgres, que autentica localmente por peer e dispensa senha. As ferramentas cliente vêm dos pacotes do PostgreSQL:

sudo apt update
sudo apt install -y postgresql-client

# Confirme que o cliente é igual ou mais novo que a major do servidor
pg_dump --version
sudo -u postgres psql -XAtqc "SHOW server_version"

# Destino, do postgres e ilegível para os demais
sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

Para um servidor remoto, crie uma role dedicada e use um arquivo ~/.pgpass em vez de colocar senha no script ou em variável de ambiente:

-- No servidor de banco. É necessário ler cada linha, então esta role é poderosa.
CREATE ROLE backup LOGIN PASSWORD 'use-um-segredo-gerado';
GRANT pg_read_all_data TO backup;   -- PostgreSQL 14+

# No host de backup, como usuário postgres
echo 'db.interno:5432:*:backup:use-um-segredo-gerado' >> ~/.pgpass
chmod 600 ~/.pgpass

Script completo de backup

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

#!/usr/bin/env bash
#
# Backup lógico do PostgreSQL no Ubuntu com pg_dump / pg_dumpall.
#
# Uso:
#   sudo -u postgres ./postgresql-logical-backup-ubuntu.sh
#   sudo -u postgres BACKUP_ROOT=/backup/pg RETENTION_DAYS=14 ./postgresql-logical-backup-ubuntu.sh
#   sudo -u postgres DATABASES="app financeiro" ./postgresql-logical-backup-ubuntu.sh
#
set -Eeuo pipefail

BACKUP_ROOT="${BACKUP_ROOT:-/var/backups/postgresql}"
RETENTION_DAYS="${RETENTION_DAYS:-7}"
JOBS="${JOBS:-2}"
COMPRESS_LEVEL="${COMPRESS_LEVEL:-6}"
LOCK_FILE="${LOCK_FILE:-/var/lock/postgresql-logical-backup.lock}"

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

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

[[ "$RETENTION_DAYS" =~ ^[0-9]+$ ]] || fail "RETENTION_DAYS deve ser inteiro."
[[ "$JOBS" =~ ^[1-9][0-9]*$ ]] || fail "JOBS deve ser inteiro positivo."
[[ "$COMPRESS_LEVEL" =~ ^[0-9]$ ]] || fail "COMPRESS_LEVEL deve estar entre 0 e 9."

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

# Serializa execuções: um segundo cron disparando com o primeiro ainda ativo
# gravaria no mesmo conjunto e quebraria a garantia de "completo ou ausente".
exec 9>"$LOCK_FILE" || fail "Não foi possível abrir o lock $LOCK_FILE"
flock -n 9 || fail "Já existe um backup em andamento."

# Falha cedo se o servidor não responder, antes de criar qualquer diretório.
SERVER_VERSION="$(psql -XAtqc 'SHOW server_version' postgres)" ||
  fail "Não foi possível conectar. Verifique PGHOST/PGPORT/PGUSER e ~/.pgpass."

if [[ -z "${DATABASES:-}" ]]; then
  # datallowconn exclui o template0, que não pode ser exportado.
  mapfile -t DATABASE_LIST < <(psql -XAtqc     "SELECT datname FROM pg_database
      WHERE datallowconn AND NOT datistemplate
      ORDER BY datname" postgres)
else
  read -r -a DATABASE_LIST <<< "$DATABASES"
fi

(( ${#DATABASE_LIST[@]} > 0 )) || fail "Nenhum banco para backup."

TIMESTAMP="$(date +%Y%m%dT%H%M%S)"
# Grava em staging e renomeia só no sucesso, para que um conjunto parcial
# nunca seja confundido com backup válido pelo restore ou pela retenção.
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 )) && [[ -d "$STAGING_DIR" ]]; then
    log "Execução falhou; removendo conjunto incompleto $STAGING_DIR"
    rm -rf -- "$STAGING_DIR"
  fi
  exit "$exit_code"
}
trap cleanup_staging EXIT

log "PostgreSQL $SERVER_VERSION"
log "Bancos: ${DATABASE_LIST[*]}"
log "Destino: $FINAL_DIR"

# Roles, tablespaces e demais objetos globais NÃO estão no dump de um banco.
# Sem este arquivo, o restore chega sem donos nem permissões.
log "Exportando globals (roles, tablespaces)"
pg_dumpall --globals-only --no-role-passwords   --file="${STAGING_DIR}/globals.sql"

for database in "${DATABASE_LIST[@]}"; do
  target="${STAGING_DIR}/${database}.dump"
  log "Exportando banco: $database"
  # --jobs vale apenas para o formato directory; custom é gravado serialmente.
  # JOBS vai para o manifesto porque é no pg_restore que ele compensa.
  pg_dump     --format=custom     --compress="$COMPRESS_LEVEL"     --no-password     --verbose     --file="$target"     "$database" 2>"${STAGING_DIR}/${database}.log"

  # Um dump que o pg_restore não lê não é backup. Ler o índice interno é
  # barato e detecta truncamento ou filesystem de destino quebrado.
  pg_restore --list "$target" >/dev/null ||
    fail "Verificação de integridade falhou em $target"

  log "  $(du -h "$target" | cut -f1) — índice do dump verificado"
done

(cd "$STAGING_DIR" && sha256sum ./*.dump ./globals.sql > SHA256SUMS)

cat > "${STAGING_DIR}/MANIFEST.txt" <<MANIFEST
backup_type      logical (pg_dump custom format)
started_at       ${TIMESTAMP}
finished_at      $(date --iso-8601=seconds)
server_version   ${SERVER_VERSION}
host             $(hostname -f 2>/dev/null || hostname)
pg_dump_version  $(pg_dump --version)
databases        ${DATABASE_LIST[*]}
restore_globals  psql -f globals.sql postgres
restore_database pg_restore --clean --if-exists --create --jobs=${JOBS} -d postgres <database>.dump
MANIFEST

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

# A retenção só roda depois que existe um conjunto bom, então uma noite
# ruim nunca apaga a última cópia válida.
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 "Backup concluído: $FINAL_DIR"
log "Tamanho total: $(du -sh "$FINAL_DIR" | cut -f1)"
log "Lembrete: um backup só vale depois que um restore foi testado."

Executar

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

# Primeira execução, explícita e acompanhada
sudo -u postgres /usr/local/bin/postgresql-logical-backup-ubuntu.sh

Uma execução bem-sucedida mostra cada banco, o tamanho do dump e a confirmação de que o índice interno foi lido de volta:

[2026-08-05T02:00:01-03:00] PostgreSQL 16.3 (Ubuntu 16.3-1.pgdg24.04+1)
[2026-08-05T02:00:01-03:00] Bancos: app financeiro
[2026-08-05T02:00:01-03:00] Exportando globals (roles, tablespaces)
[2026-08-05T02:00:02-03:00] Exportando banco: app
[2026-08-05T02:04:17-03:00]   1.4G — índice do dump verificado
[2026-08-05T02:04:17-03:00] Exportando banco: financeiro
[2026-08-05T02:05:02-03:00]   212M — índice do dump verificado
[2026-08-05T02:05:03-03:00] Aplicando retenção: removendo conjuntos com mais de 7 dias
[2026-08-05T02:05:03-03:00] Backup concluído: /var/backups/postgresql/20260805T020001

Cada conjunto se descreve sozinho:

/var/backups/postgresql/20260805T020001/
├── app.dump          # formato custom, restaure com pg_restore
├── app.log           # saída do pg_dump --verbose deste banco
├── financeiro.dump
├── financeiro.log
├── globals.sql       # roles e tablespaces, restaure com psql
├── MANIFEST.txt      # versões, host e os comandos de restore
└── SHA256SUMS        # detecta corrupção silenciosa em repouso

Agendar no cron

Instale o job no usuário postgres para herdar a mesma autenticação peer da execução manual:

sudo crontab -u postgres -e

# Backup lógico diário às 02:00, retenção de duas semanas.
# MAILTO faz o cron entregar o stderr; sem isso, falhas passam em silêncio.
MAILTO=dba@exemplo.com
0 2 * * * RETENTION_DAYS=14 /usr/local/bin/postgresql-logical-backup-ubuntu.sh   >> /var/log/postgresql/backup.log 2>&1

Se preferir timers do systemd ao cron, o equivalente é um serviço Type=oneshot com User=postgres e um timer com OnCalendar=*-*-* 02:00:00 e Persistent=true, que ainda reexecuta um backup perdido enquanto a máquina estava desligada.

Restaurar — a parte que precisa de ensaio

A ordem importa: as roles precisam existir antes dos objetos que pertencem a elas.

cd /var/backups/postgresql/20260805T020001

# 0. Confirme que os arquivos estão íntegros antes de começar
sha256sum --check SHA256SUMS

# 1. Globals primeiro: roles e definições de tablespace
sudo -u postgres psql -f globals.sql postgres

# 2. Depois cada banco. --create cria o banco, então conecte no postgres.
sudo -u postgres pg_restore   --clean --if-exists --create   --jobs=4   --dbname=postgres   app.dump

O que cada flag faz e quando ela machuca:

• --clean --if-exists derruba os objetos existentes antes de recriá-los. Isso destrói o conteúdo atual do banco de destino. Nunca aponte para um banco de produção que você não pretende sobrescrever.
• --create executa o CREATE DATABASE a partir do dump, por isso você conecta em postgres e não no banco que está sendo restaurado.
• --jobs=4 restaura tabelas e constrói índices em paralelo. É o maior ganho isolado de tempo; use perto da quantidade de núcleos e note que não combina com --single-transaction.
• --single-transaction torna o restore tudo-ou-nada — valioso em uma recuperação crítica, mas serializa o trabalho e mantém locks o tempo todo.

Recuperar uma tabela só a partir do dump completo é o caso do dia a dia, e é por isso que o formato custom valeu a escolha:

# Veja o que está disponível
pg_restore --list app.dump | grep -i pedidos

# Restaure só os dados dessa tabela em um banco descartável e depois
# copie as linhas. Restaurar direto por cima da produção perde tudo
# que foi gravado desde o dump.
sudo -u postgres createdb app_recuperacao
sudo -u postgres pg_restore --table=pedidos --dbname=app_recuperacao app.dump

Depois de qualquer restore, as estatísticas não existem até que o planner receba novas. Até lá, as consultas podem ser muito mais lentas que na origem:

sudo -u postgres vacuumdb --analyze-in-stages --dbname=app

Provar que o backup funciona

Backup não verificado é hipótese. Verificar significa restaurar por inteiro em um ambiente descartável e conferir os dados — não apenas que o comando terminou com zero.

# Restaura o conjunto da última noite em um banco descartável e compara
LATEST=$(ls -1d /var/backups/postgresql/20*T* | tail -1)
sudo -u postgres createdb teste_restore
sudo -u postgres pg_restore --jobs=4 -d teste_restore "$LATEST/app.dump"

# Contagem de linhas por tabela, origem contra restaurado
sudo -u postgres psql -X -d teste_restore -c "
  SELECT relname, n_live_tup
    FROM pg_stat_user_tables
   ORDER BY n_live_tup DESC
   LIMIT 20;"

sudo -u postgres dropdb teste_restore

Faça isso com periodicidade — mensal no mínimo — e registre quanto tempo o restore levou. Esse número é o seu RTO real, e costuma ser várias vezes maior do que as pessoas imaginam, porque a reconstrução de índices domina.

Falhas comuns e o que significam

• pg_dump: error: aborting because of server version mismatch — o cliente é mais antigo que o servidor. Instale o postgresql-client mais novo.
• O dump demora mais a cada noite — normalmente é crescimento, mas verifique bloat: uma tabela com muito mais linhas mortas que vivas é lida por inteiro pelo pg_dump. Ajustar o autovacuum resolve na origem.
• Dumps longos travam mudanças de schema — o pg_dump mantém um lock ACCESS SHARE em cada tabela durante toda a execução, então um ALTER TABLE concorrente espera, e qualquer consulta enfileirada atrás desse ALTER também espera. Agende migrações fora da janela de backup.
• Permissão negada em uma tabela — a role de backup não tem leitura em algo criado depois. GRANT pg_read_all_data evita esse descompasso de grants.
• O disco enche no meio do dump — o diretório de staging é descartado e a execução sai com erro, então nenhum conjunto truncado é promovido. Monitore espaço livre no volume de backup como métrica de primeira classe.

Observe o backup como você observa o banco

A métrica que importa não é "o job rodou", e sim "qual a idade do backup verificado mais recente e ele pode ser restaurado dentro da janela de recuperação". O PG Monitoring acompanha crescimento, bloat e comportamento de I/O junto da carga de trabalho, para que você veja a janela de dump se esticando na direção da janela de manutenção antes que as duas colidam — e correlacione um backup lento com o checkpoint ou o vacuum que realmente o está causando.

Referências: o manual do PostgreSQL cobre SQL dump, as referências de pg_dump e pg_restore, e o pg_dumpall para os globals do cluster.