Le problème

Prenons une boutique en ligne découpée en services Symfony : order, stock, shipping et billing. Chaque service a sa base PostgreSQL, dans son conteneur, sur son volume : autant de bases à exploiter.

Le plan d'exploitation tient souvent en une phrase : « on a des dumps ». Un pg_dump part chaque nuit vers un stockage, et personne ne l'a jamais restauré. Le jour où une migration Doctrine supprime une colonne de trop dans la base de order, trois questions restent sans réponse.

  • Que perd-on ? Tout ce qui a été écrit depuis le dernier dump : jusqu'à une journée de commandes.
  • Combien de temps pour revenir ? Personne n'a jamais chronométré une restauration.
  • Le dump est-il exploitable ? Un fichier présent n'est pas une restauration réussie.

Le reste n'a pas d'outil : pourquoi la réservation de stock ralentit-elle ? Quel index manque ? Qui a accordé DELETE sur la table customer à un compte applicatif ?

Les options

Des dumps logiques planifiés

  • Ce qu'ils règlent : une copie lisible, portable d'une version majeure à l'autre et d'un système à l'autre. pg_dump ne dépend pas de l'ordre de tri du système.
  • Ce qu'ils coûtent : on perd tout ce qui suit le dernier dump, et la restauration rejoue tout le SQL, index compris. Aucun retour à une heure précise, et rien pour la supervision ou l'audit.

Un service PostgreSQL managé

  • Ce qu'il règle : sauvegardes, retour à un instant donné, métriques et montées de version sont fournis.
  • Ce qu'il coûte : il n'est pas toujours possible, quand les données doivent rester dans une zone ou chez un hébergeur imposé. Son coût suit le nombre de bases, et le test de restauration reste à écrire.

Un outil open source par besoin, autour de chaque base

  • Ce qu'il règle : un outil spécialisé et documenté par besoin, activé base par base, sur l'hébergement des services.
  • Ce qu'il coûte : plusieurs outils à tenir à jour, des extensions à précharger, une image à construire. Barman et WAL-G sont des alternatives sérieuses à pgBackRest ; la démarche ne change pas.

Notre recommandation

Quatre besoins, un outil open source par besoin, et une restauration testée chaque jour sans intervention.

  • Superviser, avec pgwatch. Un collecteur lit les vues de statistiques de chaque base avec un rôle pg_monitor. Il range les métriques dans un stockage (PostgreSQL, TimescaleDB ou Prometheus), et Grafana les affiche.
  • Comprendre les requêtes lentes, avec PoWA. Il archive les statistiques de pg_stat_statements, les prédicats de pg_qualstats et les attentes de pg_wait_sampling. Ses suggestions d'index sont testées par HypoPG, avec des index hypothétiques : rien n'est créé sur la base.
  • Tracer les accès, avec pgaudit. Les DDL et les changements de rôles et de droits sont journalisés. Les écritures sur une table sensible le sont dès que le rôle d'audit reçoit le droit d'y écrire.
  • Sauvegarder pour revenir à un instant donné, avec pgBackRest. Des sauvegardes complètes, différentielles et incrémentales, plus l'archivage continu des WAL. Revenir à 10 h 41, juste avant la migration fautive, devient une commande.
  • Tester la restauration. Un script restaure la dernière sauvegarde dans une instance jetable et rejoue les WAL. Il lance une requête métier puis pg_amcheck, et un échec alerte comme une sauvegarde manquée.

Une base, quatre fonctions, et une restauration testée chaque jour order-database PostgreSQL 18, image outillée activée par compose.ops.yml rôles techniques dédiés Superviser pgwatch + Grafana rôle pg_monitor Analyser les requêtes PoWA, pg_qualstats index testés par HypoPG Tracer les accès pgaudit DDL, rôles, écritures Sauvegarder pgBackRest, PITR WAL archivés en continu sauvegardes et WAL restauration testée, dans la même image Dépôt pgBackRest une stanza par base Restaurer instance jetable Contrôle métier requête, fraîcheur pg_amcheck tables, index B-tree chaque jour, après la sauvegarde : rapport OK, ou une alerte

La pile est opt-in : un fichier Compose de plus par service, compose.ops.yml, la branche sur sa base. Les outils partagés tournent une seule fois : collecteur pgwatch, dépôt et interface de PoWA, dépôt pgBackRest. Ils joignent chaque base par un alias réseau, order-database ou stock-database : database peut rejoindre le réseau ops, car seuls ces outils y tournent.

Les compromis assumés

Une image Debian imposée. Les paquets PGDG des extensions existent pour Debian : l'image outillée part de postgres:18.7-trixie. Elle ne monte jamais un volume initialisé par l'image Alpine, car musl et glibc ne trient pas le texte de la même façon.

