Mise à niveau PostgreSQL sans interruption

Cet article explique comment passer une base PostgreSQL de production à une nouvelle version majeure pendant qu'elle continue de servir le trafic. La logical replication fait tourner l'ancienne et la nouvelle version en parallèle jusqu'au basculement, ce qui évite la fenêtre de maintenance qu'exige normalement un pg_upgrade sur place. Les seules interruptions sont un redémarrage planifié de l'ancien primaire pour activer le décodage logique, et une pause des écritures de quelques secondes au moment du basculement.

Pourquoi pg_upgrade sur place impose une interruption

pg_upgrade, même en mode rapide --link, pose un verrou exclusif sur l'ensemble du cluster pendant toute la conversion du catalogue : indisponible depuis le lancement jusqu'à la fin de la vérification et du redémarrage. Cette durée dépend de la complexité du catalogue, extensions, nombre de relations, pas du volume de données, ce qui explique pourquoi l'estimation « 20 minutes » n'est pas fiable. Pire : si la montée de version échoue en cours de route, votre plan de repli consiste à restaurer depuis une sauvegarde, à l'heure où ça échoue.

La logical replication contourne ce problème : vous construisez une réplique sur la nouvelle version à partir de zéro, vous répliquez dedans pendant que l'ancien primaire continue de servir le trafic, et vous redirigez l'application une fois la nouvelle version prouvée à jour. L'ancien primaire est mis au repos ensuite, à votre rythme.

Vérifications préalables sur l'ancien primaire

Deux conditions doivent être remplies sur l'ancien primaire (PostgreSQL 14 dans cet exemple) avant toute chose.

wal_level doit valoir logical. Sur PostgreSQL 14, ce changement demande un redémarrage. Planifiez-en un court dans une fenêtre calme, quelques jours avant la migration :

-- Sur l'ANCIEN primaire (PostgreSQL 14)
SHOW wal_level; -- doit valoir 'logical', pas 'replica'
 
ALTER SYSTEM SET wal_level = 'logical';
-- Plafonne le WAL qu'un slot de réplication non consommé peut retenir (PostgreSQL 13+),
-- pour qu'un slot bloqué ou oublié ne remplisse pas le disque
ALTER SYSTEM SET max_slot_wal_keep_size = '100GB';
-- Puis redémarrez PostgreSQL (par ex. systemctl restart postgresql) et revérifiez SHOW wal_level.

Chaque table publiée doit avoir une replica identity. Avec FOR ALL TABLES, une table sans clé primaire ni REPLICA IDENTITY réplique toujours ses insertions, mais les UPDATE et DELETE sur cette table se mettent à échouer sur le primaire de production dès que la publication existe. Trouvez ces tables d'abord :

-- Tables dont les UPDATE/DELETE casseraient une fois publiées
SELECT c.oid::regclass AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (
    c.relreplident = 'n'
    OR (c.relreplident = 'd'
        AND NOT EXISTS (SELECT 1 FROM pg_index i
                        WHERE i.indrelid = c.oid AND i.indisprimary))
  );

Corrigez chacune avant de créer la publication. Ajoutez une clé primaire quand c'est possible, sinon rabattez-vous sur ALTER TABLE public.audit_log REPLICA IDENTITY FULL;. FULL journalise l'ancienne ligne complète à chaque mise à jour et suppression, ce qui peut ralentir leur application côté subscriber sur de grosses tables. Utilisez-le avec parcimonie.

Mettre en place la réplique sur la nouvelle version

Provisionnez l'instance de la nouvelle version majeure (disons PostgreSQL 17) comme un cluster séparé, pas une copie de l'ancien passée par pg_upgrade. Les rôles sont des objets globaux que pg_dump n'inclut pas. Copiez-les d'abord avec pg_dumpall --globals-only depuis l'ancien serveur.

L'ordre des étapes suivantes compte, car une erreur fait perdre des données sans le moindre message. La copie initiale et le flux de réplication doivent partir exactement du même point. Créez d'abord le slot de réplication, faites-lui exporter un snapshot, puis faites le dump depuis ce snapshot. Si vous faites le dump avant de créer le slot, toute écriture validée entre les deux n'est ni dans le dump ni dans le flux, et rien ne vous le signalera.

