Guide complet de mise à niveau et migration PostgreSQL avec exemples pratiques

Les mises à niveau et migrations PostgreSQL peuvent être prévisibles, observables et réversibles lorsque vous suivez un processus discipliné. Ce guide montre comment planifier et exécuter une mise à niveau en mettant l'accent sur la sécurité, la vérification et la récupération. Vous apprendrez à inventorier les versions, la topologie et les extensions pour choisir une méthode sûre ; à choisir parmi dump/restore, pg_upgrade in-place ou migration logique selon votre tolérance aux temps d'arrêt et la taille des données ; à exécuter des procédures étape par étape avec des commandes concrètes ; à vérifier les résultats à l'aide des vues et commandes intégrées ; et à vous préparer aux modes de défaillance courants avec des chemins de restauration clairs. Commencez par un projet pilote étroit sur une copie non-production afin de mesurer les besoins en temps, espace et temps d'arrêt avant le vrai changement. Gardez votre ancienne base de données démarrable et vos sauvegardes testées jusqu'à ce que vous soyez satisfait des résultats de vérification.

Inventaire des versions et de l'environnement

Avant de sélectionner une méthode, rassemblez les faits qui contraignent votre approche.

Confirmer les versions client et serveur

Exécutez ces commandes pour capturer les versions exactes :

# Shell
psql --version
pg_config --version

# SQL (depuis psql connecté au serveur)
SELECT version();

Enregistrez à la fois la version majeure (par exemple 14, 15, 16) et le niveau de correctif mineur. Les mises à niveau mineures au sein de la même version majeure nécessitent généralement seulement un échange de binaires et un redémarrage. Les mises à niveau majeures nécessitent l'une des méthodes décrites dans ce guide.

Topologie et accès

Identifiez si vous exécutez une instance unique ou un primaire avec des répliques. Confirmez que vous disposez des privilèges superutilisateur ou suffisants pour créer des rôles, extensions et objets de réplication. Si vous utilisez du pooling de connexions (PgBouncer, PgPool) ou des équilibreurs de charge, notez leur configuration car ils auront besoin de mises à jour lors du basculement.

Extensions et compatibilité

Listez les extensions installées et leurs versions :

\dx
SELECT extname, extversion FROM pg_extension ORDER BY extname;

Enregistrez les extensions requises. Prévoyez de les faire correspondre ou de les mettre à niveau sur la cible. Si une extension n'est pas disponible sur la version cible, vous ne pouvez pas compléter une mise à niveau in-place ou logique sans alternatives. Portez une attention particulière à PostGIS, TimescaleDB, Citus et pg_partman, qui ont souvent des matrices de compatibilité spécifiques aux versions.

Taille des données et croissance

Estimez les données et les plus grandes relations pour choisir une méthode et dimensionner le stockage :

SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;

SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(format('%I.%I', schemaname, relname))) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(format('%I.%I', schemaname, relname)) DESC
LIMIT 10;

Cela aide à choisir entre dump/restore (bon pour les petits ensembles de données sous ~100 Go) et pg_upgrade ou migration logique (mieux pour les grands ensembles de données ou fenêtres serrées).

Points chauds de performance et transactions longues

Vérifiez les transactions de longue durée qui pourraient bloquer la maintenance :

SELECT pid, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE state = 'active' AND xact_start IS NOT NULL
ORDER BY xact_start;

Terminez ou attendez les transactions plus anciennes que votre seuil de fenêtre de maintenance avant de commencer.

Préparation à la réplication et WAL (pour migration logique)

Vérifiez wal_level et la capacité pour les slots et workers :

SHOW wal_level; -- doit être 'logical' pour la réplication logique
SHOW max_replication_slots;
SHOW max_wal_senders;
SHOW max_logical_replication_workers;
SHOW shared_preload_libraries;

Si wal_level n'est pas logical et que vous prévoyez la réplication logique, planifiez un redémarrage après l'avoir changé. Assurez-vous que max_replication_slots accueille au moins un slot par base de données à migrer plus une marge.

Fenêtre de maintenance et tolérance aux temps d'arrêt

Définissez les temps d'arrêt en lecture seule et en écriture acceptables. Utilisez ceci pour sélectionner une méthode dans le tableau de comparaison ci-dessous.