Les index B-tree sur du texte deviendraient faux, et des recherches manqueraient des lignes existantes. En développement, un volume neuf rempli par les migrations Doctrine suffit. Avec des données, nous passons par pg_dump, que l'ordre de tri n'affecte pas.

Des extensions préchargées. shared_preload_libraries ne change qu'au redémarrage, et chaque extension coûte un peu de mémoire et de CPU. En production, nous gardons l'échantillonnage par défaut de pg_qualstats : une requête sur max_connections.

Des droits larges pour PoWA. Pour ses suggestions, PoWA fait planifier les requêtes sous des index hypothétiques. Sa documentation demande SELECT sur les tables et sur pg_statistic ; sans eux, pas de suggestion. pg_read_all_data donne tout d'un coup, et nous lui préférons des GRANT SELECT ciblés en production.

Un audit au mieux. pgaudit écrit dans le journal de PostgreSQL, sans garantie transactionnelle selon sa documentation. Les journaux partent vers un stockage central, en écriture seule.

Un dépôt et une clé à protéger. Un dépôt sur l'hôte de la base disparaît avec lui. En production, il part sur S3, Azure Blob ou GCS. La clé de chiffrement se range comme un secret de production : sans elle, plus aucune restauration.

Un contrôle partiel. pg_amcheck vérifie les tables et les index B-tree, et ignore les autres types d'index. Chaque test occupe aussi la place d'une copie de la base.

Le signal qui doit faire réexaminer ce choix : des dizaines de bases à outiller. Une instance partagée, avec une base et un rôle par service, réduit alors le nombre de collecteurs, de stanzas et de tests.

Mise en œuvre

Les exemples utilisent PostgreSQL 18.7, pgBackRest 2.60, pgaudit 18.0, PoWA 5.3 et pgwatch 6, avec Docker Compose.

1. Une image outillée

