E-NO
EN FR
Commencer
Dépannage PostgreSQL 7 min de lecture

Dépannage PostgreSQL avec exemples pratiques

calendar_today Publié : 2026-08-14
update Dernière mise à jour : 2026-08-14
analytics Efficacité SEO : 100%
Illustration du guide technique pour « Dépannage PostgreSQL avec exemples pratiques ».

Le dépannage PostgreSQL progresse d'un symptôme observé vers une résolution vérifiée par une investigation systématique. Ce guide couvre l'identification de version, l'analyse des journaux, la validation de configuration, les diagnostics de performance et les procédures de récupération pour les développeurs, ingénieurs DevOps et équipes techniques opérant PostgreSQL en production. Chaque section fournit des commandes concrètes, sorties attendues, signaux d'échec et chemins de restauration pour agir avec confiance plutôt que par approximation.

Inventaire de version et d'environnement

Avant d'investiguer tout problème, établissez une base précise de ce qui tourne et où.

Identifier la version et les détails de compilation PostgreSQL

Exécutez la commande de version depuis le client psql ou le binaire serveur :

psql --version
# psql (PostgreSQL) 16.2

# Ou depuis le binaire serveur
postgres --version
# postgres (PostgreSQL) 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.0, 64-bit

Dans une session connectée, interrogez la version serveur et les options de compilation :

SELECT version();
-- PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.0, 64-bit

SHOW server_version_num;
-- 160002

Résultat attendu : Chaîne de version majeur.mineur.patch correspondant à votre manifeste de déploiement. Signal d'échec : Incompatibilité de version entre client et serveur, ou version majeure non supportée (ex. PostgreSQL 12 en fin de vie depuis novembre 2024).

Capturer la topologie de déploiement

Déterminez si vous exécutez une instance unique, une réplication en flux, une réplication logique, Patroni, pg_auto_failover, ou un service géré (RDS, Cloud SQL, Azure Database). Exécutez :

SELECT * FROM pg_stat_replication;
-- Résultat vide = aucune réplica en flux connectée

SELECT * FROM pg_stat_subscription;
-- Affiche les abonnements de réplication logique le cas échéant

Vérifiez la présence de pool de connexions (PgBouncer, pgpool-II) et notez le mode de pool (session, transaction, instruction).

Enregistrer les réglages critiques et prérequis

Capturez la configuration active avec ses sources :

SELECT name, setting, unit, source, context
FROM pg_settings
WHERE source NOT IN ('default', 'override')
ORDER BY name;

Exportez vers un fichier horodaté pour comparaison ultérieure :

psql -c "SELECT name, setting, unit, source FROM pg_settings WHERE source != 'default' ORDER BY name;" > pg_settings_$(date +%F_%H%M).txt

Prérequis : Superutilisateur ou rôle pg_read_all_settings. Rayon d'action : Lecture seule. Vérification : Le fichier existe et contient des entrées non-par-défaut.

Chemin de configuration sécurisé

Les modifications de configuration doivent être observables, minimales et réversibles. N'éditez jamais postgresql.conf directement sans sauvegarde versionnée.

Observer l'état de configuration actuel

Utilisez pg_settings pour voir la valeur effective, les redémarrages en attente et la source :

SELECT name, setting, pending_restart, context, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size', 'max_connections', 'wal_level', 'max_wal_senders');

Appliquer une modification unique et ciblée

Exemple : Augmenter work_mem de 4 Mo à 16 Mo pour une charge de travail de reporting.

  1. Sauvegarder la configuration actuelle :
   cp /etc/postgresql/16/main/postgresql.conf /etc/postgresql/16/main/postgresql.conf.$(date +%F_%H%M).bak
  1. Modifier via ALTER SYSTEM (préféré pour la persistance après redémarrage) :
   ALTER SYSTEM SET work_mem = '16MB';
   SELECT pg_reload_conf();
  1. Vérifier que la modification a pris effet :
   SHOW work_mem;
   -- work_mem: 16MB

