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_dumpne 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 depg_qualstatset les attentes depg_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.
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_timeoutré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_amcheckdans 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).
- pgBackRest, User Guide, Command Reference, Configuration Reference et versions publiées.
- PostgreSQL 18, pg_amcheck, pg_upgrade, rôles prédéfinis et fonctions d'administration.
- PostgreSQL 18, locales, initdb, notes de version et versions maintenues.
- Wiki PostgreSQL, Locale data changes.
- pgaudit, README et code source.
- PoWA, documentation, installation distante, sécurité et HypoPG ; pg_qualstats.
- pgwatch, documentation, préparer les bases et versions publiées.
-
Image officielle PostgreSQL,
documentation
(
PGDATA,VOLUME), Dockerfile 18 et script d'entrée. - pgautoupgrade, README et script d'entrée.
- Docker, services Compose, fusion de fichiers, docker compose up, attestations et provenance.
- Barman et WAL-G.
- Lychen, pull requests 265 et 269 : une mise en œuvre open source de cette pile d'exploitation, puis de la montée à PostgreSQL 18 avec pgautoupgrade, dans un monorepo moon.