Aller au contenu

PostgreSQL externe (installation, durcissement, HA, sauvegarde)

Le DATABASE_URL de Repod est une simple chaîne de connexion PostgreSQL, sans aucun chemin de code qui suppose que la base de données vit dans un conteneur Docker — voir Guide de déploiement — utiliser une base PostgreSQL externe pour savoir comment pointer docker-compose.yaml vers une telle instance. Cette page est le guide compagnon pour l'autre moitié de cette décision : comment réellement déployer, sécuriser, rendre hautement disponible et sauvegarder l'instance PostgreSQL elle-même, une fois que vous avez décidé de la faire tourner hors du conteneur db: embarqué.

Il s'agit d'administration PostgreSQL générale, écrite pour un opérateur Repod qui souhaite la même profondeur « je maîtrise ma base de données » que les produits de dépôt d'artefacts dédiés documentent pour ce scénario exact. Rien ici n'est de la magie propre à Repod — un DBA PostgreSQL qui fait déjà tourner du Postgres en production ailleurs reconnaîtra chaque étape. PostgreSQL uniquement ; aucun autre moteur de base de données n'est dans le périmètre de Repod.

1. Compatibilité & prérequis

Version de référence : PostgreSQL 16. C'est ce que postgres:16-alpine fournit dans le docker-compose.yaml/docker-compose.rpm.yml propre à Repod, et ce contre quoi tourne la suite de tests — considérez-la comme la version contre laquelle Repod est validé, et privilégiez-la (ou une version majeure plus récente, une fois que vous avez vous-même confirmé la compatibilité) pour un nouveau déploiement.

