🔝 Retour au Sommaire
Lors de la mise à jour d'une base de données PostgreSQL vers une version majeure, l'outil pg_upgrade est votre meilleur allié. PostgreSQL 18 apporte des améliorations significatives à cet outil, notamment la préservation des statistiques, une fonctionnalité qui simplifie grandement les migrations.
PostgreSQL utilise un système de versionnage où les versions majeures (comme 16, 17, 18) peuvent introduire des changements dans le format de stockage des données. Contrairement aux mises à jour mineures (comme 18.0 → 18.1), une mise à jour majeure nécessite une migration complète des données.
Traditionnellement, pour migrer vers une version majeure, il fallait :
- Exporter toutes les données avec
pg_dump(sauvegarde logique) - Installer la nouvelle version de PostgreSQL
- Importer toutes les données avec
pg_restore
Problèmes : Cette approche était très lente pour les bases volumineuses. Une base de 500 Go pouvait nécessiter plusieurs heures, voire jours, d'indisponibilité.
pg_upgrade est un outil spécialisé qui permet de migrer une instance PostgreSQL vers une version majeure supérieure de manière beaucoup plus rapide. Au lieu de recréer toutes les données, il utilise des techniques intelligentes pour réutiliser les fichiers existants.
Avantages :
- ⚡ Migration jusqu'à 10× plus rapide qu'une sauvegarde/restauration
- 🔄 Minimise le temps d'indisponibilité
- 💾 Peut fonctionner en mode "lien" pour économiser l'espace disque
pg_upgrade effectue les opérations suivantes :
- Vérification de compatibilité : S'assure que la migration est possible
- Création du nouveau cluster : Installe la structure de la nouvelle version
- Migration du schéma : Recrée les tables, index, contraintes, etc.
- Migration des données :
- Mode
--copy: Copie physiquement les fichiers de données - Mode
--link: Crée des liens physiques (hardlinks) vers les fichiers existants
- Mode
- Mise à jour des métadonnées : Adapte les catalogues système
┌─────────────────────────────────────────────────────────────┐
│ Ancien Cluster (PostgreSQL 17) │
│ /var/lib/postgresql/17/main │
│ │
│ ┌──────────┐ ┌──────────┐ ┌────────────┐ │
│ │ Tables │ │ Index │ │Statistiques│ │
│ │ (data) │ │ (data) │ │ (pg_stats)│ │
│ └──────────┘ └──────────┘ └────────────┘ │
└─────────────────────────────────────────────────────────────┘
│
│ pg_upgrade
▼
┌─────────────────────────────────────────────────────────────┐
│ Nouveau Cluster (PostgreSQL 18) │
│ /var/lib/postgresql/18/main │
│ │
│ ┌──────────┐ ┌──────────┐ ┌────────────┐ │
│ │ Tables │ │ Index │ │Statistiques│ ← PRÉSERVÉES ! │
│ │ (data) │ │ (data) │ │ (pg_stats)│ │
│ └──────────┘ └──────────┘ └────────────┘ │
└─────────────────────────────────────────────────────────────┘
Les statistiques sont des informations cruciales que PostgreSQL collecte sur vos données pour optimiser l'exécution des requêtes.
-
Statistiques de colonnes :
- Nombre de valeurs distinctes (cardinalité)
- Distribution des valeurs (histogrammes)
- Valeurs les plus fréquentes (most common values - MCV)
- Corrélation entre l'ordre physique et l'ordre logique
-
Statistiques de tables :
- Nombre de lignes (tuples)
- Nombre de pages (blocs de données)
- Taux de remplissage
- Dernière date d'ANALYZE
-
Statistiques d'index :
- Taille des index
- Nombre de pages parcourues
Imaginons une table commandes avec 10 millions de lignes :
-- PostgreSQL collecte ces informations :
- n_distinct (produit_id) : 5000 produits différents
- n_distinct (client_id) : 200 000 clients différents
- most_common_vals (statut) : ['livré': 60%, 'en_cours': 25%, 'annulé': 15%]
- correlation (date_commande) : 0.95 (fortement corrélé à l'ordre physique)Ces statistiques permettent au planificateur de requêtes de prendre des décisions intelligentes.
Le planificateur de requêtes (query planner) est le "cerveau" de PostgreSQL. Avant d'exécuter une requête, il :
- Analyse la requête SQL
- Explore les différentes stratégies possibles (utiliser un index ? faire un scan séquentiel ?)
- Estime les coûts de chaque stratégie grâce aux statistiques
- Choisit le plan d'exécution optimal
SELECT * FROM commandes WHERE statut = 'annulé';Avec statistiques : Le planificateur sait que seulement 15% des commandes sont annulées (1,5 million de lignes). Il choisira probablement un index sur statut.
Sans statistiques : Le planificateur ne sait pas. Il pourrait faire une mauvaise estimation et choisir un scan séquentiel complet (plus lent).
Lors d'une migration avec pg_upgrade avant PostgreSQL 18 :
- ✅ Les données sont migrées (tables, index)
- ✅ Le schéma est migré (structure des tables)
- ❌ Les statistiques sont perdues
Conséquence : Après la migration, la nouvelle base ne possède aucune statistique sur les données.
┌────────────────────────────────────────────────────────────┐
│ État juste après pg_upgrade (PG ≤ 17) │
├────────────────────────────────────────────────────────────┤
│ │
│ ⚠️ Statistiques : AUCUNE │
│ │
│ Le planificateur est "aveugle" : │
│ - Estime toutes les tables à 1000 lignes par défaut │
│ - Ne connaît pas la distribution des valeurs │
│ - Fait des choix d'optimisation aléatoires │
│ │
│ Résultat : Performances dégradées de 10× à 100× │
│ │
└────────────────────────────────────────────────────────────┘
-- Avant migration (avec statistiques) : 50ms
SELECT *
FROM commandes c
JOIN clients cl ON c.client_id = cl.id
WHERE c.date_commande >= '2024-01-01';
-- Après migration PG ≤ 17 (sans statistiques) : 5000ms
-- Le planificateur choisit un mauvais plan d'exécution !Après la migration, il fallait manuellement :
# Lancer ANALYZE sur toute la base
psql -d ma_base -c "ANALYZE VERBOSE;"Problèmes :
- ⏰ Peut prendre des heures sur de grosses bases (plusieurs To)
- 📉 Pendant ce temps, les performances sont catastrophiques
- 🔥 Crée une charge supplémentaire sur la base fraîchement migrée
- 😰 Stress pour les équipes : "La migration est finie, mais tout est lent !"
PostgreSQL 18 introduit un mécanisme de préservation des statistiques lors de pg_upgrade :
-
Avant la migration :
pg_upgradeexporte les statistiques de l'ancien cluster- Format interne optimisé pour la réimportation
-
Pendant la migration :
- Les données et le schéma sont migrés comme avant
- Les statistiques sont également transférées
-
Après la migration :
- Les statistiques sont immédiatement disponibles
- Le planificateur fonctionne à pleine capacité dès le redémarrage
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL ≤ 17 │
├─────────────────────────────────────────────────────────────┤
│ 1. pg_upgrade → 2 heures │
│ 2. Redémarrage → 2 minutes │
│ 3. ❌ Performances LENTES → Jusqu'au ANALYZE │
│ 4. ANALYZE VERBOSE → 6 heures │
│ 5. ✅ Performances normales │
│ │
│ Total downtime effectif : ~8 heures │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ PostgreSQL 18 (avec préservation) │
├─────────────────────────────────────────────────────────────┤
│ 1. pg_upgrade → 2 heures │
│ 2. Redémarrage → 2 minutes │
│ 3. ✅ Performances NORMALES → Immédiatement ! │
│ │
│ Total downtime effectif : ~2 heures │
└─────────────────────────────────────────────────────────────┘
- 🚀 Performances immédiates : Plus de période de "chauffe"
- ⏱️ Temps de migration réduit : Pas besoin d'ANALYZE post-migration
- 💰 Économies : Moins de downtime = moins de coûts
- 😌 Sérénité : Migration plus prévisible
Les statistiques PostgreSQL sont conservées dans plusieurs tables système :
-
pg_statistic : Table interne (catalogue système)
- Contient les statistiques brutes
- Non lisible directement par l'utilisateur
-
pg_stats : Vue publique
- Version "lisible" de pg_statistic
- Accessible pour consultation
-
pg_class : Métadonnées des relations
- Nombre de tuples (reltuples)
- Nombre de pages (relpages)
┌────────────────────────────────────────────────────────────┐
│ Phase 1 : Extraction (ancien cluster PG 17) │
├────────────────────────────────────────────────────────────┤
│ │
│ pg_upgrade lit : │
│ • pg_statistic (statistiques détaillées) │
│ • pg_class (métadonnées tables) │
│ • pg_attribute (métadonnées colonnes) │
│ │
│ → Sérialisation dans un format intermédiaire │
│ → Fichier temporaire : pg_upgrade_stats.dump │
│ │
└────────────────────────────────────────────────────────────┘
↓
┌────────────────────────────────────────────────────────────┐
│ Phase 2 : Import (nouveau cluster PG 18) │
├────────────────────────────────────────────────────────────┤
│ │
│ pg_upgrade restaure : │
│ • Recrée les entrées dans pg_statistic │
│ • Met à jour pg_class avec reltuples/relpages │
│ • Adapte au format interne de PG 18 si nécessaire │
│ │
│ → Statistiques immédiatement exploitables │
│ │
└────────────────────────────────────────────────────────────┘
PostgreSQL 18 gère intelligemment les cas limites :
PG 11 → PG 18 : Certaines statistiques peuvent être obsolètes
→ pg_upgrade préserve ce qui est compatible
→ Log des statistiques non transférables
→ Recommande un ANALYZE ciblé sur les objets concernés
Si PG 18 collecte de nouveaux types de statistiques :
→ Les anciennes sont préservées
→ Les nouvelles seront collectées au prochain ANALYZE
→ Pas de dégradation de performance
Si des statistiques sont détectées comme invalides :
→ pg_upgrade les ignore
→ Log d'avertissement
→ Ces tables nécessiteront un ANALYZE manuel
La préservation des statistiques est automatique et activée par défaut dans PostgreSQL 18. Aucune option spéciale n'est requise.
pg_upgrade \
--old-datadir=/var/lib/postgresql/17/main \
--new-datadir=/var/lib/postgresql/18/main \
--old-bindir=/usr/lib/postgresql/17/bin \
--new-bindir=/usr/lib/postgresql/18/bin \
--check # Mode vérification (sans modification)Avant de lancer la migration réelle, il est fortement recommandé de faire une vérification :
# Vérifie la faisabilité de la migration
pg_upgrade --check [options...]
# Sortie possible :
# ✅ Performing Consistency Checks
# ✅ Checking cluster versions
# ✅ Checking database connection settings
# ✅ Checking for prepared transactions
# ✅ Checking statistics preservation capability ← Nouveau !
#
# Statistics preservation: ENABLED
# - 1,247 tables will have statistics preserved
# - Estimated stats data size: 87 MB
#
# Clusters are compatible (peut procéder à la migration)# Enlever --check pour lancer la vraie migration
pg_upgrade \
--old-datadir=/var/lib/postgresql/17/main \
--new-datadir=/var/lib/postgresql/18/main \
--old-bindir=/usr/lib/postgresql/17/bin \
--new-bindir=/usr/lib/postgresql/18/bin \
--link # Optionnel : utilise des liens au lieu de copier# Le log indique les statistiques préservées
cat pg_upgrade_server.log | grep -i "statistic"
# Exemple de sortie :
# Preserving statistics for table "public.commandes"
# Preserving statistics for table "public.clients"
# Statistics preservation: 1,247 objects transferred
# Statistics preservation: 3 objects skipped (incompatible)-- Connexion à la nouvelle base PostgreSQL 18
psql -d ma_base
-- Vérifier qu'une table a des statistiques
SELECT
schemaname,
tablename,
last_analyze,
last_autoanalyze,
n_live_tup
FROM pg_stat_user_tables
WHERE tablename = 'commandes';
-- Si last_analyze est NULL mais n_live_tup > 0
-- → Les statistiques ont été préservées (pas de nouvel ANALYZE)-- Voir les statistiques détaillées d'une colonne
SELECT
attname AS column_name,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds
FROM pg_stats
WHERE tablename = 'commandes'
AND attname = 'statut';
-- Exemple de résultat :
-- column_name | n_distinct | most_common_vals | most_common_freqs
-- statut | 3 | {livré,en_cours,ann.} | {0.6,0.25,0.15}
--
-- Si ces valeurs existent → statistiques préservées ✅Même avec la préservation, certains cas nécessitent un ANALYZE après migration :
-- Si les données ont beaucoup changé depuis le dernier ANALYZE
-- (avant la migration)
ANALYZE VERBOSE ma_table_volatile;-- Si vous voulez profiter de nouveaux types de statistiques
-- introduits dans PG 18
ANALYZE VERBOSE; -- Sur toute la base (si rapide)# Si pg_upgrade a indiqué :
# "Statistics for table 'public.ancien_format' could not be preserved"
# Alors :
psql -d ma_base -c "ANALYZE VERBOSE public.ancien_format;"-- Garantit que les statistiques sont à jour
-- avant d'être préservées
ANALYZE VERBOSE;Pourquoi ? Les statistiques préservées sont celles existantes. Si elles sont obsolètes, elles le resteront après migration.
-- Identifier les tables sans statistiques récentes
SELECT
schemaname,
tablename,
last_analyze,
last_autoanalyze,
GREATEST(last_analyze, last_autoanalyze) AS derniere_analyse
FROM pg_stat_user_tables
WHERE GREATEST(last_analyze, last_autoanalyze) < NOW() - INTERVAL '7 days'
OR GREATEST(last_analyze, last_autoanalyze) IS NULL
ORDER BY n_live_tup DESC;
-- Lancer ANALYZE sur ces tables avant migration# La préservation des statistiques ajoute quelques MB/GB
# selon la taille de votre base
# Vérifier l'espace disponible
df -h /var/lib/postgresql/# TOUJOURS faire un dry-run
pg_upgrade --check [options]# Dans un autre terminal
tail -f pg_upgrade_server.log | grep -E "(statistic|error|warning)"# Avant TOUTE migration majeure
pg_basebackup -D /backup/pg17_before_upgrade -Fp -Xs -P-- Script de validation post-migration
SELECT
'Statistics preserved' AS status,
COUNT(*) AS tables_count,
SUM(CASE WHEN last_analyze IS NOT NULL
OR last_autoanalyze IS NOT NULL THEN 1 ELSE 0 END) AS with_stats
FROM pg_stat_user_tables;
-- Résultat attendu :
-- status | tables_count | with_stats
-- Statistics preserved | 1247 | 1247-- Comparer quelques requêtes critiques
-- Vérifier que les plans d'exécution sont similaires
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM commandes
WHERE date_commande >= '2024-01-01';
-- Comparer avec le plan de l'ancienne version-- Activer pg_stat_statements si pas déjà fait
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Surveiller les requêtes lentes dans les premières heures
SELECT
query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000 -- > 1 seconde
ORDER BY mean_exec_time DESC
LIMIT 20; Toutes les statistiques ne peuvent pas toujours être préservées :
- Changements de format : Si PG 18 a modifié la structure interne d'un type de statistique
- Types de données obsolètes : Si vous utilisez des types deprecated
- Extensions tierces : Les statistiques d'extensions (ex: PostGIS) peuvent nécessiter une recollection
Principe fondamental :
Garbage In → Garbage Out
Si les statistiques de PG 17 étaient mauvaises,
les statistiques de PG 18 le seront aussi !
Si des données changent drastiquement entre l'ancien et le nouveau cluster (ce qui ne devrait pas arriver en production), les statistiques préservées peuvent être inadaptées.
La préservation des statistiques ajoute un léger surcoût au temps de migration :
Ordre de grandeur :
- Base 10 GB : +1-2 secondes
- Base 100 GB : +10-30 secondes
- Base 1 TB : +2-5 minutes
- Base 10 TB : +10-20 minutes
Généralement négligeable comparé au temps total de migration.
L'export et l'import des statistiques consomment de la mémoire :
Mémoire utilisée ≈ (nombre de colonnes statistiques × 50 KB)
Exemple :
- 10,000 colonnes → ~500 MB de RAM
- 100,000 colonnes → ~5 GB de RAM
Généralement acceptable sur des serveurs modernes.
| Critère | pg_upgrade (PG 18) | pg_dump/restore |
|---|---|---|
| Vitesse | ⚡ Très rapide (heures) | 🐌 Lent (jours pour To) |
| Préservation stats | ✅ Oui (automatique) | ❌ Non |
| Downtime | ⏱️ Minimal | ⏱️⏱️ Important |
| Sécurité | ✅ Copie indépendante | |
| Réorganisation | ❌ Pas de nettoyage | ✅ Tables réorganisées |
| Rollback | 🔄 Possible (avec backup) | 🔄 Facile (garder ancien) |
| Critère | pg_upgrade (PG 18) | Réplication Logique |
|---|---|---|
| Complexité | 🟢 Simple | 🔴 Complexe |
| Downtime | ⏱️ Court (heures) | ⏱️ Quasi-nul (minutes) |
| Préservation stats | ✅ Oui | ❌ Non (nouveau cluster) |
| Migration sélective | ❌ Tout ou rien | ✅ Table par table possible |
| Risque | 🟡 Moyen | 🟡 Moyen |
| Rollback | 🔄 Nécessite sauvegarde | 🔄 Changement de cible |
Recommandation générale :
pg_upgradepour la majorité des cas (simple, rapide, fiable)- Réplication logique pour les migrations critiques avec downtime minimal (<15 min)
Contexte :
- Base de données : 50 GB
- Tables : ~300
- Colonnes : ~2,000
- Downtime acceptable : 2-4 heures
Procédure avec PG 18 :
# 1. Vérification (5 min)
pg_upgrade --check [...options]
# 2. Migration (30 min - 1h)
pg_upgrade [...options]
# 3. Vérification statistiques (2 min)
psql -c "SELECT COUNT(*) FROM pg_stats;"
# 4. Redémarrage et tests (15 min)
# Total : ~2 heuresAvant PG 18 : +4 heures d'ANALYZE → Total ~6 heures
Contexte :
- Base de données : 2 TB
- Tables : ~5,000
- Colonnes : ~80,000
- Downtime acceptable : 12 heures max
Procédure avec PG 18 :
# 1. Préparation (1 jour avant)
ANALYZE VERBOSE; # 2-3 heures
# 2. Vérification (20 min)
pg_upgrade --check [...options]
# 3. Migration (8 heures)
pg_upgrade --link [...options]
# 4. Vérification et redémarrage (1 heure)
# Total downtime : ~9 heuresAvant PG 18 : +24h d'ANALYZE → Impossible dans la fenêtre de 12h !
Contexte :
- Base de données : 100 GB
- Forte volatilité des données
- Downtime acceptable : 4 heures
Stratégie optimale :
# 1. ANALYZE complet JUSTE avant migration
psql -c "ANALYZE VERBOSE;" # 20-30 min
# 2. Migration immédiate
pg_upgrade [...options] # 1-2 heures
# 3. Vérification ciblée post-migration
# ANALYZE uniquement sur tables très volatiles identifiées
psql -c "ANALYZE VERBOSE table_events, table_metrics;" # 5 min
# Total : ~3 heuresRéponse : Oui, dans PostgreSQL 18, la préservation des statistiques est automatique et activée par défaut. Aucune configuration particulière n'est requise.
Réponse : pg_upgrade :
- Log un avertissement pour chaque objet concerné
- Continue la migration normalement
- Vous pouvez lancer un ANALYZE ciblé après migration sur ces objets
Réponse : En général, non. Les statistiques préservées sont suffisantes. Exceptions :
- Tables très volatiles dont les données ont beaucoup changé récemment
- Objets signalés dans les logs de pg_upgrade comme non préservés
- Si vous voulez profiter de nouveaux types de statistiques PG 18
Réponse : Oui, la préservation fonctionne avec toutes les options de pg_upgrade :
- Mode
--copy(copie des fichiers) - Mode
--link(création de liens physiques)
Réponse : Oui, mais avec limitations. Plus l'écart de versions est grand, plus certaines statistiques peuvent être incompatibles. pg_upgrade préservera ce qui est compatible et logguera le reste.
Réponse : Minimal. Les statistiques représentent généralement moins de 0.1% de la taille totale de la base :
- Base 100 GB → ~50-100 MB de statistiques
- Base 1 TB → ~500 MB - 1 GB de statistiques
Réponse : Oui, toutes les statistiques disponibles dans pg_statistic et pg_class sont préservées, incluant :
- Statistiques de colonnes
- Statistiques de tables
- Métadonnées d'index (nombre de pages, etc.)
Réponse : Vérifiez d'abord si les statistiques sont bien présentes :
-- Vérification
SELECT tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public'; Si les statistiques sont présentes mais performances mauvaises :
- Vérifier les plans d'exécution (EXPLAIN ANALYZE)
- Vérifier les changements de configuration PostgreSQL
- Lancer un VACUUM si bloat important
- En dernier recours : ANALYZE VERBOSE
La préservation des statistiques dans PostgreSQL 18 est une amélioration majeure de pg_upgrade qui :
- ✅ Simplifie les migrations majeures
- ✅ Réduit drastiquement le temps de downtime effectif
- ✅ Élimine la période de performances dégradées post-migration
- ✅ Améliore la prévisibilité des migrations
- ✅ Permet des migrations plus sereines en production
Cette fonctionnalité est particulièrement bénéfique pour :
- Les bases de données volumineuses (> 500 GB)
- Les environnements avec des contraintes de downtime strictes
- Les équipes DevOps cherchant à automatiser les upgrades
- Automatique : Aucune configuration requise, activé par défaut
- Transparent : Fonctionne avec toutes les options de pg_upgrade
- Fiable : Gestion intelligente des incompatibilités
- Performant : Impact négligeable sur le temps de migration
- Production-ready : Testé et validé par la communauté
Pour aller plus loin dans votre compréhension de pg_upgrade et des migrations PostgreSQL :
- Section 19.3.2 : Option --swap pour upgrade rapide (nouveau PG 18)
- Section 19.3.3 : Vérifications parallèles avec --jobs (nouveau PG 18)
- Section 19.3.4 : Stratégies Blue/Green et Réplication Logique
- Section 16.10 : Maintenance VACUUM/ANALYZE pour optimiser avant migration
Note : Cette section peut sembler technique, mais retenez l'essentiel : PostgreSQL 18 rend les migrations plus simples et plus rapides. Si vous utilisez pg_upgrade, vos requêtes fonctionneront immédiatement à vitesse normale après la migration, sans période de "rodage" nécessaire. C'est un progrès majeur pour la production !