Résultat attendu : SHOW retourne la nouvelle valeur ; pg_settings.pending_restart est faux pour ce paramètre. Signal d'échec : pg_reload_conf() retourne faux, ou le paramètre affiche encore l'ancienne valeur (contexte = postmaster nécessite un redémarrage). Restauration : ALTER SYSTEM RESET work_mem; SELECT pg_reload_conf(); puis vérifier.

Paramètres nécessitant un redémarrage

Pour shared_buffers, max_connections, wal_level ou max_wal_senders, un redémarrage est obligatoire. Planifiez une fenêtre de maintenance, informez les parties prenantes, et vérifiez que le cluster redémarre proprement :

systemctl restart postgresql@16-main
# ou
pg_ctlcluster 16 main restart

Vérifiez le journal pour un démarrage réussi :

tail -n 50 /var/log/postgresql/postgresql-16-main.log
-- LOG:  database system is ready to accept connections

Vérification et diagnostics

Lorsque des symptômes apparaissent (requêtes lentes, épuisement des connexions, décalage de réplication, problèmes de vacuum), utilisez ces parcours diagnostiques.

Analyse des connexions et sessions

Sessions actives avec durée et état :

SELECT pid, usename, application_name, client_addr, state,
       now() - backend_start AS session_duration,
       now() - state_change AS state_duration,
       wait_event_type, wait_event,
       LEFT(query, 120) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY backend_start;

Nombre de connexions vs limite :

SELECT count(*) AS active_connections,
       setting::int AS max_connections,
       round(100.0 * count(*) / setting::int, 1) AS pct_used
FROM pg_stat_activity, pg_settings
WHERE name = 'max_connections';

Signal d'échec : Utilisation > 80 % des connexions ou sessions bloquées en active avec wait_event_type = 'Lock' depuis > 30 secondes.

Analyse des blocages et verrous

Identifier les sessions bloquantes et bloquées :

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query,
       blocked_activity.state AS blocked_state,
       blocking_activity.state AS blocking_state
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
  AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
  AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
  AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
  AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
  AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
  AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
  AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
  AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Résultat attendu : Zéro ligne (aucun blocage). Signal d'échec : Lignes retournées ; examinez blocking_query — souvent une transaction non validée ou un DDL long.

Santé de la réplication (flux)

Côté primaire :

SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_human
FROM pg_stat_replication;

Côté réplica :

SELECT status, receiver_state, sender_state,
       pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       pg_last_wal_replay_timestamp(),
       now() - pg_last_wal_replay_timestamp() AS replay_delay
FROM pg_stat_wal_receiver;

Signal d'échec : replay_lag_bytes > 1 Go ou replay_delay > 5 minutes sur une réplica synchrone.

Surveillance du vacuum et de la fragmentation

Vérifier l'activité autovacuum et estimer la fragmentation des tables :

SELECT schemaname, relname, n_dead_tup, n_live_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum, last_autovacuum, last_analyze, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY dead_pct DESC
LIMIT 20;

Signal d'échec : dead_pct > 20 % sur de grosses tables, ou last_autovacuum plus vieux de 24 heures sur tables fréquemment mises à jour.

Déclencher un vacuum manuel si nécessaire :

VACUUM (ANALYZE, VERBOSE) public.grosse_table;

Utilisation des index et index manquants

Index inutilisés (candidats à la suppression) :

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND pg_relation_size(indexrelid) > 1024 * 1024  -- > 1 Mo
ORDER BY pg_relation_size(indexrelid) DESC;

Balayages séquentiels sur grosses tables (index possibles manquants) :

SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND n_live_tup > 100000
ORDER BY seq_tup_read DESC
LIMIT 20;

Analyse des journaux pour erreurs

Erreurs et fatals récents (dernières 100 lignes) :

grep -E "(ERROR|FATAL|PANIC)" /var/log/postgresql/postgresql-16-main.log | tail -100

