Liste de contrôle des opérations de production PostgreSQL avec exemples pratiques

Introduction

Faire fonctionner PostgreSQL en production signifie prévoir le jour où une requête est lente, une connexion échoue ou un changement de configuration provoque un comportement inattendu. Une liste de contrôle élimine les suppositions en transformant des inquiétudes vagues en vérifications observables et actions réversibles. Cet article fournit une liste de contrôle pratique pour les opérations PostgreSQL, couvrant l'inventaire des versions et de l'environnement, les changements de configuration sûrs, la vérification et les diagnostics, les modes de défaillance et la récupération, les pièges courants, et une liste consolidée avec propriété et cadence de révision.

L'objectif est la sécurité opérationnelle : observer avant de changer, limiter le rayon d'impact, utiliser des paramètres fictifs plutôt que des secrets, vérifier le résultat et documenter comment récupérer si l'état attendu n'est pas atteint. Chaque section inclut des commandes concrètes, des sorties attendues et des chemins de récupération. Vous pouvez adapter les exemples à votre environnement, mais la séquence compte : vérifier la version et la topologie, capturer l'état, effectuer un changement limité, vérifier et revenir en arrière si nécessaire.

Inventaire des versions et de l'environnement

Avant de toucher à quoi que ce soit, sachez ce que vous exécutez. Inventoriez la version de PostgreSQL, le système d'exploitation, la topologie de déploiement (instance unique, réplication en continu ou service géré) et les outils disponibles pour l'administration. Un environnement de production peut avoir plusieurs versions de PostgreSQL ; vérifiez chacune séparément.

Commandes d'observation en lecture seule :

-- Se connecter à une base de données et obtenir la version du serveur
SELECT version();

La sortie attendue inclut la version de PostgreSQL et les détails de compilation :

PostgreSQL 14.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0, 64-bit

Depuis le shell, utilisez pg_config pour voir les détails d'installation :

pg_config --version
# PostgreSQL 14.2

Vérifiez le répertoire de données et l'emplacement du fichier de configuration :

SHOW data_directory;
SHOW config_file;
SHOW hba_file;

Ces paramètres sont en lecture seule et indiquent où PostgreSQL lit sa configuration. La sortie inclut le chemin complet, par exemple :

   data_directory
---------------------
 /var/lib/postgresql/14/main

       config_file
-------------------------
 /etc/postgresql/14/main/postgresql.conf

Découverte de la topologie :

Déterminez si l'instance est primaire ou en attente. Sur un serveur primaire, exécutez :

SELECT pg_is_in_recovery();

Sortie attendue : f (faux) pour un primaire ; t (vrai) pour un serveur en attente. Pour voir l'état de la réplication sur un primaire :

SELECT client_addr, state, sync_state FROM pg_stat_replication;

Exemple de sortie :

 client_addr  |   state   | sync_state
--------------+-----------+------------
 10.0.0.2     | streaming | async

Si vous êtes sur un service géré comme Amazon RDS ou Google Cloud SQL, certaines commandes peuvent être restreintes ; utilisez les vues en lecture seule ou les API spécifiques du fournisseur. Par exemple, sur RDS, vous pouvez exécuter SELECT aurora_version(); sur Aurora PostgreSQL ou consulter la console AWS pour les informations de version.

Rayon d'impact et récupération : Ces commandes sont en lecture seule et ne modifient pas l'état. Aucun chemin de récupération n'est nécessaire au-delà de les réexécuter plus tard pour confirmer la cohérence. Si la version est en fin de vie, planifiez une mise à niveau avant d'autres changements ; la mise à niveau est une opération distincte à risque plus élevé avec sa propre liste de contrôle.

Prérequis :

• Accès à un superutilisateur PostgreSQL ou à un rôle avec les autorisations pg_monitor pour des vues comme pg_stat_replication.
• Client psql installé localement ou accès au shell de l'hôte pour pg_config.

Chemin de configuration sûr

La configuration de PostgreSQL réside dans postgresql.conf, pg_hba.conf et postgresql.auto.conf pour les paramètres modifiés via ALTER SYSTEM. Changer la configuration peut affecter les performances, la sécurité et la disponibilité, suivez donc un chemin sûr : observez les paramètres actuels, changez une valeur, rechargez ou redémarrez si nécessaire, vérifiez et sachez comment revenir en arrière.

Étape 1 : Observer les paramètres actuels

Pour voir tous les paramètres actuels et leur source :