Version minimale supportée. Rien dans le schéma ou la couche de requêtes propres à Repod ne nécessite une fonctionnalité PostgreSQL récente spécifique :

  • Les colonnes JSONB (backend/db/tables.py) sont disponibles depuis PostgreSQL 9.4.
  • Les verrous consultatifs (pg_try_advisory_lock()/pg_advisory_unlock(), utilisés pour l'élection de leader dans backend/services/leader_election.py) sont une fonctionnalité de base ancienne, sans comportement spécifique à une version dont Repod dépendrait.
  • Le multi-tenant basé sur les schémas (CREATE SCHEMA IF NOT EXISTS + SET LOCAL search_path, utilisé en mode SaaS) est du SQL standard supporté par toute version de PostgreSQL dans n'importe quelle fenêtre de support réaliste.
  • Alembic (backend/alembic/versions/) génère du DDL simple — CREATE TABLE, ALTER TABLE ADD COLUMN, des index — rien d'exotique.

En pratique, cela signifie que Repod lui-même n'impose pas de version minimale plus récente que ce que n'importe quelle version PostgreSQL activement supportée fournit déjà. Ne lisez pas cela comme une licence pour faire tourner une vieille version quand même — la politique de versionnement propre à PostgreSQL supporte chaque version majeure pendant 5 ans après sa sortie initiale, après quoi elle cesse de recevoir des correctifs de sécurité. Faites tourner la version majeure encore dans cette fenêtre de support au moment du déploiement, et vérifiez cette page vous-même plutôt que de faire confiance à une date figée dans ce guide (les fenêtres de support avancent chaque année ; une date de fin de vie précise écrite ici aujourd'hui deviendrait obsolète). Comme plancher, ne provisionnez rien de plus ancien que PostgreSQL 13 — au-delà de « encore supportée », cela n'a aucune incidence sur la compatibilité avec Repod spécifiquement.

Outillage client. Rien à installer côté Repod. pg_dump/pg_restore sont déjà fournis dans l'image backend-api (utilisés par la fonctionnalité de sauvegarde intégrée propre à Repod, voir Sauvegarder et restaurer Repod) — vous n'avez besoin des binaires clients psql/pg_dump/pg_restore que sur l'hôte depuis lequel vous administrez le serveur PostgreSQL, en les faisant correspondre à la version majeure du serveur, comme le veut la pratique PostgreSQL habituelle.

Locale & encodage. Créez la base de données avec l'encodage UTF8 — rien dans le code de Repod n'est sensible à l'encodage ou à la locale (il stocke du JSON et du texte brut via SQLAlchemy/psycopg2, tous deux UTF-8 de bout en bout), il n'y a donc aucune exigence de locale propre à Repod. en_US.UTF-8 ou le C.UTF-8 indépendant de la locale sont tous deux des choix sûrs et standards ; évitez SQL_ASCII quel que soit le défaut de votre OS, car il autorise silencieusement des données incohérentes du point de vue de l'encodage dans une base que Repod suppose UTF-8 de bout en bout.

Extensions. Aucune n'est requise. Il n'y a aucun CREATE EXTENSION nulle part dans le backend de Repod — le code s'appuie délibérément sur aucune extension Postgres pour sa fonctionnalité centrale. (Une extension, pg_stat_statements, est un ajout opérationnel réellement utile pour la supervision des requêtes — voir Paramètres opérationnels supplémentaires ci-dessous. C'est un choix de supervision propre à l'opérateur, pas quelque chose que Repod exige pour fonctionner, et cela ne contredit pas l'affirmation « aucune extension requise » ci-dessus.)

2. Installation

Utilisez les dépôts officiels du PostgreSQL Global Development Group (PGDG) plutôt que le paquet postgresql embarqué de votre distribution — les dépôts de distribution ont typiquement plusieurs versions majeures de retard, et PGDG est la méthode recommandée par le projet PostgreSQL lui-même pour installer une version actuelle et activement supportée sous Linux. Les étapes ci-dessous correspondent aux deux familles d'OS que le guide de configuration client propre à Repod documente déjà : Debian/Ubuntu (APT) et famille RHEL/openSUSE (RPM).

Debian / Ubuntu (dépôt APT PGDG)

# Importer la clé de signature de PGDG
sudo apt install -y curl ca-certificates gnupg
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc \
  --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Ajouter le dépôt PGDG (fait correspondre automatiquement votre nom de code Debian/Ubuntu)
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

sudo apt update
sudo apt install -y postgresql-16 postgresql-client-16

apt-get install postgresql (sans suffixe de version) récupérerait quelle que soit la version que les dépôts propres de votre distribution proposent — installez toujours le paquet versionné (postgresql-16) depuis le dépôt PGDG à la place, afin que la version soit un choix délibéré, et non ce qui se trouvait être actuel au moment où votre version de distribution a été figée.

Famille RHEL (AlmaLinux / Rocky / RHEL) — dépôt yum/dnf PGDG

# Installer le RPM du dépôt PGDG (ajustez l'URL selon votre version majeure d'OS, ici EL9)
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# RHEL 8/9 fournissent leur propre flux de module "postgresql" intégré, qui
# entre en conflit avec le paquet postgresql16-server propre à PGDG — le désactiver d'abord
sudo dnf -qy module disable postgresql

sudo dnf install -y postgresql16-server postgresql16-contrib

# Initialiser le répertoire de données et activer le service
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable --now postgresql-16

openSUSE Leap

sudo zypper addrepo https://download.postgresql.org/pub/repos/zypp/repos/opensuse/15.6/postgresql16-16/postgresql16-16.repo
sudo zypper refresh
sudo zypper install postgresql16-server postgresql16
sudo systemctl enable --now postgresql

Les URL exactes du dépôt PGDG peuvent évoluer avec le temps

Les URL de clé/dépôt PGDG ci-dessus reflètent la procédure de configuration PGDG standard actuelle au moment de l'écriture. Si l'une d'elles renvoie une erreur 404, consultez postgresql.org/download pour les instructions actuelles de configuration de dépôt pour votre distribution — le mécanisme sous-jacent (un dépôt apt/yum PGDG dédié, désactiver le flux de module embarqué de la distribution sur RHEL 8/9) est stable ; seuls les noms de fichiers exacts changent occasionnellement au fil des mises à jour de l'infrastructure du projet PostgreSQL.

Créer la base de données et le rôle repod

Une fois le serveur installé et démarré, créez une base de données dédiée et un rôle limité à cette seule base — ne pointez jamais Repod vers une base partagée et préexistante, et n'utilisez jamais le rôle superutilisateur postgres pour la connexion applicative.

sudo -u postgres psql <<'SQL'
CREATE ROLE repod WITH LOGIN PASSWORD 'CHANGE_ME';
CREATE DATABASE repod OWNER repod ENCODING 'UTF8';
GRANT ALL PRIVILEGES ON DATABASE repod TO repod;
SQL

Générez le mot de passe de la même façon que le guide de déploiement propre à Repod génère déjà POSTGRES_PASSWORD pour le conteneur embarqué :

openssl rand -hex 24

Utilisez cette valeur à la fois comme mot de passe du rôle ci-dessus et dans DATABASE_URL :

DATABASE_URL=postgresql://repod:<même mot de passe>@<hôte-externe>:5432/repod

Vérifier que ça fonctionne

psql "postgresql://repod:<mot de passe>@<hôte-externe>:5432/repod" -c '\conninfo'

Vous devriez voir You are connected to database "repod" as user "repod". Si cette connexion réussit, entrypoint.sh parviendra à exécuter les migrations Alembic contre cette base de la même façon qu'il le ferait contre le conteneur embarqué — voir Guide de déploiement, étape 4 pour câbler DATABASE_URL dans docker-compose.yaml.

3. Durcissement de sécurité

Liaison réseau

Définissez listen_addresses dans postgresql.conf sur l'interface (ou les interfaces) spécifique(s) devant accepter des connexions — ne le laissez jamais au défaut (localhost uniquement, ce qui le rendrait totalement injoignable depuis backend-api) sans immédiatement associer une liaison plus large à un pg_hba.conf restrictif :

postgresql.conf
listen_addresses = '10.0.1.5'   # l'interface réseau privé que backend-api atteint, pas '*'
port = 5432

Se lier à * est parfois inévitable (par exemple si l'adresse de l'interface n'est pas statique), mais si vous le faites, c'est pg_hba.conf — et non l'adresse d'écoute — qui doit réellement restreindre qui peut se connecter. Ne comptez jamais sur listen_addresses seul comme contrôle d'accès.

pg_hba.conf — moindre privilège

N'autorisez que le rôle repod à se connecter, uniquement depuis le(s) hôte(s) ou le sous-réseau spécifique(s) exécutant backend-api, et uniquement en utilisant l'authentification par mot de passe scram-sha-256 (la méthode de mot de passe actuellement recommandée par PostgreSQL — jamais trust, et jamais l'ancienne méthode md5 pour un nouveau déploiement) :

pg_hba.conf
# TYPE  DATABASE  USER   ADDRESS           METHOD
host    repod     repod  10.0.1.10/32      scram-sha-256   # hôte backend-api unique
host    repod     repod  10.0.1.0/24       scram-sha-256   # ou un sous-réseau, pour plusieurs répliques (HA)

Supprimez ou commentez toute règle par défaut plus large ajoutée par votre installeur (certains paquets de distribution fournissent par défaut une ligne permissive 0.0.0.0/0/::/0 — vérifiez explicitement sa présence plutôt que de supposer qu'elle n'y est pas). Définissez aussi password_encryption = scram-sha-256 dans postgresql.conf, afin que les rôles nouvellement créés soient hachés avec SCRAM plutôt qu'avec l'ancien format MD5 :

postgresql.conf
password_encryption = scram-sha-256

Rechargez après toute modification de pg_hba.conf : sudo systemctl reload postgresql (ou SELECT pg_reload_conf(); depuis psql) — aucun redémarrage, aucune coupure de connexion, n'est nécessaire.

TLS pour les connexions en transit

Activez TLS sur le serveur :

postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Utilisez un certificat émis par votre AC interne (ou une AC publique si le point de terminaison est accessible depuis l'extérieur) — un certificat auto-signé fonctionne pour les tests mais signifie que Repod ne peut pas vérifier l'identité du serveur contre une véritable chaîne de confiance en mode verify-full.

Exprimez l'exigence TLS dans DATABASE_URL via le paramètre de requête standard libpq/psycopg2 sslmode — psycopg2 (et le dialecte postgresql+psycopg2:// de SQLAlchemy, celui utilisé par Repod) analysent tous deux les paramètres de connexion libpq standards ajoutés à l'URL :

# Exiger TLS, mais ne pas vérifier le certificat serveur contre une AC
DATABASE_URL=postgresql://repod:<mot de passe>@pg-host:5432/repod?sslmode=require

# Exiger TLS ET vérifier le certificat serveur contre une AC de confiance
# (le mode le plus fort — vérifie aussi que le nom d'hôte correspond au certificat)
DATABASE_URL=postgresql://repod:<mot de passe>@pg-host:5432/repod?sslmode=verify-full&sslrootcert=/path/to/ca.crt

sslrootcert doit pointer vers un chemin à l'intérieur du conteneur backend-api — montez votre certificat d'AC dans l'image/le volume du conteneur si vous utilisez verify-full. sslmode=require seul (sans sslrootcert) chiffre la connexion mais n'authentifie pas le serveur, ce qui reste une réelle amélioration par rapport à une connexion non chiffrée et un minimum raisonnable sur un réseau privé de confiance ; privilégiez verify-full dès que la connexion traverse une frontière réseau que vous ne contrôlez pas entièrement.

Pare-feu

Seul(s) le(s) hôte(s) exécutant backend-api devrai(en)t jamais pouvoir atteindre le port 5432 — le même principe « jamais exposer directement sur internet » que la documentation propre à Repod énonce déjà pour ses autres services exposés au réseau (dépôts APT/RPM en façade nginx, points de terminaison à jeton interne du SaaS). Restreignez avec le pare-feu de votre plateforme (ufw, firewalld, un groupe de sécurité cloud) en plus de pg_hba.conf — de la défense en profondeur, pas un substitut à celui-ci.

# exemple ufw — n'autoriser que le sous-réseau backend-api
sudo ufw allow from 10.0.1.0/24 to any port 5432 proto tcp

Rotation des mots de passe & moindre privilège

  • Faites tourner le mot de passe du rôle repod périodiquement (ALTER ROLE repod WITH PASSWORD '<nouvelle valeur>';), mettez à jour DATABASE_URL dans backend.env, et redémarrez backend-api — il n'existe aucun chemin de rechargement d'identifiants à chaud, le pool de connexions est construit une seule fois au démarrage du processus (db/engine.py:get_engine()).
  • Le rôle repod doit être propriétaire de sa base de données et de rien d'autre — il n'a besoin d'aucun attribut SUPERUSER, CREATEDB, ou CREATEROLE. L'instruction CREATE ROLE ... WITH LOGIN PASSWORD ... ci-dessus n'en accorde délibérément aucun.
  • Ne réutilisez jamais le compte superutilisateur postgres comme rôle applicatif — Repod n'a jamais besoin de privilèges superutilisateur pour quoi que ce soit qu'il fasse (création de schéma via Alembic, CREATE SCHEMA pour les tenants SaaS, et les verrous consultatifs sont tous des opérations relevant du privilège d'un rôle ordinaire).

4. Haute disponibilité

Cette section porte sur la HA côté serveur PostgreSQL — faire tourner un cluster de base de données résilient qui survit à la perte d'un nœud. C'est une préoccupation différente de Déployer une haute disponibilité multi-répliques, qui couvre la HA au niveau applicatif pour backend-api lui-même et suppose simplement « un point de terminaison PostgreSQL HA externe » comme prérequis, sans expliquer comment en construire un. Cette page est ce vers quoi pointe la section des prérequis de ce guide.

Fondamentaux de la réplication en streaming

Le mécanisme de réplication intégré à PostgreSQL est un nœud primaire qui expédie son journal des transactions (WAL, write-ahead log) vers un ou plusieurs nœuds secondaires (standby), qui le rejouent en continu pour rester synchronisés :

  • Réplication asynchrone (le défaut) — le primaire valide une transaction et retourne au client sans attendre la confirmation de réception d'un secondaire quelconque. Latence d'écriture la plus basse ; un secondaire peut avoir un retard d'une petite fenêtre, généralement inférieure à la seconde. Si le primaire tombe en panne avant qu'un secondaire n'ait rattrapé son retard, les transactions les plus récentes peuvent être perdues — un compromis disponibilité/durabilité, pas un bug.
  • Réplication synchrone (synchronous_standby_names) — le primaire attend un accusé de réception d'au moins un secondaire désigné avant de valider. Garantit zéro perte de données lors d'un basculement vers ce secondaire, au prix d'une latence d'écriture accrue (chaque validation attend désormais un aller-retour réseau) et, si le secondaire synchrone devient injoignable, soit des écritures bloquées, soit un comportement de repli que vous devez configurer explicitement (mode synchronous_commit).

Choisissez en fonction de vos besoins réels : l'asynchrone est le défaut courant pour la plupart des déploiements ; le synchrone vaut le coût en latence spécifiquement quand quelques secondes de transactions perdues lors d'un basculement seraient inacceptables (données sensibles sur le plan financier/réglementaire, par exemple — évaluez cela selon vos propres exigences, pas selon une recommandation générale dans un sens ou dans l'autre).

Outillage de basculement automatisé

La réplication propre de PostgreSQL est le mécanisme d'expédition des données — elle ne promeut pas d'elle-même un secondaire en primaire automatiquement quand le primaire meurt. C'est ce qu'ajoute un outil dédié d'orchestration de basculement. Trois options largement utilisées et réalistes, dans un ordre approximatif de maturité/adoption :

Outil Approche Notes
Patroni Un agent Python tournant aux côtés de chaque nœud PostgreSQL, utilisant un magasin de consensus distribué (etcd, Consul, ou ZooKeeper) pour élire et maintenir le primaire L'option moderne la plus largement adoptée pour la HA PostgreSQL automatisée ; la plus capable, mais nécessite d'exploiter un cluster DCS (magasin de configuration distribué) séparé aux côtés de PostgreSQL lui-même — une réelle surface opérationnelle supplémentaire
repmgr Un gestionnaire de réplication natif à PostgreSQL avec son propre schéma de métadonnées ; peut automatiser le basculement via repmgrd, ou être utilisé purement pour une promotion manuelle/assistée Plus simple à raisonner et sans dépendance DCS externe, au prix d'une protection anti-split-brain automatique moins sophistiquée que Patroni
pg_auto_failover Un modèle à deux/trois nœuds plus simple (issu à l'origine de Citus Data, désormais Microsoft) avec son propre processus de supervision léger, sans DCS externe Plus facile à déployer que Patroni pour un petit cluster ; moins éprouvé à très grande échelle

Repod ne s'intègre à aucun de ces outils, et n'en a pas besoin. Le code propre à Repod n'a aucune connaissance de la topologie de réplication PostgreSQL — il a simplement besoin d'une chaîne de connexion stable qui résout toujours vers le nœud actuellement primaire. L'outil que vous choisissez relève entièrement de votre propre décision opérationnelle, prise indépendamment de Repod.

Présenter un point de terminaison stable unique à Repod

Quel que soit le mécanisme de basculement choisi, backend-api doit se connecter via un point de terminaison unique qui achemine de façon transparente vers le primaire actuel — jamais directement vers le nom d'hôte d'un secondaire spécifique, ce qui casserait silencieusement dès que ce nœud cesserait d'être le primaire. Options standards :

  • Un pooler/proxy de connexions devant le cluster — PgBouncer ou HAProxy configuré avec un contrôle de santé qui n'achemine que vers le nœud qui se rapporte actuellement comme primaire (Patroni fournit un point de terminaison de contrôle de santé REST conçu exactement pour cela ; HAProxy peut le sonder directement). C'est le motif de production le plus courant et celui vers lequel convergent la plupart des tutoriels HA PostgreSQL.
  • Une IP flottante/virtuelle — déplacée vers quel que soit le nœud primaire par l'outil de basculement lui-même (Patroni le supporte via un script de rappel ; keepalived est un compagnon courant pour la mécanique de VIP). Simple, mais nécessite que les nœuds partagent un segment réseau L2.
  • Basculement basé sur DNS — un enregistrement DNS à faible TTL mis à jour pour pointer vers le primaire actuel. Fonctionne, mais est le moins immédiat des trois — la mise en cache DNS (y compris la mise en cache côté client PostgreSQL, et tout résolveur intermédiaire) peut retarder la convergence au-delà de ce qu'obtient un proxy ou une VIP.

DATABASE_URL pointe alors simplement vers l'hôte et le port de ce point de terminaison stable — la même variable, le même format, que pour pointer vers une instance unique non-HA :

DATABASE_URL=postgresql://repod:<mot de passe>@pg-ha-endpoint:5432/repod?sslmode=require

Ce que Repod fait (et ne fait pas) pendant un basculement

Repod ne participe d'aucune façon au basculement PostgreSQL — il n'existe aucun code Repod qui détecte une topologie primaire/secondaire, déclenche une promotion, ou réagit spécialement à celle-ci. Tout ce qu'il fait, c'est se reconnecter. Le pool de connexions de SQLAlchemy (pool_pre_ping=True, pool_recycle=1800 — backend/db/engine.py) valide déjà une connexion avant de la réutiliser et en ouvre une nouvelle de façon transparente si l'ancienne connexion est périmée ou rompue, donc une fois que votre mécanisme HA choisi a fini de promouvoir un nouveau primaire et que le point de terminaison stable achemine à nouveau vers lui, backend-api le prend en compte à sa prochaine requête, sans redémarrage ni configuration particulière — exactement le même comportement de reconnexion que le guide de HA au niveau applicatif documente déjà en s'y appuyant.

5. Sauvegarde & restauration (au niveau opérateur du serveur PostgreSQL)

Cette section couvre une protection supplémentaire au niveau opérateur du serveur PostgreSQL — complémentaire à, et non un remplacement de, la sauvegarde intégrée à Repod, déclenchée par un administrateur et basée sur pg_dump, déjà documentée dans Sauvegarder et restaurer Repod et le runbook opérationnel. La sauvegarde intégrée propre à Repod fonctionne déjà sans modification contre une instance PostgreSQL externe — elle exécute pg_dump <DATABASE_URL> -F c depuis l'intérieur du conteneur backend-api (backend/services/backup.py), elle n'a donc besoin de rien de plus que d'une accessibilité réseau et fonctionne de façon identique, que la base de données soit un conteneur voisin ou une instance managée distante. Ce qui est couvert ici est ce qu'un opérateur de serveur PostgreSQL ajouterait par-dessus pour la durabilité au niveau serveur, indépendamment du fait que la sauvegarde propre à Repod s'exécute ou non : sauvegardes physiques, archivage continu de WAL, et récupération à un point dans le temps (PITR).

Sauvegardes physiques avec pg_basebackup

pg_basebackup prend une copie binaire cohérente de l'intégralité du répertoire de données directement depuis un serveur en cours d'exécution — le fondement à la fois pour mettre en place un nouveau secondaire et pour une sauvegarde de base physique (par opposition à logique/pg_dump) :

pg_basebackup -h pg-host -U repod_backup -D /backups/base/$(date +%Y%m%d) \
  -F tar -z -P -X stream

-X stream inclut le WAL généré pendant la sauvegarde elle-même, de sorte que le résultat est immédiatement restaurable seul, sans avoir besoin d'un segment WAL archivé séparément pour ce point exact dans le temps. Utilisez un rôle dédié avec le privilège REPLICATION pour cela (pas le rôle applicatif repod) — pg_basebackup nécessite un accès de niveau réplication, un privilège nettement différent de celui dont l'application a jamais besoin.

Archivage continu de WAL (pour la récupération à un point dans le temps)

Une seule sauvegarde de base ne vous permet de restaurer qu'au moment où elle a été prise. Pour récupérer à un point arbitraire entre deux sauvegardes de base — « restaurer juste avant la mauvaise migration exécutée à 14:32 » — PostgreSQL a besoin de chaque segment WAL généré depuis la sauvegarde de base, archivé en continu :

postgresql.conf
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'   # ou pousser vers du stockage objet, voir ci-dessous
wal_level = replica

Un cp local n'est qu'illustratif — dans un déploiement réel, archive_command expédie typiquement chaque segment WAL vers un stockage durable (stockage objet, un hôte séparé) plutôt que vers un répertoire local qui partage le même domaine de panne que le primaire.

Outils dédiés de sauvegarde/PITR

Le scripting artisanal pg_basebackup + archive_command fonctionne, mais à l'échelle de la production, deux outils dédiés sont le choix standard pour automatiser cela correctement (sauvegardes parallèles, politiques de rétention, vérification des sauvegardes, PITR simplifiée) — utilisez l'un d'eux plutôt que de maintenir un scripting sur mesure dès lors que vous comptez sur cela pour de véritables garanties de reprise :

  • pgBackRest — largement adopté, supporte la sauvegarde/restauration parallèle, la vérification des sauvegardes, et plusieurs cibles de dépôt (local, stockage objet compatible S3, Azure, GCS).
  • Barman (« Backup and Recovery Manager ») — originaire de 2ndQuadrant/EnterpriseDB, tout aussi mature, avec un modèle opérationnel légèrement différent (un hôte de sauvegarde dédié qui tire les données depuis le(s) serveur(s) PostgreSQL qu'il gère).

Les deux sont des choix réels et standards dans les déploiements PostgreSQL de production — choisissez selon votre outillage opérationnel existant et la familiarité de votre équipe, plutôt que l'un étant strictement « meilleur » par défaut que l'autre.

Procédure de restauration PITR (aperçu)

La forme générale d'une restauration à un point dans le temps, quel que soit l'outil qui l'orchestre :

  1. Restaurer la sauvegarde de base la plus récente prise avant votre heure de récupération cible, dans un répertoire de données neuf.
  2. Configurer la cible de récupération — dans postgresql.conf (ou recovery.signal + postgresql.auto.conf selon le mécanisme exact de votre version de PostgreSQL), définissez : 
    restore_command = 'cp /archive/wal/%f %p'   # ou la commande de restauration propre à l'outil
recovery_target_time = '2026-08-21 14:30:00+00'
  3. Démarrer le serveur. PostgreSQL rejoue le WAL depuis la sauvegarde de base en avançant jusqu'à ce qu'il atteigne recovery_target_time, puis arrête la récupération et redémarre comme un primaire normal, accessible en écriture, exactement à ce point dans le temps.
  4. Vérifier l'état restauré avant de pointer backend-api vers cette instance — vérifiez que les données que vous attendez (par exemple, le dernier upload de paquet connu comme bon, la dernière décision CVE avant l'incident) sont présentes et que rien au-delà de l'heure cible ne s'est infiltré.

pgBackRest et Barman enveloppent tous deux cette séquence exacte dans une seule commande de restauration (pgbackrest restore --target=... --type=time, barman recover --target-time=...) — les étapes manuelles ci-dessus sont ce que chaque outil fait sous le capot, utile à comprendre même si vous utilisez la commande propre de l'outil en pratique.

Discipline opérationnelle

Appliquez la même discipline que la documentation de sauvegarde propre à Repod exige déjà au niveau applicatif, au niveau du serveur PostgreSQL aussi :

  • 3-2-1 : au moins 3 copies de vos données, sur 2 supports différents, avec 1 copie hors site — une archive WAL et des sauvegardes de base situées dans le même datacenter que le primaire ne constituent pas un véritable plan de reprise après sinistre, seulement une commodité de restauration locale plus rapide.
  • RPO/RTO : décidez, explicitement, de la quantité de perte de données acceptable (Recovery Point Objective, objectif de point de reprise — régie par la fréquence à laquelle vous prenez des sauvegardes de base et par le degré de continuité de l'archivage/expédition du WAL) et du temps qu'une restauration est autorisée à prendre (Recovery Time Objective, objectif de délai de reprise — régi par la taille de la sauvegarde de base, la vitesse réseau vers l'archive, et le degré d'entraînement de la procédure). Écrivez les deux chiffres ; ne les découvrez pas pour la première fois pendant un incident réel.
  • Testez les restaurations régulièrement. Une archive WAL ou une sauvegarde de base à partir de laquelle vous n'avez jamais réellement restauré est une hypothèse non vérifiée, pas une sauvegarde. Exécutez la procédure PITR ci-dessus contre un environnement de test périodiquement — exactement la même discipline de « test de restauration » que le runbook opérations de sauvegarde-restauration exige déjà pour les archives pg_dump propres à Repod.

6. Paramètres opérationnels supplémentaires à envisager

Dimensionnement du pool de connexions

Le moteur SQLAlchemy propre à Repod (backend/db/engine.py:get_engine()) est configuré avec :

pool_size=10
max_overflow=20
pool_pre_ping=True
pool_recycle=1800   # 30 minutes

C'est par processus backend-api — jusqu'à 30 connexions (10 de base + 20 en débordement) depuis une seule réplique sous charge. Ces valeurs sont codées en dur, non exposées via une variable d'environnement — il n'existe actuellement aucun réglage de type DATABASE_POOL_SIZE pour ajuster cela sans modifier directement backend/db/engine.py. Dimensionnez le max_connections de votre serveur (postgresql.conf) en tenant compte de cela, multiplié par le nombre de répliques backend-api que vous faites tourner (voir le guide HA pour les déploiements multi-répliques), plus une marge pour vos propres connexions administratives, agents de supervision, et tout pooler en amont (si vous placez PgBouncer entre backend-api et PostgreSQL pour le point de terminaison HA ci-dessus, dimensionnez max_connections en fonction du pool backend propre de PgBouncer, pas directement en fonction des 30 connexions de backend-api).

Le défaut de postgresql.conf (max_connections = 100) est un point de départ raisonnable pour un déploiement à réplique unique ; augmentez-le explicitement (et budgétez la mémoire correspondante — chaque connexion porte une réelle surcharge) pour une HA multi-répliques.

pg_stat_statements pour la supervision des requêtes

Non requis par Repod, mais un ajout standard réellement utile pour la visibilité sur ce qui est effectivement lent — l'extension évoquée dans l'affirmation « aucune extension requise » de ce code portait strictement sur ce dont Repod a besoin pour fonctionner, pas une recommandation contre l'outillage de supervision choisi par l'opérateur :

postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

Nécessite un redémarrage du serveur (c'est une entrée shared_preload_libraries, pas un paramètre rechargeable à chaud), puis :

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements
  ORDER BY total_exec_time DESC
  LIMIT 20;

Configuration du fuseau horaire

Le paramètre timezone au niveau serveur de PostgreSQL n'a pas besoin de correspondre au fuseau horaire dans lequel tournent backend-api ou son hôte. Chaque colonne d'horodatage du schéma propre à Repod est en TIMESTAMP WITH TIME ZONE (TimestampTZ = TIMESTAMP(timezone=True), backend/db/tables.py) — PostgreSQL stocke les valeurs TIMESTAMPTZ en interne, normalisées en UTC, indépendamment du paramètre timezone de la session, et ne convertit vers/depuis le fuseau horaire de cette session que pour l'affichage. Cela signifie qu'une discordance entre le fuseau horaire configuré du serveur et celui propre à Repod (orienté UTC, selon les nombreux points d'appel datetime.now(timezone.utc) répartis dans le backend) n'a aucun impact sur la correction — c'est purement cosmétique pour quiconque exécute des requêtes psql brutes directement contre le serveur. Laisser timezone à son défaut hérité de l'OS est acceptable ; il n'y a aucune raison propre à Repod de le régler sur UTC, bien que le faire soit une convention raisonnable et courante si vous souhaitez que les sessions psql affichent les heures de la même façon que l'application les raisonne.

Bases de supervision & d'alerte

Conseils PostgreSQL de production généraux, non spécifiques au schéma ou aux motifs de requêtes propres à Repod :

  • Retard de réplication (si vous faites tourner la configuration HA ci-dessus) — SELECT * FROM pg_stat_replication; sur le primaire, ou pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn() sur un secondaire. Alertez si le retard croît de façon inattendue, en particulier avant de compter sur un secondaire pour une garantie de durabilité de réplication synchrone.
  • Nombre de connexions — SELECT count(*) FROM pg_stat_activity; contre max_connections. Alertez bien avant la saturation ; un serveur PostgreSQL dont les connexions sont épuisées rejette purement et simplement les nouvelles connexions, ce qui pour Repod signifie que chaque requête ayant besoin d'une nouvelle connexion de pool commence à échouer.
  • Utilisation du disque — à la fois le répertoire de données (SELECT pg_database_size('repod');) et, séparément, l'endroit où atterrissent les archives WAL/sauvegardes de base — un volume d'archive WAL plein avec archive_mode = on et une archive_command en échec finira par empêcher le primaire de recycler les segments WAL et peut remplir son propre disque de données.
  • Activité d'autovacuum — SELECT * FROM pg_stat_user_tables; (colonnes last_autovacuum, n_dead_tup) — de la maintenance PostgreSQL standard à surveiller sur toute table avec un fort taux de churn UPDATE/DELETE (les tables manifests, inventory_cve, et celles adjacentes à l'audit sont les plus intensives en écriture de ce schéma).

Voir aussi