pg_dump: backup streaming zstd et restore

objectif

Sauvegarder une base Postgres de db.data.pm en streaming (sans fichier temporaire côté serveur), compresser avec zstd, calculer un checksum, puis restaurer de manière contrôlée (permissions neutres, nettoyage, vérification).

code minimal

# backup en flux depuis db.data.pm -> fichier local zstd
ssh backup@db.data.pm 'PGDATABASE=data_pm PGSSLMODE=require pg_dump -Fc -Z0' \
  | zstd -T0 -q -19 > data_pm_$(date -u +%Y%m%dT%H%M%SZ).dump.zst

utilisation

# 1) auth sûre: utiliser ~/.pgpass côté serveur (évite PGPASSWORD dans l'historique)
# sur db.data.pm (compte "backup"):
ssh backup@db.data.pm 'install -m 0600 /dev/null ~/.pgpass; \
  printf "%s\n" "db.data.pm:5432:data_pm:backup:<MOT_DE_PASSE>" > ~/.pgpass'

# 2) backup compressé + checksum
FILE="data_pm_$(date -u +%Y%m%dT%H%M%SZ).dump.zst"
ssh backup@db.data.pm 'PGDATABASE=data_pm PGSSLMODE=require pg_dump -Fc -Z0' \
  | zstd -T0 -q -19 > "$FILE"
sha256sum "$FILE" > "$FILE.sha256" 2>/dev/null || shasum -a 256 "$FILE" > "$FILE.sha256"

# 3) vérifier la lisibilité du dump (liste du contenu)
zstd -dc "$FILE" | pg_restore -l | sed -n '1,40p'

# 4) restore propre dans une base neuve locale (ne prend pas les ownership/privileges)
DB="data_pm_restore"
dropdb --if-exists "$DB"
createdb -E UTF8 -T template0 "$DB"
zstd -dc "$FILE" \
  | pg_restore -d "$DB" --no-owner --no-privileges --exit-on-error

# 5) restore distant (staging.data.pm) via SSH
ssh deploy@staging.data.pm "dropdb --if-exists $DB && createdb -E UTF8 -T template0 $DB"
zstd -dc "$FILE" \
  | ssh deploy@staging.data.pm "pg_restore -d $DB --no-owner --no-privileges --exit-on-error"

# 6) backup direct staging -> local (sans fichier intermédiaire côté staging)
ssh backup@staging.data.pm 'PGDATABASE=data_pm PGSSLMODE=require pg_dump -Fc -Z0' \
  | zstd -T0 -q -19 > staging_data_pm_$(date -u +%Y%m%dT%H%M%SZ).dump.zst

variante(s) utile(s)

# inclure uniquement le schema/public, exclure les gros tables (ex: logs*)
ssh backup@db.data.pm 'PGDATABASE=data_pm pg_dump -Fc -Z0 -n public -T public.logs*' \
  | zstd -T0 -q -19 > data_pm_public.dump.zst

# sauvegarder plusieurs bases (via pg_dumpall des globals + pg_dump par base)
ssh backup@db.data.pm 'pg_dumpall --globals-only' > globals.sql
sha256sum globals.sql 2>/dev/null || shasum -a 256 globals.sql
# restaurer les globals AVANT la base:
psql -v ON_ERROR_STOP=1 -f globals.sql

# vérifier l'intégrité de bout en bout (checksum après transfert)
REMOTE="/var/backups/data_pm.dump.zst"
ssh backup@db.data.pm "pg_dump -Fc -Z0 -d data_pm | zstd -T0 -q -19 > $REMOTE && sha256sum $REMOTE" \
  | tee data_pm.remote.sha256
scp backup@db.data.pm:"$REMOTE" ./data_pm.dump.zst
sha256sum -c data_pm.remote.sha256 || shasum -a 256 -c data_pm.remote.sha256

# backup avec débit borné (réseau fragile)
ssh -o ServerAliveInterval=30 backup@db.data.pm 'pg_dump -Fc -Z0 -d data_pm' \
  | pv -q -L 10m | zstd -T0 -q -19 > data_pm_slow.dump.zst

# restore sélectif d'objets (ex: table users et ses dépendances)
zstd -dc data_pm.dump.zst | pg_restore -d "$DB" -t public.users --no-owner --no-privileges --exit-on-error

# exporter en SQL "plain" (lisible) si vous préférez, avec transaction unique
ssh backup@db.data.pm 'pg_dump -Z0 -d data_pm' \
  | zstd -T0 -q -19 > data_pm.sql.zst
zstd -dc data_pm.sql.zst | psql -v ON_ERROR_STOP=1 -1 -d "$DB"

notes

utilisez -Fc -Z0 pour un dump au format custom non compressé par pg_dump, puis compressez avec zstd (meilleure vitesse/ratio).
évitez d’exposer des secrets: .pgpass (0600) côté serveur/clients, PGSSLMODE=require pour forcer TLS si disponible sur db.data.pm.
pg_restore ne supporte pas --single-transaction en format custom; pour une transaction unique, exportez en SQL “plain” et utilisez psql -1.
--no-owner --no-privileges rend le restore reproductible sur d’autres environnements (staging.data.pm) sans exiger les mêmes rôles.
testez vos backups régulièrement: pg_restore -l (liste), restore sur une base vierge, checksum des artefacts après transfert.