SELECT name, setting, source, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'max_connections');

Exemple de sortie :

      name       | setting |       source        | pending_restart
-----------------+---------+---------------------+----------------
 max_connections | 100     | configuration file  | f
 shared_buffers  | 16384   | configuration file  | f
 work_mem        | 4096    | default             | f

Unités : shared_buffers et work_mem sont en blocs de 8 Ko par défaut, donc shared_buffers = 16384 signifie 128 Mo. pending_restart affiche t si le paramètre nécessite un redémarrage pour prendre effet.

Étape 2 : Effectuer un changement limité

Exemple : augmenter work_mem de 4 Mo à 8 Mo pour une session afin de tester une requête sans affecter toute l'instance. Utilisez SET dans votre session :

SET work_mem = '8MB';
-- Exécutez votre requête, puis réinitialisez
RESET work_mem;

Pour un changement permanent, utilisez ALTER SYSTEM :

ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();

Pour revenir en arrière, exécutez :

ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();

Étape 3 : Vérifier

Vérifiez que la nouvelle valeur est active :

SHOW work_mem;

Sortie attendue : 8MB. Si pending_restart était t, un redémarrage est nécessaire ; planifiez une interruption et effectuez-la pendant une fenêtre de maintenance.

Rayon d'impact et récupération : SET au niveau de la session n'affecte que la session courante et s'annule à la déconnexion. ALTER SYSTEM affecte toutes les nouvelles sessions après rechargement ; les sessions existantes conservent l'ancienne valeur. Le retour en arrière utilise ALTER SYSTEM RESET et un rechargement. Testez toujours les changements dans un environnement de préproduction d'abord.

Prérequis :

• Superutilisateur ou privilège pg_reload_conf pour recharger la configuration.
• Pour les paramètres nécessitant un redémarrage, planifiez une interruption et ayez un plan de retour en arrière (par exemple, rétablir la valeur et redémarrer).

Vérification et diagnostics

Une fois un changement effectué, vérifiez qu'il fonctionne comme prévu et recueillez des diagnostics si quelque chose ne va pas. Utilisez les vues et outils intégrés de PostgreSQL pour comparer l'état actuel au comportement attendu.

Exemple : Vérifier les limites de connexion et les connexions actives

Vérifiez max_connections et l'utilisation actuelle :

SELECT count(*) AS total_connections FROM pg_stat_activity;
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

Si total_connections est proche de max_connections, vous pourriez être près de l'épuisement. Diagnostiquez quelles connexions sont inactives ou de longue durée :

SELECT pid, usename, state, query_start, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC
LIMIT 10;

Exemple : Diagnostiquer les requêtes lentes

Activez log_min_duration_statement pour journaliser les requêtes dépassant un seuil (par exemple, 500 ms) pour une base de données ou un utilisateur spécifique :

ALTER DATABASE mydb SET log_min_duration_statement = 500;

Consultez ensuite le fichier journal PostgreSQL pour les requêtes lentes. Pour une analyse en temps réel, utilisez pg_stat_statements si l'extension est activée :

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Exemple de sortie :

                     query                      | calls | total_exec_time | mean_exec_time
------------------------------------------------+-------+-----------------+----------------
 SELECT * FROM events WHERE user_id = $1       |  1023 |        12345 ms |         12.07 ms
 UPDATE accounts SET balance = balance + $1    |   512 |         8765 ms |         17.12 ms

Rayon d'impact et récupération : Les requêtes de vérification sont en lecture seule ; les paramètres de journalisation comme log_min_duration_statement affectent le volume des journaux et peuvent être rétablis avec ALTER DATABASE ... RESET ou ALTER SYSTEM RESET. Les extensions comme pg_stat_statements ajoutent une surcharge ; surveillez et supprimez si non nécessaire (DROP EXTENSION pg_stat_statements;).

Prérequis :

• pg_stat_statements nécessite shared_preload_libraries dans postgresql.conf et un redémarrage pour charger.
• Accès à l'emplacement du fichier journal PostgreSQL (paramètre log_directory) pour consulter les journaux de requêtes lentes.

Modes de défaillance et récupération

Les bases de données de production échouent de manière prévisible : disque plein, épuisement des connexions, retard de réplication, index corrompus ou suppression accidentelle de données. Savoir détecter et récupérer de chacun est essentiel.

1. Disque plein