# ops/postgres/Dockerfile
# Debian (glibc) : jamais sur un volume initialisé par une image Alpine (musl).
FROM postgres:18.7-trixie
RUN apt-get update \
 && apt-get install -y --no-install-recommends pgbackrest \
      postgresql-${PG_MAJOR}-pgaudit postgresql-${PG_MAJOR}-powa \
      postgresql-${PG_MAJOR}-pg-qualstats postgresql-${PG_MAJOR}-pg-stat-kcache \
      postgresql-${PG_MAJOR}-pg-wait-sampling postgresql-${PG_MAJOR}-pg-track-settings \
      postgresql-${PG_MAJOR}-hypopg \
 && rm -rf /var/lib/apt/lists/* \
 && install -d -o postgres -g postgres /var/lib/pgbackrest /var/log/pgbackrest /var/spool/pgbackrest

L'image officielle Debian déclare déjà le dépôt PGDG, et PG_MAJOR vient d'elle.

2. Brancher une base

La base habituelle de order tourne sur postgres:18.7-trixie, volume monté sur /var/lib/postgresql. L'overlay change son image et ses réglages, et ajoute un sidecar de sauvegarde.

# services/order/compose.ops.yml
services:
  database:
    build: ../../ops/postgres
    image: app-local/postgres-ops:18.7
    command:
      - postgres
      - -c
      - shared_preload_libraries=pg_stat_statements,pg_stat_kcache,pg_qualstats,pg_wait_sampling,pgaudit
      - -c
      - pgaudit.log=ddl,role
      - -c
      - pgaudit.log_catalog=off
      - -c
      - pgaudit.role=auditor
      - -c
      - archive_mode=on
      - -c
      - archive_command=pgbackrest --stanza=order archive-push %p
      - -c
      - archive_timeout=60
    environment:
      PGBACKREST_REPO1_CIPHER_PASS: ${PGBACKREST_CIPHER_PASS:?required}
    networks:
      default: {}
      ops:
        aliases: [order-database]
    volumes:
      - database_socket:/var/run/postgresql
      - pgbackrest_repo:/var/lib/pgbackrest
      # Chemin par défaut : tout appel à pgbackrest le trouve sans option.
      - ../../ops/postgres/pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf:ro

  backup:
    build: ../../ops/postgres
    image: app-local/postgres-ops:18.7
    entrypoint: [sh, /ops/backup-loop.sh]
    user: postgres
    network_mode: none # parle à sa base par le socket
    environment:
      PGBACKREST_STANZA: order
      PGBACKREST_REPO1_CIPHER_PASS: ${PGBACKREST_CIPHER_PASS:?required}
      PGUSER: app
    volumes_from: [database]
    volumes:
      - ../../ops/postgres/scripts:/ops:ro
    depends_on:
      database:
        condition: service_healthy
    restart: unless-stopped

volumes:
  database_socket:
  pgbackrest_repo:
    external: true
    name: ops-pgbackrest-repo

networks:
  ops:
    external: true

docker compose -f compose.yml -f compose.ops.yml up -d active la pile ; la retirer demande d'abord un DROP EXTENSION pgaudit, dont les triggers d'événement bloqueraient sinon toute DDL. Sur une base Alpine, l'overlay remplacerait ses montages par un volume neuf (volumes: !override).

Pas de pull_policy: build : Compose reconstruirait l'image à chaque up. Avec le magasin d'images containerd, BuildKit attache par défaut une attestation de provenance datée. Même servie par le cache, l'image change d'identifiant, et Compose recrée la base : après une modification du Dockerfile, up -d --build suffit.

3. Des rôles techniques, pas de superutilisateur

-- ops/postgres/init-ops.sql (une fois, par le superutilisateur de la base app)
CREATE ROLE pgwatch LOGIN PASSWORD :'pgwatch_password';
GRANT pg_monitor TO pgwatch;

CREATE ROLE powa LOGIN PASSWORD :'powa_password';
GRANT pg_read_all_stats TO powa;
GRANT pg_read_all_data TO powa; -- les suggestions d'index lisent les tables

CREATE ROLE auditor NOLOGIN; -- cible de l'audit objet de pgaudit
GRANT INSERT, UPDATE, DELETE ON customer TO auditor;

CREATE EXTENSION IF NOT EXISTS pgaudit;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_qualstats;
CREATE EXTENSION IF NOT EXISTS hypopg;

Chaque écriture sur customer est alors journalisée. PoWA demande aussi une base powa sur l'instance, avec ses extensions, et l'appel à powa_register_server() dans son dépôt.

4. Sauvegarder en continu

# ops/postgres/pgbackrest.conf (le mot de passe de chiffrement vient de l'environnement)
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
compress-type=zst
start-fast=y

[order]
pg1-path=/var/lib/postgresql/18/docker
pg1-user=app

[stock]
pg1-path=/var/lib/postgresql/18/docker
pg1-user=app
#!/bin/sh
# ops/postgres/scripts/backup-loop.sh
set -eu
until pg_isready -h /var/run/postgresql -q; do sleep 2; done
# Après une montée de majeure, la stanza enregistre la nouvelle version.
pgbackrest stanza-create || pgbackrest stanza-upgrade
pgbackrest check

while true; do
  case "$(date +%u%H)" in
    701) type=full ;; # dimanche, 1 h
    ?01) type=diff ;; # chaque jour, 1 h
    *) type=incr ;;
  esac
  pgbackrest --type="$type" backup || echo "BACKUP FAILED [$PGBACKREST_STANZA]"
  if [ "$type" != incr ]; then
    sh /ops/restore-test.sh || echo "RESTORE TEST FAILED [$PGBACKREST_STANZA]"
  fi
  sleep $((3600 - $(date +%s) % 3600)) # jusqu'à l'heure pleine suivante
done

Faute de sauvegarde complète, pgBackRest transforme la première demande en complète. Les lignes FAILED alimentent une alerte sur les journaux.

5. Tester la restauration

#!/bin/sh
# ops/postgres/scripts/restore-test.sh
set -eu
dir=$(mktemp -d /tmp/restore-test.XXXXXX)
# Exporté : PostgreSQL relance pgbackrest pour rejouer les WAL, avec ce chemin.
export PGBACKREST_PG1_PATH="$dir" PGHOST="$dir" PGPORT=5499
trap 'pg_ctl -D "$dir" -m immediate stop >/dev/null 2>&1 || true; rm -rf "$dir"' EXIT

pgbackrest --log-level-console=warn restore
# Aucun archivage vers le dépôt, aucun port TCP, pgaudit préchargé.
pg_ctl -D "$dir" -l "$dir/server.log" -w -t 600 start -o "-c port=5499 \
  -c listen_addresses= -c unix_socket_directories=$dir -c archive_mode=off \
  -c shared_preload_libraries=pgaudit -c pgaudit.log=none"
i=0
until [ "$(psql -d postgres -Atc 'select pg_is_in_recovery()' 2>/dev/null)" = f ]; do
  i=$((i + 1)); [ "$i" -le 3600 ] || exit 1; sleep 1
done

echo "Données jusqu'à : $(psql -d postgres -Atc 'select pg_last_xact_replay_timestamp()')"
psql -d app -Atc 'select count(*) from doctrine_migration_versions' # contrôle métier
pg_amcheck --all --install-missing --heapallindexed
echo "RESTORE TEST OK [$PGBACKREST_STANZA]"

Trois précautions évitent des pièges. archive_mode=off empêche l'instance jetable d'écrire dans le dépôt de production. Sans pgaudit préchargé, ses triggers d'événement font échouer toute DDL, dont le CREATE EXTENSION amcheck lancé par pg_amcheck --install-missing.

Enfin, le test tourne dans la même image que la base : une sauvegarde physique restaurée sous une autre libc hérite du problème de tri. pg_last_xact_replay_timestamp() donne la fraîcheur des données restaurées, donc la perte réelle en cas d'incident.

6. Revenir à un instant donné

# services/order : revenir à 10 h 41 (UTC), juste avant la migration fautive
dc() { docker compose -f compose.yml -f compose.ops.yml "$@"; }
dc exec database psql -U app -d app -c 'select last_archived_time from pg_stat_archiver'
dc stop api backup database
dc run --rm --no-deps --entrypoint pgbackrest backup \
  --delta --type=time --target="2026-09-14 10:41:00+00" --target-action=promote restore
dc start database backup api

last_archived_time doit dépasser l'heure visée. pgBackRest choisit une sauvegarde terminée avant cette heure, PostgreSQL rejoue les WAL jusqu'à elle, puis l'instance est promue.

7. Monter de version majeure

L'image officielle a changé de disposition en 18 : PGDATA vaut /var/lib/postgresql/18/docker, et le volume déclaré est /var/lib/postgresql. Elle refuse de démarrer sur des données d'une majeure antérieure, sans y toucher.

# services/order/compose.yml
services:
  database:
    image: postgres:18.7-trixie # majeure figée, jamais lue d'une variable
    environment:
      POSTGRES_DB: app
      POSTGRES_USER: app
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?required}
    healthcheck:
      test: pg_isready -h 127.0.0.1
    volumes:
      - database_data:/var/lib/postgresql # disposition 18+ : données dans 18/docker

volumes:
  database_data:

Une image plus ancienne, lue d'une variable oubliée, chercherait ses données dans /var/lib/postgresql/data : elle initialiserait une base vide à côté des vraies.

Pour une base créée en 16 ou 17, pgautoupgrade lance pg_upgrade --link, range les données dans 18/docker et réindexe. Il aligne aussi les sommes de contrôle, actives par défaut depuis la 18, sur l'ancien cluster.

# services/order/compose.upgrade.yml (un seul déploiement)
services:
  database:
    image: pgautoupgrade/pgautoupgrade:18-trixie # même libc que la base
    healthcheck: !reset null # l'image a le sien

La montée se fait dans le service database lui-même : Compose arrête l'ancien serveur avant de démarrer le nouveau. Un service à part démarrerait à côté de l'ancien serveur, sur le même volume, alors que pg_upgrade exige deux serveurs arrêtés.

POSTGRES_DB doit être passé s'il diffère de l'utilisateur : sinon pgautoupgrade ouvre l'ancien cluster sur une base nommée comme l'utilisateur, et échoue. --link réutilise les fichiers : une fois la 18 démarrée, seule la sauvegarde d'avant permet de revenir.

La base outillée demande plus : pg_upgrade exige, dans la nouvelle majeure, les bibliothèques de chaque extension installée. L'image pgautoupgrade n'a ni pgaudit ni PoWA : nous en dérivons une image de montée qui les installe, répétée sur une copie du volume. Après la montée, le sidecar lance stanza-upgrade, et nous déclenchons aussitôt une sauvegarde complète.

Checklist

  • Une image outillée sur la même libc que la base, jamais sur un volume Alpine.
  • Des rôles techniques (pg_monitor, lecture pour PoWA, rôle d'audit), aucun superutilisateur partagé.
  • Les WAL archivés en continu, avec archive_timeout réglé.
  • Un dépôt chiffré hors de l'hôte, sa clé rangée avec les secrets de production.
  • Un test de restauration planifié : instance jetable, sans archivage, avec pgaudit préchargé.
  • Un contrôle métier et pg_amcheck dans ce test, et une alerte sur chaque échec.
  • Un retour à un instant donné répété avant d'en avoir besoin.
  • La majeure figée dans le tag de l'image, et la montée faite dans le service database.

Sources

Versions relevées le 15 novembre 2026 : PostgreSQL 18.7 (12 novembre), pgBackRest 2.60.0 (18 octobre), pgaudit 18.0, PoWA 5.3 (paquet PGDG postgresql-18-powa) et pgwatch 6.2.0 (14 novembre).