Motifs courants :

  • could not connect to server: Connection refused → postmaster arrêté ou incompatibilité listen_addresses/port
  • remaining connection slots are reserved for non-replication superuser connections → max_connections épuisé
  • could not write to WAL file: No space left on device → disque plein sur pg_wal
  • deadlock detected → problème d'ordre de transactions applicatif

Vérifier la destination et le format des journaux :

SHOW log_destination;      -- stderr, csvlog, syslog
SHOW logging_collector;    -- on/off
SHOW log_directory;        -- log/
SHOW log_filename;         -- postgresql-%Y-%m-%d_%H%M%S.log

Modes de défaillance et récupération

Scénario 1 : Disque WAL plein

Symptômes : PANIC: could not write to WAL file, la base cesse d'accepter les écritures.

Actions immédiates :

  1. Vérifier l'espace disque : df -h /var/lib/postgresql/16/main/pg_wal
  2. Identifier les fichiers supprimables : ls -lh /var/lib/postgresql/16/main/pg_wal/ | head -20
  3. Ne supprimez jamais les fichiers WAL manuellement. Libérez plutôt de l'espace ailleurs (vieilles sauvegardes, journaux, tmp) ou agrandissez le volume.

Vérification de la récupération :

systemctl start postgresql@16-main
tail -f /var/log/postgresql/postgresql-16-main.log
-- LOG:  database system was interrupted; last known up at ...
-- LOG:  entering standby mode (si réplica) ou
-- LOG:  database system is ready to accept connections

Scénario 2 : Rétention de slot de réplication causant accumulation WAL

Symptômes : pg_wal grossit sans limite ; pg_replication_slots montre un slot inactif avec restart_lsn très en retard.

Diagnostic :

SELECT slot_name, plugin, slot_type, datoid, database,
       active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Résolution : Supprimer le slot inactif :

SELECT pg_drop_replication_slot('nom_slot_obsolete');

Vérification : La taille de pg_wal se stabilise ; pg_replication_slots n'affiche plus le slot.

Scénario 3 : Index corrompu

Symptômes : ERROR: index "idx_name" contains unexpected data, ou requête retourne de mauvais résultats silencieusement.

Récupération :

REINDEX INDEX CONCURRENTLY idx_name;
-- Ou pour table entière :
REINDEX TABLE CONCURRENTLY grosse_table;

Vérification : Exécuter la requête qui échouait ; vérifier pg_stat_user_indexes pour idx_scan qui s'incrémente.

Scénario 4 : Risque de rebouclage d'identifiant de transaction

Symptômes : WARNING: database "dbname" must be vacuumed within N transactions dans les journaux.

Action immédiate :

VACUUM (FREEZE) DATABASE base_critique;
-- Ou par table pour moins de contention de verrous :
VACUUM (FREEZE, ANALYZE) public.table_critique;

Surveiller la progression :

SELECT pid, phase, heap_blks_scanned, heap_blks_vacuumed,
       index_vacuum_count, max_dead_tuples
FROM pg_stat_progress_vacuum;

Vérification : age(datfrozenxid) diminue :

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
WHERE datallowconn
ORDER BY xid_age DESC;

Scénario 5 : DROP TABLE accidentel

Options de récupération (par ordre de préférence) :

  1. PITR (Point-in-Time Recovery) depuis sauvegarde de base + archive WAL — restaure à un horodatage précis.
  2. Sauvegarde logique (pg_dump) si assez récente.
  3. Extension pg_dirtyread (dernier recours, lit les tuples morts) — nécessite superutilisateur et CREATE EXTENSION pg_dirtyread;.

Aperçu PITR :

# 1. Arrêter le cluster
systemctl stop postgresql@16-main

# 2. Restaurer la sauvegarde de base vers nouveau répertoire de données
pg_basebackup -D /var/lib/postgresql/16/main_recovery -Ft -z -P -h hote_sauvegarde -U replicator