Détection : Surveillez l'utilisation du disque avec des commandes OS (df -h) et les tailles des tablespaces PostgreSQL. Un disque plein peut empêcher PostgreSQL d'accepter des écritures et peut entraîner une corruption des données.

SELECT pg_size_pretty(pg_database_size('mydb'));
SELECT pg_size_pretty(pg_tablespace_size('pg_default'));

Récupération :

• Libérez de l'espace immédiatement : supprimez les anciens fichiers WAL si sûr (pg_archivecleanup ou selon vos paramètres d'archivage), tronquez les grandes tables de journaux ou déplacez un tablespace vers un autre volume.
• Ajoutez de l'espace disque ou étendez le volume.
• Après avoir libéré de l'espace, exécutez VACUUM pour nettoyer les tuples morts et vérifiez avec df -h.

2. Épuisement des connexions

Détection : Les applications reçoivent FATAL: sorry, too many clients already. Vérifiez :

SELECT count(*) FROM pg_stat_activity;
SELECT max_connections FROM pg_settings WHERE name = 'max_connections';

Récupération :

• Identifiez et terminez les connexions inactives avec précaution :

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle' AND now() - state_change > interval '10 minutes';

• Augmentez max_connections seulement après avoir compris les implications mémoire ; chaque connexion consomme de la mémoire. Mieux : utilisez un pool de connexions comme PgBouncer.
• Pour une solution à long terme, révisez la gestion des connexions de l'application et implémentez le pooling.

3. Retard de réplication

Détection : Sur le primaire, vérifiez pg_stat_replication :

SELECT application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

Sur le serveur en attente, exécutez :

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

Si lag est important ou si pg_last_xact_replay_timestamp() est nul pendant longtemps, le serveur en attente est en retard.

Récupération :

• Vérifiez les problèmes de réseau et de ressources du serveur en attente.
• Si le serveur en attente est trop en retard, envisagez de le reconstruire à partir d'une sauvegarde de base fraîche avec pg_basebackup.
• Vérifiez en réexécutant la requête de retard ; le retard devrait diminuer.

4. Suppression accidentelle de données

Détection : Erreurs d'application ou rapports d'utilisateurs. Si la récupération à un instant donné (PITR) est configurée, vous pouvez restaurer à un état antérieur.

Récupération :

• Arrêtez les écritures supplémentaires si possible.
• Utilisez pg_basebackup à partir d'une sauvegarde récente plus les archives WAL pour effectuer une PITR. Exemple :

# Sur le serveur de récupération, restaurez la sauvegarde de base, puis configurez recovery.conf avec recovery_target_time
recovery_target_time = '2024-03-15 10:00:00'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'

• Démarrez PostgreSQL ; il rejoue jusqu'à l'heure cible. Vérifiez la restauration des données.

5. Index corrompu

Détection : Les requêtes utilisant un index échouent avec des erreurs comme index "idx_name" contains unexpected zero page at block 0.

Récupération :

• Réindexez l'index ou la table affectée :

REINDEX INDEX idx_name;
-- Ou pour une table entière
REINDEX TABLE my_table;

• Pour éviter le blocage, utilisez REINDEX INDEX CONCURRENTLY idx_name; (nécessite PostgreSQL 12+).
• Vérifiez en exécutant une requête qui utilise l'index et en contrôlant les erreurs.

Rayon d'impact et récupération pour tous les modes de défaillance : La plupart des actions de récupération impliquent de changer l'état ; testez d'abord dans un environnement de préproduction. Ayez toujours des sauvegardes récentes et des procédures de restauration documentées. Pour la PITR, vérifiez régulièrement les sauvegardes en effectuant des exercices de restauration.

Prérequis :

• Les sauvegardes et l'archivage WAL doivent être configurés et testés avant qu'une défaillance survienne.
• La surveillance (par exemple, Prometheus, Nagios) doit alerter sur l'utilisation du disque, le nombre de connexions et le retard de réplication.

Pièges courants

Même les opérateurs expérimentés tombent dans des pièges. Voici des erreurs fréquentes et comment les éviter ou en récupérer.

1. Changer la configuration sans test

Pourquoi cela arrive : La pression pour résoudre rapidement un problème de performance conduit à changer shared_buffers, work_mem ou max_connections directement en production.

Comment éviter : Testez les changements dans un environnement de préproduction qui reflète la production. Utilisez ALTER SYSTEM et pg_reload_conf pour les paramètres rechargeables ; pour les paramètres nécessitant un redémarrage, planifiez une maintenance et ayez un plan de retour en arrière.