Comparaison des méthodes de mise à niveau

Chemin de configuration sécurisé

Sélectionnez une méthode principale basée sur la tolérance aux temps d'arrêt et la taille des données. Gardez les autres comme options de repli.

Prérequis pour toutes les méthodes

• Une sauvegarde testée de la source que vous pouvez restaurer indépendamment (pg_basebackup ou pg_dump vérifiée par pg_restore vers une instance de test).
• Espace disque libre pour le cluster cible et artefacts transitoires (taille données cible plus 30 % de marge).
• Locales et encodages correspondants sauf si vous les changez intentionnellement.
• Un environnement pilote isolé (par exemple, une copie restaurée sur un hôte ou répertoire séparé) pour pratiquer et mesurer.

Chemin A : Dump/Restore (Le plus simple, temps d'arrêt modéré)

Idéal pour les petites et moyennes bases de données ou lorsque vous voulez refactorer les objets de schéma pendant le déplacement.

1. Mettre au repos et verrouiller les écritures

Mettez l'application en mode maintenance, ou restreignez temporairement les écritures. Confirmez qu'aucune transaction d'écriture n'est active :

SELECT count(*) FROM pg_stat_activity
WHERE state <> 'idle' AND query !~* '^(COPY|SELECT)';

2. Exporter les rôles et globaux

pg_dumpall --globals-only > globals.sql

3. Exporter chaque base de données (Format personnalisé compatible restauration parallèle)

pg_dump -Fc -j 4 -d yourdb > yourdb.dump

Utilisez -j pour correspondre aux cœurs CPU pour un dump plus rapide. Pour les très grandes tables, considérez --table pour diviser les dumps.

4. Préparer le cluster cible

Initialisez la nouvelle version PostgreSQL et démarrez-la. Créez les bases de données vides et rôles requis :

psql -f globals.sql
createdb yourdb

5. Restaurer

pg_restore -j 4 -d yourdb yourdb.dump

Surveillez la progression avec pg_restore -l yourdb.dump | wc -l pour voir le nombre d'objets.

6. Maintenance post-restauration

Rafraîchissez les statistiques :

vacuumdb --all --analyze-in-stages

7. Basculement

Pointez l'application vers la nouvelle instance. Gardez l'ancienne instance en lecture seule pour une fenêtre de restauration définie (par exemple, 48 heures).

Restauration (Rollback)

Si la validation échoue avant la reprise des écritures, pointez l'application vers l'ancienne instance et reprenez le service. Comme aucune nouvelle écriture n'a eu lieu, il n'y a pas de divergence de données.

Chemin B : Mise à niveau majeure in-place avec pg_upgrade (Temps d'arrêt court)

Idéal pour les moyennes et grandes bases de données sur le même hôte où vous pouvez installer les binaires PostgreSQL anciens et nouveaux.

1. Préparer les nouveaux binaires et un nouveau répertoire de données

Installez la nouvelle version PostgreSQL à côté de l'ancienne. Initialisez un nouveau cluster avec le même encodage et locale :

initdb -D /path/to/newdatadir -E UTF8 --locale=en_US.utf8

Faites correspondre exactement la locale source. Utilisez locale -a pour lister les locales disponibles.

2. Arrêter l'ancien serveur proprement

Assurez-vous qu'aucun processus actif n'est attaché, puis arrêtez le service :

systemctl stop postgresql@14-main   # exemple pour version 14

Vérifiez avec pg_ctl status -D /path/to/olddatadir.

3. Exécuter pg_upgrade

Exemple sans liens durs (plus sûr ; plus d'espace) :

pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/16/bin \
  -d /var/lib/postgresql/14/main \
  -D /var/lib/postgresql/16/main \
  -U postgres \
  -j 4

Examinez la sortie. En cas de succès, pg_upgrade génère des scripts d'aide comme analyze_new_cluster.sh et delete_old_cluster.sh.

4. Analyser et réindexer si nécessaire

Réveillez les statistiques :

./analyze_new_cluster.sh

Si vous avez changé la collation ou traversez un changement de collation système (par exemple, mise à niveau glibc), prévoyez de réindexer les objets affectés. En cas de doute, priorisez la réindexation des index btree visibles par l'utilisateur pour les tables critiques :

REINDEX TABLE CONCURRENTLY orders;
REINDEX TABLE CONCURRENTLY customers;

5. Démarrer le nouveau serveur et vérifier

Démarrez le service en utilisant les nouveaux binaires et le nouveau répertoire de données :

systemctl start postgresql@16-main

6. Basculement

Pointez l'application vers l'instance mise à niveau.

7. Nettoyage

Après la fenêtre de restauration, supprimez l'ancien répertoire de données avec le script généré :

./delete_old_cluster.sh

Restauration (Rollback)

Si pg_upgrade échoue, l'ancien cluster reste intact ; démarrez-le et reprenez le service. Si des problèmes sont détectés après le démarrage du nouveau cluster mais avant la reprise des écritures, arrêtez le nouveau serveur et redémarrez l'ancien serveur.

Notes

Le drapeau -k peut réduire le temps et l'espace en créant des liens durs, mais il lie les anciens et nouveaux répertoires de données au même stockage. Préférez le mode copie par défaut pour des frontières de restauration plus claires.

Chemin C : Migration logique avec publications/abonnements (Temps d'arrêt minimal)

Idéal lorsque vous devez maintenir les lectures et écritures en ligne et pouvez gérer un basculement bref.

Hypothèses

• Les versions source et cible supportent la réplication logique intégrée (PostgreSQL 10+).
• wal_level=logical sur la source, et vous avez la capacité pour au moins un slot de réplication.

1. Préparer le cluster cible

Initialisez et démarrez la nouvelle version. Créez les rôles et bases de données vides requis pour la migration. Installez les extensions requises sur la cible avant de créer l'abonnement.

2. Assurer les paramètres source (Redémarrer la source si vous les changez)

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_logical_replication_workers = 4;
-- Recharger ou redémarrer pour appliquer
SELECT pg_reload_conf(); -- nécessite redémarrage pour wal_level

3. Créer une publication sur la source

Pour toutes les tables utilisateur dans une base de données :

-- Se connecter à la base de données source
GRANT USAGE ON SCHEMA public TO PUBLIC; -- assurer l'accès à la cible selon besoins
CREATE PUBLICATION pub_all FOR ALL TABLES;

Si vous avez besoin d'un contrôle plus fin, publiez les tables sélectionnées :

CREATE PUBLICATION pub_core FOR TABLE orders, customers, products;

4. Créer un abonnement sur la cible

Remplacez les paramètres de connexion selon le cas :

-- Sur la base de données cible
CREATE SUBSCRIPTION sub_all
  CONNECTION 'host=SOURCE_HOST port=5432 dbname=DB user=REPL_USER password=SECRET'
  PUBLICATION pub_all
  WITH (copy_data = true, create_slot = true, enabled = true);

Cela copie les données existantes et commence le streaming des changements. Utilisez copy_data = false si vous chargerez un instantané initial via pg_dump/pg_restore et ne voulez que la capture de changements.

5. Surveiller la synchronisation

SELECT subname, relid::regclass AS table, status,
       last_msg_send_time, last_msg_receipt_time,
       bytes_lag
FROM pg_stat_subscription;

Attendez que la synchronisation initiale des tables se termine (status = 'r' pour ready) et que le retard de réplication reste proche de zéro.

6. Gérer les séquences avant le basculement

La réplication logique ne maintient pas automatiquement les valeurs de séquences synchronisées. Juste avant le basculement, rafraîchissez les séquences sur la cible en utilisant les valeurs de la source. Une approche pratique : extraire les valeurs de la source et appliquer avec un script qui exécute setval sur la cible.

# Sur source : générer commandes setval
psql -d DB -Atc "
SELECT format('SELECT setval(%L, %s, true);', sequence_schema||'.'||sequence_name, last_value)
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('pg_catalog', 'information_schema');
" > sync_sequences.sql

# Sur cible : appliquer
psql -d DB -f sync_sequences.sql

7. Basculement

Mettez au repos les écritures sur la source (mode maintenance application ou restriction niveau base de données) :

ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();

Attendez que toutes les transactions d'écriture sur la source se terminent :

SELECT count(*) FROM pg_stat_activity
WHERE state <> 'idle' AND query ~* 'INSERT|UPDATE|DELETE|MERGE';

Sur la cible, attendez que la relecture rattrape son retard :

SELECT pg_sleep(1)
FROM generate_series(1,30)
WHERE (SELECT COALESCE(SUM(bytes_lag),0) FROM pg_stat_subscription) = 0;

Pointez l'application vers la cible et reprenez le trafic.

8. Décommissionner la réplication

Quand vous êtes confiant, supprimez l'abonnement sur la cible et la publication sur la source :

DROP SUBSCRIPTION sub_all;
-- Sur source une fois la fenêtre de restauration terminée
DROP PUBLICATION pub_all;

Restauration (Rollback)

Si vous détectez des problèmes avant d'activer les écritures sur la cible, gardez le trafic sur la source et supprimez l'abonnement, ou laissez-le pour réessayer plus tard. Après l'activation des écritures sur la cible, le retour vers la source nécessite un plan de réplication inverse ou restauration depuis sauvegarde. Évitez les changements destructeurs sur la source jusqu'à la finalisation de l'acceptation.

Vérification et diagnostics

La vérification prouve que la nouvelle instance est correcte et saine. Exécutez ces vérifications avant de déclarer le succès.

Vérifications de correction principales

• Version serveur et paramètres :

SELECT version();
SHOW server_version_num;
SHOW data_directory;

• Extensions installées et versions :

\dx

• Différences de schéma (dumps schema-only) :

pg_dump --schema-only -d source_db > source_schema.sql
pg_dump --schema-only -d target_db > target_schema.sql
diff -u source_schema.sql target_schema.sql

Examinez les différences. Les différences attendues incluent les objets système spécifiques aux versions.

• Comptages de lignes pour tables clés (vérifications ponctuelles) :

SELECT 'orders' AS table, count(*) FROM orders
UNION ALL
SELECT 'customers', count(*) FROM customers
UNION ALL
SELECT 'products', count(*) FROM products;

Pour les grandes tables, utilisez pg_class.reltuples pour une estimation rapide :

SELECT relname, reltuples::bigint AS est_rows
FROM pg_class
WHERE relname IN ('orders', 'customers', 'products');

Préparation aux performances

• Analyser pour rafraîchir les statistiques :

vacuumdb --all --analyze-in-stages

• Vérifier les requêtes lentes après mise à niveau et comparer les plans :

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 12345;

Comparez avec les plans de la source si vous les avez capturés pendant le pilote.

• Surveiller les tâches d'arrière-plan et l'activité autovacuum :

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Santé de la réplication (Migration logique)

• Statut d'abonnement et retard :

SELECT * FROM pg_stat_subscription;

• Les conflits ou erreurs d'application sont rapportés dans les journaux serveur ; maintenez le niveau de journal au moins WARNING pendant la migration.

Tableau récapitulatif de vérification

Modes de défaillance et récupération

Anticipez où les choses cassent et comment récupérer sans perte de données.

Problèmes courants par méthode

Dump/Restore

• Échec : Erreurs de restauration dues à des extensions manquantes.
• Action : Installer ou supprimer/remplacer l'extension ; relancer la restauration.
• Échec : Incompatibilités de rôles ou propriétaires.
• Action : Restaurer les globaux d'abord ; mapper les propriétaires avec pg_restore --no-owner et corriger ensuite avec GRANT/ALTER OWNERSHIP.
• Restauration : Si vous n'avez pas repris les écritures, continuez d'utiliser la source et réessayez plus tard.

pg_upgrade

• Échec : Incompatibilité binaire ou mauvais chemins.
• Action : Relancer avec les bons drapeaux -b/-B et répertoires de données ; vérifier que les deux versions sont accessibles.
• Échec : Extension incompatible laissée installée.
• Action : Supprimer ou mettre à niveau l'extension sur la source avant de réessayer ; consulter les notes de version de l'extension.
• Échec : Erreurs d'index liées à la collation.
• Action : Réindexer les objets affectés sur la cible après mise à niveau ; tester sur pilote pour identifier lesquels.
• Restauration : Démarrer l'ancien cluster ; restaurer la configuration de service originale.

Migration logique

• Échec : Erreurs de worker de réplication sur changements DDL pendant la synchronisation.
• Action : Geler le schéma pendant la synchronisation initiale ; si DDL requis, appliquer DDL compatible des deux côtés dans l'ordre.
• Échec : Retard de réplication persistant.
• Action : Augmenter les ressources pour workers d'application ; assurer la stabilité réseau ; éviter les transactions longues sur la source ; vérifier max_logical_replication_workers.
• Échec : Séquences hors synchronisation après basculement.
• Action : Exécuter un script de synchronisation séquences utilisant setval juste avant d'activer les écritures sur la cible.
• Restauration : Avant d'activer les écritures sur la cible, garder le trafic sur la source et supprimer l'abonnement. Après activation des écritures, la restauration nécessite une réplication inverse ou un plan de restauration.

Pratiques générales de récupération

• Gardez des sauvegardes vérifiées et un instantané testé de restauration depuis immédiatement avant la mise à niveau.
• Ne supprimez pas l'ancien cluster ou ne modifiez pas ses données jusqu'à la fin de la fenêtre de restauration.
• Gardez les chaînes de connexion, fichiers de service et règles pare-feu prêtes pour basculer rapidement.
• Maintenez un runbook avec toutes les commandes et les versions exactes utilisées.

Liste de contrôle opérationnelle

Utilisez cette liste pour exécuter un pilote puis la production. Ajustez les durées à votre environnement.

Prévol (1-2 jours avant)

• [ ] Prendre et vérifier une sauvegarde fraîche (restaurer vers une instance de test).
• [ ] Inventorier versions, extensions, taille données et plus grandes tables.
• [ ] Choisir la méthode : dump/restore, pg_upgrade ou logique.
• [ ] Préparer les binaires cible et cluster vide.
• [ ] Confirmer l'espace disque (cible + 30 % marge).
• [ ] Préparer la fenêtre de maintenance et plan de communication.

Pilote sur une copie

• [ ] Répéter la méthode choisie de bout en bout sur une copie restaurée.
• [ ] Mesurer le temps écoulé pour chaque étape et stockage requis.
• [ ] Documenter les ajustements d'extension ou collation.
• [ ] Pratiquer les étapes de vérification et enregistrer les sorties attendues.

Jour d'exécution

• [ ] Annoncer le démarrage ; mettre les apps en maintenance ou lecture seule selon besoin.
• [ ] S'assurer qu'aucune transaction d'écriture longue sur la source.
• [ ] Exécuter les étapes de migration sélectionnées.
• [ ] Exécuter la vérification : version, extensions, diffs schéma, comptages lignes, stats.
• [ ] Pour migration logique, confirmer retard zéro avant basculement.
• [ ] Activer l'application contre la cible.

Critères de restauration

• [ ] Erreurs ou incompatibilités prédéfinies déclenchant la restauration (exemples : diff schéma sur tables critiques, incohérences séquences, erreurs d'application réplication).
• [ ] Si restauration, redémarrer l'ancienne instance et rétablir les chaînes de connexion.

Post-mise à niveau

• [ ] Surveiller requêtes lentes et CPU/IO ; exécuter EXPLAIN ANALYZE ciblé sur chemins clés.
• [ ] Compléter analyse et toute réindexation requise.
• [ ] Garder l'ancienne instance en lecture seule et démarrer le minuteur de décommission (par exemple, 48 heures).
• [ ] Supprimer publication/abonnement ou ancien répertoire de données après acceptation.

Conclusion

Une mise à niveau PostgreSQL sécurisée repose sur l'inventaire, une méthode soigneusement choisie, une vérification explicite et un chemin de restauration clair. Commencez par un pilote étroit pour mesurer l'espace, le temps et les temps d'arrêt. Pour les petites bases de données, dump/restore est simple et résilient. Pour les grandes bases de données avec fenêtres serrées, pg_upgrade in-place est rapide et préserve les fichiers. Lorsque les temps d'arrêt doivent être minimaux, la migration logique permet de copier et rattraper avant un court basculement. Quel que soit le chemin choisi, gardez l'ancienne instance exécutable jusqu'à ce que les vérifications passent et que votre équipe soit confiante. Capturez les commandes exactes et les chronométrages de votre pilote pour prédire le comportement en production. Avec ces étapes, vous pouvez mettre à niveau de manière prévisible, valider les résultats et récupérer en toute sécurité si quelque chose tourne mal.