L'extension Heroku CLI heroku-pg-extras fournit un ensemble de commandes pg:* utiles pour la gestion avancée des bases de données PostgreSQL. Ce guide se concentre sur deux commandes essentielles pour superviser les connexions et l'état de maintenance : pg:user-connections et pg:vacuum-stats.

Installation et configuration initiale

Installez l'extension via npm :

heroku plugins:install @heroku-cli/heroku-pg-extras

Pour lister toutes les commandes PostgreSQL disponibles, exécutez :

heroku pg

Analyse des connexions utilisateur avec pg:user-connections

Chaque connexion à PostgreSQL consomme une ressource sur le serveur de base de données. Une consommation excessive peut entraîner le rejet de nouvelles requêtes. La commande pg:user-connections regroupe les connexions actives par identifiants d'accès (credentials).

heroku pg:user-connections -a nom-de-votre-app

La sortie ressemble à ceci :

Scénarios d'utilisation :

• Détection des fuites de connexions : une augmentation constante du nombre de connexions pour un identifiant sans libération correspondante peut indiquer une fuite dans le code applicatif.
• Optimisation du pool de connexions : les données permettent d'ajuster la taille maximale du pool de connexoins dans la configuration de l'application.
• Planification des ressources : si le total approche de la limite de l'instance de base de données, il peut être nécessaire de procéder à un scaling.

L'implémentation interroge l'API Heroku Postgres pour agréger les statistiques de connexion par identifiant.

État de la maintenance (Vacuum) avec pg:vacuum-stats

PostgreSQL utilise le mécanisme MVCC. Les lignes supprimées ou mises à jour ne sont pas immédiatement effacées mais marquées comme mortes. L'opération VACUUM nettoie ces lignes pour récupérer l'espace disque et maintenir les performances des requêtes. La commande pg:vacuum-stats offre une vue par table sur l'état du vacuum.

heroku pg:vacuum-stats -a nom-de-votre-app

Les colonnes clés incluent :

• schema et table : l'objet concerné.
• last_vacuum / last_autovacuum : date de la dernière exécution manuelle/automatique.
• dead_rowcount : nombre de lignes mortes actuelles.
• autovacuum_threshold : seuil déclenchant le vacuum automatique.
• expect_autovacuum : indique si une exécution automatique est imminente (si dead_rowcount > threshold).

Interprétation des résultats :

• Surveiller les tables où dead_rowcount est élevé par rapport au seuil.
• Une valeur expect_autovacuum à yes signale une nécessité d'attention, bien que le système doive intervenir automatiquement.
• Un last_autovacuum très ancien peut indiquer un problème de configuration (désactivé, seuil trop haut, etc.).

L'implémentation de cette commande exécute une requête SQL complexe sur les tables système pg_stat_user_tables et pg_class pour calculer cet état.

Bonnes pratiques et intégration

Intégrez ces commandes dans vos routines de maintenance automatisée.

# Vérification hebdomadaire des connexions
0 9 * * 1 heroku pg:user-connections -a mon-app >> /var/log/db-connections.log

# Surveillance quotidienne de l'état du vacuum, alerte si nécessaire
0 6 * * * heroku pg:vacuum-stats -a mon-app | grep -q "yes" && echo "Alerte Vacuum - $(date)" >> /var/log/db-alerts.log

D'autres commandes complémentaires de heroku-pg-extras peuvent renforcer cette surveillence :

• pg:long-running-queries : identifie les requêtes de longue durée, souvent liées à une saturation des connexions.
• pg:blocking : détecte les verrous bloquants entre requêtes.
• pg:bloat : mesure le gonflement (bloat) des tables et index, indicateur complémentaire à l'état du vacuum.

Traitement des problèmes courants :

• Connexions trop nombreuses : vérifier le pool de l'application, utiliser heroku pg:killall pour terminer les connexions inactives, ou envisager une mise à niveau du plan.
• Vacuum insuffisant : lancer un vacuum manuel via heroku pg:psql -c "VACUUM NOM_TABLE;" ou ajuster les paramètres autovacuum de la table via ALTER TABLE NOM_TABLE SET (autovacuum_vacuum_scale_factor = 0.05);.

L'utilisation régulière de pg:user-connections et pg:vacuum-stats permet de prévenir les pannes liées à l'épuisement des ressources, de garantir le bon fonctionnement de la maintenance automatique, et d'optimiser l'utilisation de la base de données PostgreSQL sur Heroku.