-- 1. Sur l'ANCIEN primaire : la publication doit exister avant la création du slot
CREATE PUBLICATION app_upgrade_pub FOR ALL TABLES;

# 2. Terminal 1 : ouvrez une connexion de RÉPLICATION vers l'ancien primaire et créez le slot.
#    Laissez cette session ouverte et inactive jusqu'à la fin du dump : le snapshot exporté
#    ne reste valide que tant que cette session est connectée et n'exécute aucune autre commande.
psql "host=old-primary.internal dbname=app user=replicator replication=database sslmode=verify-full"
 
app=> CREATE_REPLICATION_SLOT app_upgrade_slot LOGICAL pgoutput EXPORT_SNAPSHOT;
    slot_name     | consistent_point |    snapshot_name    | output_plugin
------------------+------------------+---------------------+---------------
 app_upgrade_slot | 5A/3C0012F8      | 00000003-000001B2-1 | pgoutput

# 3. Terminal 2, pendant que le terminal 1 reste ouvert : dump de ce snapshot précis.
#    Utilisez les binaires pg_dump/pg_restore de la NOUVELLE version.
pg_dump "host=old-primary.internal dbname=app user=migrator sslmode=verify-full" \
  --snapshot=00000003-000001B2-1 \
  --no-publications --no-subscriptions \
  -Fd -j 8 -f /backup/app.dump
 
# 4. Une fois pg_dump terminé, le terminal 1 peut être fermé (le slot est permanent).
#    Restauration sur le nouveau serveur :
pg_restore -d "host=new-primary.internal dbname=app user=migrator sslmode=verify-full" \
  -j 8 /backup/app.dump