# 3. Créer recovery.signal et configurer restore_command dans postgresql.conf
echo "restore_command = 'cp /mnt/wal_archive/%f %p'" >> /var/lib/postgresql/16/main_recovery/postgresql.conf
echo "recovery_target_time = '2024-01-15 14:30:00'" >> /var/lib/postgresql/16/main_recovery/postgresql.conf
touch /var/lib/postgresql/16/main_recovery/recovery.signal

# 4. Démarrer et vérifier
systemctl start postgresql@16-main_recovery

Liste de contrôle opérationnelle

Utilisez cette liste lors de la maintenance de routine et de la réponse aux incidents.

Quotidien

  • [ ] Vérifier pg_stat_replication décalage < 100 Mo sur toutes les réplicas
  • [ ] Confirmer pg_stat_activity nombre connexions < 70 % de max_connections
  • [ ] Scanner les journaux pour ERROR, FATAL, PANIC sur 24 dernières heures
  • [ ] Confirmer taille répertoire pg_wal stable (pas de croissance non bornée)
  • [ ] Vérifier achèvement sauvegarde et succès archivage WAL

Hebdomadaire

  • [ ] Examiner pg_stat_user_tables pour tables avec dead_pct > 10 %
  • [ ] Vérifier pg_stat_user_indexes pour index inutilisés > 100 Mo
  • [ ] Exécuter pg_dump --schema-only des schémas critiques ; vérifier restauration en staging
  • [ ] Tester pg_basebackup depuis réplica (valide intégrité sauvegarde et réplication)
  • [ ] Examiner pg_settings pour dérive par rapport à la configuration versionnée

Mensuel

  • [ ] REINDEX DATABASE complet pendant fenêtre maintenance (ou CONCURRENTLY par table)
  • [ ] Vérifier restauration PITR vers cluster de staging depuis dernière sauvegarde de base + WAL
  • [ ] Mettre à jour version mineure PostgreSQL si correctifs sécurité publiés
  • [ ] Examiner et faire tourner les entrées pg_hba.conf ; supprimer accès obsolètes
  • [ ] Planification capacité : projeter croissance pg_database_size sur 90 jours

Réponse incident (Premières 15 minutes)

  • [ ] Capturer instantané pg_stat_activity : SELECT * FROM pg_stat_activity \g activity_$(date +%F_%H%M).txt
  • [ ] Capturer instantané pg_locks si blocage suspecté
  • [ ] Vérifier espace disque sur volumes pg_data et pg_wal
  • [ ] Suivre journal PostgreSQL pour erreurs actives
  • [ ] Identifier fenêtre de changement : déploiement récent, modification config, migration schéma, pic trafic
  • [ ] Communiquer statut aux parties prenantes avec faits observés seulement (pas de spéculation)

Conclusion

Un dépannage PostgreSQL efficace repose sur une boucle reproductible : observer l'état actuel avec des commandes adaptées à la version, formuler une hypothèse, appliquer la modification la plus ciblée possible, vérifier le résultat face à un signal attendu, et documenter le chemin de restauration avant d'en avoir besoin. Ce guide vous a fourni des requêtes concrètes pour l'inventaire de version, la sécurité de configuration, les diagnostics de connexions et verrous, la santé de réplication, la surveillance vacuum et fragmentation, l'analyse d'index et l'interprétation des journaux — ainsi que des procédures de récupération pour les modes de défaillance les plus courants. Intégrez la liste de contrôle opérationnelle dans vos runbooks, pratiquez les restaurations PITR chaque trimestre, et traitez chaque modification de configuration comme un artefact déployable avec un plan de test et un bouton de restauration. Au prochain incident, vous passerez du symptôme à la résolution avec des preuves, non des suppositions.

Recherches connexes

Score de qualité de l’article

Utilité pour le lecteur 100%
  • check_circle Guide prêt à lire
  • check_circle Exemples pratiques inclus
  • check_circle URL d’article optimisée pour le SEO