Récupération : Si un changement cause des problèmes, revenez immédiatement en arrière : ALTER SYSTEM RESET parameter; SELECT pg_reload_conf(); ou éditez postgresql.conf et redémarrez si nécessaire.

2. Ignorer le vacuum et l'autovacuum

Pourquoi cela arrive : L'autovacuum s'exécute en arrière-plan, donc les gens supposent qu'il fonctionne toujours. Si les tables grossissent et que le ballonnement s'accumule, les performances se dégradent et le wraparound des identifiants de transaction peut survenir.

Comment éviter : Surveillez l'activité de l'autovacuum : SELECT relname, last_autovacuum, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 1000;. Ajustez les seuils de l'autovacuum si nécessaire.

Récupération : Si le ballonnement est sévère, exécutez VACUUM FULL ou pg_repack pendant une interruption. Pour prévenir, assurez-vous que l'autovacuum est activé et ajustez autovacuum_vacuum_scale_factor de manière appropriée.

3. Ne pas mettre en place la surveillance et les alertes

Pourquoi cela arrive : Les petites équipes peuvent compter sur des vérifications manuelles. Quand quelque chose casse, elles le découvrent par les utilisateurs.

Comment éviter : Mettez en place une surveillance de base : un script qui interroge pg_stat_activity, pg_stat_replication et l'utilisation du disque chaque minute, alertant sur les seuils. Utilisez des outils comme pgwatch2, check_postgres ou la surveillance cloud.

Récupération : Si vous manquez de surveillance, commencez par une simple tâche cron qui vérifie SELECT count(*) FROM pg_stat_activity WHERE state = 'active'; et journalise dans un fichier. Ajoutez progressivement plus de vérifications.

4. Utiliser le superutilisateur pour les connexions applicatives

Pourquoi cela arrive : Commodité ; les développeurs d'applications peuvent utiliser le rôle superutilisateur postgres pour éviter les problèmes de permissions.

Comment éviter : Créez des rôles dédiés avec le moindre privilège. Par exemple :

CREATE ROLE app_user LOGIN PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Récupération : Si vous découvrez une utilisation du superutilisateur, créez de nouveaux rôles et migrez progressivement les applications. Révoquez le superutilisateur des rôles applicatifs.

5. Sauter la vérification des sauvegardes

Pourquoi cela arrive : Les sauvegardes s'exécutent automatiquement, donc les gens supposent qu'elles fonctionnent. Une sauvegarde corrompue ou incomplète n'est découverte que lors d'une tentative de restauration.

Comment éviter : Testez régulièrement les restaurations. Planifiez un exercice de restauration mensuel : restaurez la dernière sauvegarde sur une instance de test et vérifiez l'intégrité des données. Automatisez avec des scripts.

Récupération : Si vous constatez que les sauvegardes échouent, corrigez immédiatement le processus de sauvegarde et créez une nouvelle sauvegarde complète. Documentez l'échec et ajoutez une surveillance de l'état des tâches de sauvegarde.

Liste de contrôle des opérations

Voici une liste consolidée pour les opérations de routine et d'urgence. Attribuez un responsable unique (nommez un rôle réel ou exemple) et une cadence de révision pour chaque élément. Le responsable est chargé d'exécuter ou de garantir l'exécution, pas nécessairement d'effectuer personnellement chaque tâche.

Chaque responsable doit documenter le résultat de sa révision et les actions entreprises. La cadence de révision suppose un environnement de production avec une fréquence de changement modérée ; ajustez selon votre profil de risque.

Conclusion

Une liste de contrôle des opérations de production PostgreSQL n'est utile que si elle est limitée à la version, observable et réversible là où la technologie le permet. Copier une commande sans vérifier les prérequis et la sortie attendue n'est pas une procédure d'exploitation.

Commencez par une vérification à faible risque tirée de cet article : vérifiez votre version de PostgreSQL et la topologie, enregistrez l'état actuel et comparez-le à votre référence. Passez ensuite aux changements de configuration sûrs, aux diagnostics et aux exercices de récupération. Attribuez des responsables et des cadences de révision pour maintenir la liste vivante.

Des opérations fiables rendent les défaillances visibles, protègent les valeurs sensibles, limitent les changements à la ressource prévue et définissent la vérification de la récupération avant qu'un incident ne force la décision. Utilisez cet article comme point de départ, adaptez les commandes à votre environnement et améliorez continuellement vos procédures.