-- 5. Sur le NOUVEAU primaire (PostgreSQL 17), en superutilisateur : rattachement au slot EXISTANT.
--    Le mot de passe vit dans le passfile (appartenant à l'utilisateur système postgres, chmod 600), pas dans le catalogue.
CREATE SUBSCRIPTION app_upgrade_sub
  CONNECTION 'host=old-primary.internal port=5432 dbname=app user=replicator sslmode=verify-full sslrootcert=/etc/postgresql/tls/ca.crt passfile=/var/lib/postgresql/.pgpass'
  PUBLICATION app_upgrade_pub
  WITH (copy_data = false, create_slot = false, slot_name = 'app_upgrade_slot');

copy_data = false n'est correct ici que parce que les données viennent du snapshot du slot lui-même. Le slot transmet ensuite chaque changement validé après ce snapshot, donc rien n'est manqué ni appliqué deux fois. Dès que le slot existe, l'ancien primaire conserve du WAL pour lui. Ne laissez pas traîner l'étape dump/restauration et surveillez l'espace disque.

L'alternative plus simple consiste à ne restaurer que le schéma (pg_dump --schema-only), puis à créer la subscription avec les valeurs par défaut (copy_data = true, create_slot = true). PostgreSQL crée alors le slot et fait lui-même une copie initiale cohérente, table par table. C'est plus lent sur de grosses bases et vous laisse moins de contrôle sur la copie, mais il n'y a aucun snapshot à gérer.

Attention aussi au DDL, car la logical replication ne réplique pas les changements de schéma. Si vous livrez une migration pendant que la subscription tourne, appliquez-la à la main des deux côtés et gardez le schéma du subscriber identique à celui du publisher, ou plus large. Le subscriber doit posséder toutes les colonnes que le publisher envoie. Une nouvelle colonne s'ajoute donc d'abord côté subscriber, puis côté publisher, et une colonne supprimée suit l'ordre inverse.

Surveiller le lag de réplication jusqu'à rattrapage complet

Une subscription qui affiche active ne vous dit pas grand-chose, regardez plutôt les chiffres de lag. Exécutez ceci sur l'ancien primaire pour voir de combien la nouvelle réplique est en retard, en octets comme en temps :

SELECT
  s.slot_name,
  s.active,
  pg_current_wal_lsn() AS publisher_lsn,
  s.confirmed_flush_lsn AS subscriber_confirmed_lsn,
  pg_wal_lsn_diff(pg_current_wal_lsn(), s.confirmed_flush_lsn) AS lag_bytes,
  r.write_lag,
  r.flush_lag,
  r.replay_lag
FROM pg_replication_slots s
LEFT JOIN pg_stat_replication r ON r.pid = s.active_pid
WHERE s.slot_name = 'app_upgrade_slot';

lag_bytes correspond au WAL que le subscriber n'a pas encore confirmé. replay_lag indique le temps qu'il a fallu récemment pour appliquer un changement côté subscriber (il vaut NULL quand il n'y a rien à répliquer). Côté subscriber, pg_stat_subscription donne la vue correspondante, où received_lsn, latest_end_lsn et latest_end_time indiquent ce qu'il a reçu et ce qu'il a confirmé en dernier au publisher. Sous charge d'écriture normale, lag_bytes doit tendre vers zéro ou vers un chiffre faible et stable. S'il oscille sans jamais converger, le matériel de la nouvelle réplique ne suit probablement pas le débit d'écriture.

C'est pour ça que je fais tourner cette requête en boucle pendant au moins 24 à 48 heures, sur un cycle complet de pic de trafic : une subscription à jour un mardi après-midi calme ne dit rien de sa tenue sous charge réelle.

La séquence de basculement

Une fois le lag resté à zéro ou presque sur un cycle complet de trafic, le basculement lui-même devrait prendre quelques secondes. Deux détails comptent ici. L'ancien primaire passe en lecture seule pour le rôle applicatif, ce qui évite de devoir supposer que toutes les écritures se sont arrêtées. Et la réplication inverse (du nouveau vers l'ancien) est en place avant que la moindre écriture n'arrive sur le nouveau primaire, sans quoi le retour arrière ne fonctionnerait pas.

#!/usr/bin/env bash
# À lancer avec un rôle d'administration (pas le rôle applicatif) ; mots de passe dans ~/.pgpass.
set -Eeuo pipefail
 
OLD="host=old-primary.internal dbname=app user=admin sslmode=verify-full"
NEW="host=new-primary.internal dbname=app user=admin sslmode=verify-full"
APP_ROLE="app_rw"
APP_CONN_STRING_SECRET="projects/prod/secrets/app-db-conn"
 
abort() {
  echo "ABANDON : $1. Ancien primaire remis en lecture-écriture." >&2
  psql "$OLD" -c "ALTER ROLE $APP_ROLE RESET default_transaction_read_only;"
  kubectl -n app scale deployment app-writer --replicas=3
  exit 1
}
 
echo "[1/7] Mise en pause des écritures applicatives..."
kubectl -n app scale deployment app-writer --replicas=0
 
echo "[2/7] Passage de l'ancien primaire en lecture seule pour l'application et purge des écritures..."
psql "$OLD" -c "ALTER ROLE $APP_ROLE SET default_transaction_read_only = on;"
# Les sessions existantes gardent l'ancien réglage : on les déconnecte, elles se reconnectent en lecture seule
psql "$OLD" -Atc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
                  WHERE usename = '$APP_ROLE' AND backend_type = 'client backend';" >/dev/null
WRITERS=1
for _ in $(seq 1 30); do
  # Une session qui détient un identifiant de transaction a écrit quelque chose de non terminé
  WRITERS=$(psql "$OLD" -Atc "SELECT count(*) FROM pg_stat_activity
                               WHERE backend_type = 'client backend' AND backend_xid IS NOT NULL
                                 AND pid <> pg_backend_pid();")
  [ "$WRITERS" = "0" ] && break
  sleep 1
done
[ "$WRITERS" = "0" ] || abort "$WRITERS sessions écrivent encore après 30 s"
 
echo "[3/7] Attente d'un lag de réplication nul..."
LAG=1
for _ in $(seq 1 60); do
  LAG=$(psql "$OLD" -Atc "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
                          FROM pg_replication_slots WHERE slot_name = 'app_upgrade_slot';")
  [ "${LAG:-1}" = "0" ] && break
  sleep 1
done
[ "${LAG:-1}" = "0" ] || abort "le lag est encore de ${LAG:-inconnu} octets"
 
echo "[4/7] Réconciliation des séquences sur le nouveau primaire (+1000 de marge)..."
psql "$OLD" -Atc "SELECT format('SELECT setval(%L, %s);',
                    quote_ident(schemaname) || '.' || quote_ident(sequencename), last_value + 1000)
                  FROM pg_sequences WHERE last_value IS NOT NULL;" > ./sync-sequences.sql
psql "$NEW" -v ON_ERROR_STOP=1 -f ./sync-sequences.sql
 
echo "[5/7] Inversion du sens de réplication : nouveau -> ancien, pour le retour arrière..."
# Supprimer la subscription supprime aussi app_upgrade_slot sur l'ancien primaire
psql "$NEW" -v ON_ERROR_STOP=1 -c "DROP SUBSCRIPTION app_upgrade_sub;"
psql "$NEW" -v ON_ERROR_STOP=1 -c "CREATE PUBLICATION app_rollback_pub FOR ALL TABLES;"
psql "$OLD" -v ON_ERROR_STOP=1 -c "CREATE SUBSCRIPTION app_rollback_sub
  CONNECTION 'host=new-primary.internal port=5432 dbname=app user=replicator sslmode=verify-full sslrootcert=/etc/postgresql/tls/ca.crt passfile=/var/lib/postgresql/.pgpass'
  PUBLICATION app_rollback_pub
  WITH (copy_data = false, slot_name = 'app_rollback_slot');"
 
echo "[6/7] Redirection de la chaîne de connexion applicative vers le nouveau primaire..."
gcloud secrets versions add "$APP_CONN_STRING_SECRET" --data-file=./new-primary-conn.txt
 
echo "[7/7] Reprise des écritures applicatives contre le nouveau primaire..."
kubectl -n app scale deployment app-writer --replicas=3

Les étapes 2 et 3 servent de garde-fous. Si l'une échoue, le script s'arrête, remet l'ancien primaire en lecture-écriture et relance les écritures sur lui, sans toucher au nouveau serveur. copy_data = false à l'étape 5 ne pose pas de problème parce que rien n'a encore été écrit sur le nouveau primaire, et le slot créé part de la position courante.

Les équipes qui n'ont pas répété la redirection DNS/chaîne de connexion de l'étape 6 y perdent souvent du temps. Les causes habituelles sont un TTL trop long, un connection pooler (PgBouncer, RDS Proxy) qui garde l'ancien hôte en cache, ou une application qui ne se reconnecte pas proprement après un changement de configuration. Répétez cette redirection sur une cible jetable en amont, séparément du test de la migration.

Plan de repli

L'avantage par rapport à un pg_upgrade sur place, c'est que l'ancien primaire existe toujours juste après le basculement, et grâce à app_rollback_sub il continue de recevoir chaque écriture acceptée par le nouveau primaire. Ne le décommissionnez pas le jour même. Gardez-le actif pendant au moins un cycle métier complet, assez longtemps pour voir passer les pics de charge et les traitements batch hebdomadaires.

Si la nouvelle version se comporte mal après le basculement (une régression du planificateur de requêtes, une incompatibilité d'extension, un client qui n'apprécie pas un changement de protocole réseau), le retour arrière reprend la même séquence avec les rôles inversés. On met les écritures en pause, on passe le rôle applicatif en lecture seule sur le nouveau primaire, on attend un lag nul sur app_rollback_slot côté nouveau primaire, on réconcilie les séquences du nouveau vers l'ancien, on réinitialise default_transaction_read_only pour le rôle applicatif sur l'ancien primaire, on redirige, puis on reprend les écritures. Aucune écriture n'est perdue et aucune sauvegarde n'entre en jeu. Répétez-le une fois en staging pour que ce chemin de repli soit testé lui aussi.

Conclusion

Un basculement de 30 secondes et une interruption de quatre heures peuvent porter sur la même migration, vers la même version. Ce qui les sépare, c'est que l'ancienne et la nouvelle base aient tourné en parallèle assez longtemps pour prouver que la nouvelle fonctionne avant de vous y engager. pg_upgrade sur place vous fait sauter cette étape et vous la refacture plus tard, en interruption de service, à un moment que vous ne choisissez pas.