Skip to content

06.3-monitoring-alerting.md

Latest commit

 

History

History
1281 lines (1009 loc) · 38.1 KB

06.3-monitoring-alerting.md

File metadata and controls

1281 lines (1009 loc) · 38.1 KB

🔝 Retour au Sommaire

19.6.3. Monitoring et Alerting

Introduction

Le monitoring (surveillance) et l'alerting (notification d'alertes) sont absolument critiques en production. Sans eux, vous êtes aveugle : vous découvrez les problèmes quand les utilisateurs se plaignent, et non avant.

Un bon système de monitoring vous permet de :

  • Détecter les problèmes avant qu'ils n'impactent les utilisateurs
  • Comprendre les tendances (croissance des données, patterns d'utilisation)
  • Optimiser les performances (identifier les requêtes lentes, les index manquants)
  • Planifier la capacité (savoir quand scaler)
  • Faciliter le troubleshooting (diagnostiquer rapidement les incidents)

Cette section vous guide à travers les métriques essentielles, les outils, et les bonnes pratiques pour mettre en place un monitoring robuste de PostgreSQL.

Principe fondamental : "You can't improve what you don't measure" - Si vous ne mesurez pas, vous ne pouvez pas améliorer.

1. Les Métriques Essentielles

1.1. Les 5 Golden Signals du Monitoring

Inspiré du Site Reliability Engineering (SRE) de Google, surveillez toujours :

  1. Latency (latence) : Temps de réponse des requêtes
  2. Traffic (trafic) : Volume de requêtes (QPS - Queries Per Second)
  3. Errors (erreurs) : Taux d'erreurs et échecs de connexion
  4. Saturation (saturation) : Utilisation des ressources (CPU, RAM, I/O, connexions)
  5. Durability (durabilité) : État des sauvegardes et réplication

1.2. Catégories de Métriques PostgreSQL

Catégorie Exemples de métriques Criticité
Santé générale Uptime, version, état réplication ⭐⭐⭐
Connexions Connexions actives, idle, waiting ⭐⭐⭐
Performances QPS, latence moyenne, requêtes lentes ⭐⭐⭐
Cache Cache hit ratio, buffer usage ⭐⭐⭐
I/O Lectures/écritures disque, WAL generation ⭐⭐
Tables Taille, bloat, scans séquentiels ⭐⭐
Index Utilisation, taille, efficacité ⭐⭐
Vacuuming Derniers vacuum/analyze, dead tuples ⭐⭐⭐
Réplication Lag, état des standby ⭐⭐⭐
Locks Verrous actifs, deadlocks ⭐⭐

2. Vues Système PostgreSQL

PostgreSQL expose de nombreuses vues système dans le schéma pg_catalog. Ce sont vos premiers outils de monitoring.

2.1. pg_stat_activity : Activité en Cours

Rôle : Affiche toutes les connexions actives et leur état.

Requête de base :

SELECT
  pid,                    -- ID du processus
  usename,                -- Utilisateur
  application_name,       -- Application connectée
  client_addr,            -- IP client
  state,                  -- État (active, idle, idle in transaction)
  wait_event_type,        -- Type d'attente (Lock, IO, etc.)
  wait_event,             -- Événement spécifique
  query_start,            -- Début de la requête
  state_change,           -- Dernier changement d'état
  query                   -- Requête en cours
FROM pg_stat_activity  
WHERE state != 'idle'     -- Exclure les connexions inactives  
ORDER BY query_start;

Cas d'usage :

-- Nombre de connexions par état
SELECT state, count(*)  
FROM pg_stat_activity  
GROUP BY state;  

-- Requêtes actives de plus de 5 minutes
SELECT
  pid,
  usename,
  now() - query_start AS duration,
  query
FROM pg_stat_activity  
WHERE state = 'active'  
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Connexions en attente de verrous
SELECT
  pid,
  usename,
  wait_event_type,
  wait_event,
  state,
  query
FROM pg_stat_activity  
WHERE wait_event_type = 'Lock';

Métrique clé : max_connections vs connexions actives

SELECT
  (SELECT count(*) FROM pg_stat_activity) as current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_connections,
  round(100.0 * (SELECT count(*) FROM pg_stat_activity) /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 2) as usage_percent;

2.2. pg_stat_database : Statistiques par Base

Rôle : Métriques agrégées par base de données.

SELECT
  datname,                          -- Nom de la base
  numbackends,                      -- Connexions actives
  xact_commit,                      -- Transactions committées
  xact_rollback,                    -- Transactions annulées
  blks_read,                        -- Blocs lus depuis disque
  blks_hit,                         -- Blocs lus depuis cache
  tup_returned,                     -- Lignes retournées
  tup_fetched,                      -- Lignes récupérées
  tup_inserted,                     -- Lignes insérées
  tup_updated,                      -- Lignes modifiées
  tup_deleted,                      -- Lignes supprimées
  conflicts,                        -- Conflits (standby)
  temp_files,                       -- Fichiers temporaires créés
  temp_bytes,                       -- Taille fichiers temporaires
  deadlocks,                        -- Deadlocks détectés
  blk_read_time,                    -- Temps lecture I/O
  blk_write_time,                   -- Temps écriture I/O
  stats_reset                       -- Dernier reset des stats
FROM pg_stat_database  
WHERE datname = 'myapp';

Métrique critique : Cache Hit Ratio

-- Doit être > 95% pour de bonnes performances
SELECT
  datname,
  round(100.0 * blks_hit / (blks_hit + blks_read), 2) AS cache_hit_ratio
FROM pg_stat_database  
WHERE datname = 'myapp';

Fichiers temporaires (symptôme de work_mem insuffisant) :

SELECT
  datname,
  temp_files,
  pg_size_pretty(temp_bytes) as temp_size
FROM pg_stat_database  
WHERE temp_files > 0  
ORDER BY temp_bytes DESC;

2.3. pg_stat_user_tables : Statistiques par Table

Rôle : Métriques détaillées sur chaque table.

SELECT
  schemaname,
  relname,                          -- Nom de la table
  seq_scan,                         -- Scans séquentiels
  seq_tup_read,                     -- Lignes lues (seq scan)
  idx_scan,                         -- Scans d'index
  idx_tup_fetch,                    -- Lignes récupérées (index)
  n_tup_ins,                        -- Insertions
  n_tup_upd,                        -- Updates
  n_tup_del,                        -- Deletes
  n_tup_hot_upd,                    -- HOT updates (efficaces)
  n_live_tup,                       -- Lignes vivantes
  n_dead_tup,                       -- Lignes mortes (à vacuum)
  last_vacuum,                      -- Dernier vacuum manuel
  last_autovacuum,                  -- Dernier autovacuum
  last_analyze,                     -- Dernier analyze manuel
  last_autoanalyze,                 -- Dernier autoanalyze
  vacuum_count,                     -- Nombre de vacuum
  autovacuum_count,                 -- Nombre d'autovacuum
  analyze_count,                    -- Nombre d'analyze
  autoanalyze_count                 -- Nombre d'autoanalyze
FROM pg_stat_user_tables  
ORDER BY n_dead_tup DESC;

Nouveauté PostgreSQL 18 : Statistiques étendues sur VACUUM et ANALYZE

-- Statistiques détaillées de vacuum (PG 18)
SELECT
  schemaname,
  relname,
  last_autovacuum,
  autovacuum_count,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) as dead_tuple_percent
FROM pg_stat_user_tables  
WHERE n_dead_tup > 1000  
ORDER BY dead_tuple_percent DESC;

Identifier les tables nécessitant un vacuum :

SELECT
  schemaname || '.' || relname as table_name,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_ratio,
  last_autovacuum
FROM pg_stat_user_tables  
WHERE n_dead_tup > 10000  
  AND (last_autovacuum IS NULL OR last_autovacuum < now() - interval '1 day')
ORDER BY n_dead_tup DESC  
LIMIT 10;

Identifier les tables avec trop de scans séquentiels :

-- Tables qui bénéficieraient d'index
SELECT
  schemaname || '.' || relname as table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / NULLIF(seq_scan, 0) as avg_seq_read,
  pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) as size
FROM pg_stat_user_tables  
WHERE seq_scan > 0  
  AND idx_scan = 0  -- Jamais utilisé d'index
  AND pg_relation_size(schemaname||'.'||relname) > 5 * 1024 * 1024  -- > 5 MB
ORDER BY seq_scan DESC  
LIMIT 10;

2.4. pg_stat_user_indexes : Utilisation des Index

Rôle : Vérifier l'efficacité des index.

SELECT
  schemaname,
  tablename,
  indexrelname,                     -- Nom de l'index
  idx_scan,                         -- Nombre de scans
  idx_tup_read,                     -- Lignes lues
  idx_tup_fetch,                    -- Lignes récupérées
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes  
ORDER BY idx_scan;

Identifier les index inutilisés :

-- Index jamais utilisés (candidats à la suppression)
SELECT
  schemaname || '.' || tablename as table_name,
  indexrelname as index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
  idx_scan,
  idx_tup_read
FROM pg_stat_user_indexes  
WHERE idx_scan = 0  
  AND indexrelname NOT LIKE '%_pkey'  -- Exclure les PK
ORDER BY pg_relation_size(indexrelid) DESC;

2.5. pg_locks : Verrous Actifs

Rôle : Identifier les problèmes de concurrence.

SELECT
  locktype,                         -- Type de verrou (relation, tuple, transaction)
  database,
  relation::regclass,               -- Table concernée
  page,
  tuple,
  virtualxid,
  transactionid,
  mode,                             -- Mode de verrou (AccessShareLock, RowExclusiveLock, etc.)
  granted,                          -- Verrou acquis ou en attente
  pid                               -- Processus détenant le verrou
FROM pg_locks  
WHERE NOT granted;                  -- Verrous en attente

Identifier les blocages :

-- Qui bloque qui ?
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_statement,
  blocking_activity.query AS blocking_statement
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;

Fonction helper (simplifie l'identification) :

-- Liste des PIDs qui bloquent d'autres processus
SELECT pg_blocking_pids(pid) as blocking_pids, *  
FROM pg_stat_activity  
WHERE pg_blocking_pids(pid)::text != '{}';

2.6. pg_stat_bgwriter : Activité du Background Writer

Rôle : Statistiques sur les écritures en arrière-plan.

SELECT
  checkpoints_timed,                -- Checkpoints planifiés
  checkpoints_req,                  -- Checkpoints demandés (surcharge)
  checkpoint_write_time,            -- Temps d'écriture
  checkpoint_sync_time,             -- Temps de sync
  buffers_checkpoint,               -- Buffers écrits (checkpoint)
  buffers_clean,                    -- Buffers écrits (bgwriter)
  maxwritten_clean,                 -- Arrêts du bgwriter (trop d'écriture)
  buffers_backend,                  -- Buffers écrits par backends
  buffers_alloc,                    -- Buffers alloués
  stats_reset
FROM pg_stat_bgwriter;

Interprétation :

  • checkpoints_req élevé → Augmenter max_wal_size
  • buffers_backend élevé → Manque de capacité du bgwriter

3. pg_stat_statements : L'Extension Indispensable

3.1. Installation

pg_stat_statements est l'extension la plus importante pour le monitoring des performances. Elle track toutes les requêtes exécutées.

Installation :

-- Créer l'extension
CREATE EXTENSION pg_stat_statements;

Configuration (dans postgresql.conf) :

# Charger l'extension au démarrage
shared_preload_libraries = 'pg_stat_statements'

# Configuration pg_stat_statements
pg_stat_statements.max = 10000          # Nombre max de requêtes trackées  
pg_stat_statements.track = all          # Track toutes les requêtes (top, all, none)  
pg_stat_statements.track_utility = on   # Track DDL et commandes utilitaires  
pg_stat_statements.save = on            # Sauvegarder entre redémarrages

Nécessite un restart : ✅ Oui (pour shared_preload_libraries)

Vérification :

SELECT * FROM pg_stat_statements LIMIT 5;

3.2. Requêtes Top 10 les Plus Lentes

SELECT
  calls,                            -- Nombre d'exécutions
  total_exec_time,                  -- Temps total (ms)
  mean_exec_time,                   -- Temps moyen (ms)
  max_exec_time,                    -- Temps maximum (ms)
  stddev_exec_time,                 -- Écart-type
  rows,                             -- Lignes retournées
  query                             -- Requête (normalisée)
FROM pg_stat_statements  
ORDER BY mean_exec_time DESC  
LIMIT 10;

Explication :

  • Les requêtes sont normalisées (les constantes sont remplacées par $1, $2, etc.)
  • mean_exec_time élevé = requête lente
  • calls élevé + mean_exec_time modéré = impact cumulé important

3.3. Requêtes Consommant le Plus de Temps Total

SELECT
  calls,
  total_exec_time,
  round(total_exec_time / calls, 2) as avg_time_ms,
  round(100.0 * total_exec_time / sum(total_exec_time) OVER (), 2) as percent_total,
  query
FROM pg_stat_statements  
ORDER BY total_exec_time DESC  
LIMIT 10;

Cas d'usage : Une requête appelée 10M de fois avec 10ms moyenne = 100k secondes au total !

3.4. Requêtes avec le Plus de Cache Miss

SELECT
  calls,
  shared_blks_hit,                  -- Blocs en cache
  shared_blks_read,                 -- Blocs lus depuis disque
  round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) as cache_hit_ratio,
  query
FROM pg_stat_statements  
WHERE shared_blks_read > 0  
ORDER BY shared_blks_read DESC  
LIMIT 10;

3.5. Requêtes Créant des Fichiers Temporaires

SELECT
  calls,
  temp_blks_written,
  pg_size_pretty(temp_blks_written * 8192::bigint) as temp_size,
  mean_exec_time,
  query
FROM pg_stat_statements  
WHERE temp_blks_written > 0  
ORDER BY temp_blks_written DESC  
LIMIT 10;

Symptôme : work_mem insuffisant. Augmenter work_mem pour ces requêtes.

3.6. Reset des Statistiques

-- Réinitialiser toutes les stats (attention !)
SELECT pg_stat_statements_reset();

-- Réinitialiser une requête spécifique (PG 13+)
SELECT pg_stat_statements_reset(userid, dbid, queryid);

4. Nouveautés PostgreSQL 18 : Statistiques I/O et WAL

4.1. Statistiques I/O par Backend

Nouveauté PostgreSQL 18 : Statistiques détaillées des opérations I/O par processus.

-- Vue des statistiques I/O (PG 18)
SELECT
  backend_type,
  reads,
  read_time,
  writes,
  write_time,
  extends,
  op_bytes,
  evictions,
  reuses,
  fsyncs,
  fsync_time
FROM pg_stat_io;

Cas d'usage : Identifier les backends consommant le plus d'I/O.

4.2. Statistiques WAL par Backend

-- Génération WAL par type de backend (PG 18)
SELECT
  backend_type,
  wal_records,
  wal_fpi,                          -- Full Page Images
  wal_bytes,
  wal_buffers_full
FROM pg_stat_wal;

Métriques critiques :

  • wal_bytes élevé → Beaucoup d'écritures (charge write-heavy)
  • wal_buffers_full → Augmenter wal_buffers

5. Métriques Système (OS Level)

PostgreSQL ne vit pas dans le vide. Surveillez aussi l'OS.

5.1. CPU

# Utilisation CPU globale
top -bn1 | grep "Cpu(s)" | awk '{print $2 + $4}'

# Utilisation par processus PostgreSQL
ps aux | grep postgres | awk '{sum += $3} END {print sum "%"}'

Seuil d'alerte : > 80% de façon soutenue

5.2. Mémoire

# Mémoire disponible
free -h

# Mémoire utilisée par PostgreSQL
ps aux | grep postgres | awk '{sum += $6} END {print sum/1024 " MB"}'

Seuil d'alerte : < 10% disponible

5.3. Disque

# Espace disque
df -h | grep /var/lib/pgsql

# I/O disque (iostat)
iostat -x 1

Métriques importantes :

  • %util : Utilisation du disque (< 90% recommandé)
  • await : Latence moyenne (< 10ms pour SSD, < 50ms pour HDD)

5.4. Réseau

# Connexions actives
netstat -an | grep :5432 | wc -l

# Bande passante
iftop -i eth0

6. Outils de Monitoring Modernes

6.1. Prometheus + postgres_exporter + Grafana

Architecture :

PostgreSQL → postgres_exporter → Prometheus → Grafana
                (collecte)       (stockage)   (visualisation)

Installation postgres_exporter :

# Télécharger
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz  
tar xvfz postgres_exporter-0.15.0.linux-amd64.tar.gz  
cd postgres_exporter-0.15.0.linux-amd64  

# Configuration
export DATA_SOURCE_NAME="postgresql://postgres_exporter:password@localhost:5432/postgres?sslmode=disable"

# Lancer
./postgres_exporter

Métriques exposées (sur http://localhost:9187/metrics) :

  • pg_stat_activity_count
  • pg_stat_database_*
  • pg_stat_bgwriter_*
  • pg_locks_count
  • pg_stat_statements_*

Configuration Prometheus (prometheus.yml) :

scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']

Dashboards Grafana :

  • PostgreSQL Overview (ID: 9628)
  • PostgreSQL Database (ID: 12485)

6.2. pgBadger : Analyse de Logs

pgBadger génère des rapports HTML à partir des logs PostgreSQL.

Installation :

# Debian/Ubuntu
sudo apt install pgbadger

# RHEL/CentOS
sudo dnf install pgbadger

Configuration PostgreSQL (pour logs détaillés) :

log_min_duration_statement = 0    # Log toutes les requêtes  
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '  
log_checkpoints = on  
log_connections = on  
log_disconnections = on  
log_lock_waits = on  
log_temp_files = 0

Génération du rapport :

# Analyser un fichier de log
pgbadger /var/log/postgresql/postgresql-2025-11-23.log -o report.html

# Analyser plusieurs fichiers
pgbadger /var/log/postgresql/postgresql-*.log -o report.html

# Mode incrémental (monitoring continu)
pgbadger --last-parsed /var/lib/pgbadger/.last_parsed \
  /var/log/postgresql/*.log -o /var/www/html/pgbadger/report.html

Rapports générés :

  • Statistiques globales (connexions, requêtes, durées)
  • Top requêtes lentes
  • Requêtes avec erreurs
  • Locks et deadlocks
  • Checkpoints
  • Graphiques temporels

6.3. pg_stat_kcache : Métriques Système

Extension qui ajoute des métriques CPU et I/O aux requêtes de pg_stat_statements.

Installation :

# Debian/Ubuntu
sudo apt install postgresql-18-pg-stat-kcache

# Configuration
echo "shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'" >> postgresql.conf  
sudo systemctl restart postgresql-18  
CREATE EXTENSION pg_stat_kcache;

Requête enrichie :

SELECT
  query,
  calls,
  mean_exec_time,
  user_time,                        -- Temps CPU user
  system_time,                      -- Temps CPU système
  minflts,                          -- Minor page faults
  majflts,                          -- Major page faults
  reads,                            -- Blocs lus
  writes                            -- Blocs écrits
FROM pg_stat_statements  
JOIN pg_stat_kcache USING (queryid)  
ORDER BY user_time + system_time DESC  
LIMIT 10;

6.4. Solutions Cloud Natives

AWS CloudWatch (RDS / Aurora)

# Exemple boto3 - Récupérer les métriques
import boto3

cloudwatch = boto3.client('cloudwatch')  
response = cloudwatch.get_metric_statistics(  
    Namespace='AWS/RDS',
    MetricName='CPUUtilization',
    Dimensions=[{'Name': 'DBInstanceIdentifier', 'Value': 'mydb'}],
    StartTime=datetime.utcnow() - timedelta(hours=1),
    EndTime=datetime.utcnow(),
    Period=300,
    Statistics=['Average']
)

Métriques RDS disponibles :

  • CPUUtilization
  • DatabaseConnections
  • FreeableMemory
  • ReadLatency / WriteLatency
  • ReadIOPS / WriteIOPS

Azure Monitor

# CLI Azure - Métriques PostgreSQL
az monitor metrics list \
  --resource /subscriptions/.../resourceGroups/.../providers/Microsoft.DBforPostgreSQL/servers/myserver \
  --metric cpu_percent

Google Cloud Monitoring

# Client Python
from google.cloud import monitoring_v3

client = monitoring_v3.MetricServiceClient()  
project_name = f"projects/{project_id}"  
interval = monitoring_v3.TimeInterval(  
    end_time={"seconds": int(time.time())},
    start_time={"seconds": int(time.time()) - 3600},
)
results = client.list_time_series(
    request={
        "name": project_name,
        "filter": 'metric.type = "cloudsql.googleapis.com/database/cpu/utilization"',
        "interval": interval,
    }
)

7. Configuration des Alertes

7.1. Principes des Alertes Efficaces

Règles d'or :

  1. Actionable : Chaque alerte doit nécessiter une action
  2. Spécifique : Expliquer le problème et la gravité
  3. Calibrée : Ni trop sensible (alert fatigue), ni trop laxiste
  4. Contextualisée : Donner les informations pour diagnostiquer

Anti-patterns à éviter :

  • Alertes pour des événements normaux
  • Alertes non critiques en pleine nuit
  • Alertes redondantes
  • Alertes sans contexte ("quelque chose ne va pas")

7.2. Niveaux de Criticité

Niveau Description Réponse attendue Notification
Critical Service down, perte de données imminente Immédiate (24/7) PagerDuty
Warning Dégradation, capacité bientôt saturée Business hours Slack/Email
Info Information, pas d'action requise Review hebdomadaire Dashboard

7.3. Alertes Prometheus (Exemples)

Fichier : postgresql_alerts.yml

groups:
  - name: postgresql
    interval: 30s
    rules:
      # CRITICAL: Instance down
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL instance {{ $labels.instance }} is down"
          description: "PostgreSQL on {{ $labels.instance }} has been down for more than 1 minute"

      # CRITICAL: Too many connections
      - alert: PostgreSQLMaxConnectionsReached
        expr: (pg_stat_activity_count / pg_settings_max_connections) > 0.9
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL connections near limit on {{ $labels.instance }}"
          description: "{{ $labels.instance }} is using {{ $value | humanizePercentage }} of max_connections"

      # WARNING: High connection usage
      - alert: PostgreSQLHighConnections
        expr: (pg_stat_activity_count / pg_settings_max_connections) > 0.7
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL high connection usage on {{ $labels.instance }}"

      # CRITICAL: Replication lag
      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 300
        for: 3m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL replication lag on {{ $labels.instance }}"
          description: "Replication lag is {{ $value }}s (> 5 minutes)"

      # WARNING: Low cache hit ratio
      - alert: PostgreSQLLowCacheHitRatio
        expr: pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read) < 0.95
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL low cache hit ratio on {{ $labels.instance }}"
          description: "Cache hit ratio is {{ $value | humanizePercentage }}"

      # WARNING: Dead tuples accumulation
      - alert: PostgreSQLDeadTuples
        expr: pg_stat_user_tables_n_dead_tup > 10000
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL dead tuples on {{ $labels.instance }}"
          description: "Table {{ $labels.relname }} has {{ $value }} dead tuples"

      # CRITICAL: Disk space
      - alert: PostgreSQLDiskSpaceLow
        expr: (node_filesystem_avail_bytes{mountpoint="/var/lib/pgsql"} / node_filesystem_size_bytes) < 0.1
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL disk space low on {{ $labels.instance }}"
          description: "Disk has less than 10% space remaining"

      # WARNING: Slow queries
      - alert: PostgreSQLSlowQueries
        expr: rate(pg_stat_statements_mean_exec_time[5m]) > 1000
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL slow queries on {{ $labels.instance }}"

      # CRITICAL: Transaction ID wraparound
      - alert: PostgreSQLTransactionIDWraparound
        expr: pg_database_datfrozenxid_age > 1500000000
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL near transaction ID wraparound on {{ $labels.instance }}"
          description: "Database {{ $labels.datname }} is at {{ $value }} age"

7.4. Notification Channels

Slack :

# alertmanager.yml
receivers:
  - name: 'slack-critical'
    slack_configs:
      - api_url: 'https://hooks.slack.com/services/YOUR/WEBHOOK/URL'
        channel: '#alerts-critical'
        title: '{{ .GroupLabels.alertname }}'
        text: '{{ range .Alerts }}{{ .Annotations.description }}{{ end }}'

  - name: 'slack-warning'
    slack_configs:
      - api_url: 'https://hooks.slack.com/services/YOUR/WEBHOOK/URL'
        channel: '#alerts-warning'

route:
  receiver: 'slack-warning'
  routes:
    - match:
        severity: critical
      receiver: 'slack-critical'

PagerDuty (pour alertes critiques) :

receivers:
  - name: 'pagerduty'
    pagerduty_configs:
      - service_key: 'YOUR_PAGERDUTY_SERVICE_KEY'
        description: '{{ .GroupLabels.alertname }}'

8. Alertes Critiques Recommandées

8.1. Checklist des Alertes Essentielles

Alerte Seuil Criticité Action
Instance down pg_up == 0 CRITICAL Redémarrer, investiguer crash
Connexions saturées > 90% CRITICAL Tuer connexions idle, scaler
Réplication lag > 5 minutes CRITICAL Vérifier réseau, I/O, queries lentes
Espace disque < 10% CRITICAL Nettoyer, agrandir volume
Transaction ID wraparound > 1.5B CRITICAL VACUUM immédiat
Cache hit ratio < 95% WARNING Augmenter shared_buffers, ajouter index
Dead tuples > 10k par table WARNING Vérifier autovacuum, vacuum manuel
Fichiers temporaires > 1 GB/jour WARNING Augmenter work_mem, optimiser requêtes
Requêtes lentes > 10s moyenne WARNING EXPLAIN ANALYZE, optimiser
Checkpoints trop fréquents > 1/minute WARNING Augmenter max_wal_size
Locks en attente > 10 WARNING Identifier blocage, optimiser

8.2. Alertes Spécifiques à la Production

1. Backups manquants :

# Script de vérification
#!/bin/bash
LAST_BACKUP=$(find /backups -name "*.backup" -mtime -1 | wc -l)  
if [ $LAST_BACKUP -eq 0 ]; then  
  echo "CRITICAL: No backup in last 24h"
  exit 2
fi

2. Réplication cassée :

-- Sur le standby
SELECT
  CASE
    WHEN pg_is_in_recovery() = false THEN 'CRITICAL: Not in recovery mode'
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 'OK'
    WHEN extract(epoch from now() - pg_last_xact_replay_timestamp()) > 300 THEN 'CRITICAL: Replication lag > 5min'
    ELSE 'OK'
  END as status;

3. Longues transactions :

-- Transactions actives > 1 heure
SELECT
  pid,
  usename,
  application_name,
  state,
  now() - xact_start as duration,
  query
FROM pg_stat_activity  
WHERE state IN ('idle in transaction', 'active')  
  AND now() - xact_start > interval '1 hour'
ORDER BY duration DESC;

Alerte : Longues transactions bloquent VACUUM et provoquent du bloat.

9. Dashboard de Monitoring (Vue d'Ensemble)

Un bon dashboard affiche en un coup d'œil :

9.1. Section "Santé"

  • ✅ Uptime
  • ✅ Version PostgreSQL
  • ✅ État réplication (si applicable)
  • ✅ Dernière sauvegarde

9.2. Section "Performance"

  • 📊 QPS (Queries Per Second)
  • 📊 Latence moyenne (p50, p95, p99)
  • 📊 Cache hit ratio
  • 📊 Connexions actives vs max

9.3. Section "Ressources"

  • 📊 CPU utilization
  • 📊 Mémoire utilisée
  • 📊 Espace disque disponible
  • 📊 I/O read/write

9.4. Section "Problèmes"

  • ⚠️ Requêtes lentes (> 1s)
  • ⚠️ Locks en attente
  • ⚠️ Dead tuples élevés
  • ⚠️ Fichiers temporaires

9.5. Section "Tables Top"

  • 📋 Tables les plus volumineuses
  • 📋 Tables avec le plus de dead tuples
  • 📋 Tables avec le plus de scans séquentiels

10. Automatisation du Monitoring

10.1. Scripts de Health Check

Script quotidien (check_postgres_health.sh) :

#!/bin/bash
# Health check PostgreSQL

PSQL="psql -U postgres -t -c"

echo "=== PostgreSQL Health Check ==="  
echo "Date: $(date)"  

# Version
echo -n "Version: "
$PSQL "SELECT version();"

# Uptime
echo -n "Uptime: "
$PSQL "SELECT now() - pg_postmaster_start_time();"

# Connexions
echo "Connexions:"
$PSQL "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

# Cache hit ratio
echo -n "Cache hit ratio: "
$PSQL "SELECT round(100.0 * sum(blks_hit) / (sum(blks_hit) + sum(blks_read)), 2)
       FROM pg_stat_database WHERE datname = 'myapp';"

# Espace disque
echo "Disk space:"  
df -h /var/lib/pgsql  

# Top 5 requêtes lentes
echo "Top 5 slow queries:"
$PSQL "SELECT round(mean_exec_time::numeric, 2) as avg_ms, calls, query
       FROM pg_stat_statements
       ORDER BY mean_exec_time DESC
       LIMIT 5;"

# Dead tuples
echo "Dead tuples:"
$PSQL "SELECT schemaname, relname, n_dead_tup
       FROM pg_stat_user_tables
       WHERE n_dead_tup > 1000
       ORDER BY n_dead_tup DESC
       LIMIT 5;"

# Dernier backup
echo -n "Last backup: "  
ls -lth /backups/*.backup | head -1 | awk '{print $6, $7, $8, $9}'

Exécution via cron :

# /etc/cron.daily/check_postgres_health
0 8 * * * /usr/local/bin/check_postgres_health.sh | mail -s "PostgreSQL Health Report" dba@company.com

10.2. Monitoring via Systemd Timer

# /etc/systemd/system/postgres-monitor.service
[Unit]
Description=PostgreSQL Monitoring

[Service]
Type=oneshot  
ExecStart=/usr/local/bin/check_postgres_health.sh  
# /etc/systemd/system/postgres-monitor.timer
[Unit]
Description=Run PostgreSQL monitoring every 5 minutes

[Timer]
OnBootSec=5min  
OnUnitActiveSec=5min  

[Install]
WantedBy=timers.target
sudo systemctl enable postgres-monitor.timer  
sudo systemctl start postgres-monitor.timer

11. Bonnes Pratiques de Monitoring

11.1. Les 4 Piliers de l'Observabilité

  1. Metrics (métriques) : Données numériques agrégées (CPU, mémoire, QPS)
  2. Logs (journaux) : Événements détaillés (erreurs, slow queries)
  3. Traces (traces) : Parcours d'une requête dans le système
  4. Profiling (profilage) : Analyse détaillée de code/requêtes

PostgreSQL excelle dans les metrics et logs. Pour les traces, utilisez APM (Application Performance Monitoring) comme New Relic, Datadog, ou open-source comme Jaeger.

11.2. Rétention des Données

Type de données Rétention recommandée Raison
Métriques haute résolution 7 jours Debug récent, pas de stockage massif
Métriques agrégées (1h) 90 jours Tendances moyen terme
Logs détaillés 30 jours Compliance, audit
Logs archivés 1-7 ans Compliance légale (GDPR, SOX, etc.)
pg_stat_statements Reset hebdomadaire Éviter saturation, focus sur récent

11.3. Éviter les Pièges Courants

Piège 1 : Alert Fatigue

  • Trop d'alertes → On les ignore → Alertes critiques manquées
  • Solution : Calibrer les seuils, agréger les alertes similaires

Piège 2 : Monitoring trop Intrusif

  • Monitoring qui ralentit la production
  • Solution : Limiter la fréquence, utiliser des vues efficaces

Piège 3 : Manque de Contexte

  • Alerte "CPU élevé" → Et alors ?
  • Solution : Ajouter contexte (tendance, comparaison, impact business)

Piège 4 : Ignorer les Métriques Métier

  • Ne monitorer que la technique (CPU, RAM)
  • Solution : Ajouter métriques business (transactions/jour, revenus, utilisateurs actifs)

12. Checklist Monitoring Production

Avant de mettre en production, vérifiez :

✅ Configuration de Base

  • pg_stat_statements installé et configuré
  • Logs activés avec niveau approprié
  • Log rotation configurée
  • Logs centralisés (syslog, ELK, CloudWatch)

✅ Métriques Collectées

  • Connexions actives / max
  • QPS (queries per second)
  • Latence requêtes (p50, p95, p99)
  • Cache hit ratio
  • CPU, RAM, disque, I/O
  • Taille base de données et tables
  • Dead tuples par table
  • État réplication (si applicable)
  • Dernière sauvegarde

✅ Alertes Configurées

  • Instance down
  • Connexions saturées (> 90%)
  • Réplication lag (> 5 min)
  • Espace disque (< 10%)
  • Cache hit ratio (< 95%)
  • Transaction ID wraparound
  • Backup manquant
  • Requêtes lentes

✅ Outils Déployés

  • Prometheus + Grafana (ou équivalent)
  • pgBadger pour analyse logs
  • Dashboards configurés
  • Alerting fonctionnel (Slack, PagerDuty)
  • Runbooks documentés

✅ Processus

  • Review hebdomadaire des métriques
  • Analyse des slow queries (hebdo)
  • Rotation et archivage des logs
  • Tests d'alertes (mensuel)
  • Formation équipe sur dashboards

13. Ressources et Documentation

Documentation Officielle

Outils Open Source

Blogs et Guides

Livres

  • "PostgreSQL Query Optimization" (Henrietta Dombrovskaya)
  • "Mastering PostgreSQL 13" (Hans-Jürgen Schönig)

Conclusion

Le monitoring de PostgreSQL est essentiel mais ne doit pas être complexe au départ.

Approche progressive :

  1. Semaine 1 : Installer pg_stat_statements, configurer les logs
  2. Semaine 2 : Déployer Prometheus + Grafana, dashboards de base
  3. Semaine 3 : Configurer les alertes critiques
  4. Semaine 4 : Affiner les seuils, ajouter contexte
  5. Mois 2+ : Optimisations continues basées sur les données

Métriques prioritaires (commencez par celles-ci) :

  1. Connexions actives
  2. Cache hit ratio
  3. Requêtes lentes (top 10)
  4. Dead tuples
  5. Espace disque

L'objectif : Transformer les données brutes en insights actionnables qui vous permettent de maintenir un PostgreSQL performant, stable et prévisible.

"In God we trust, all others must bring data." - W. Edwards Deming

Avec un monitoring solide, vous ne devinerez plus - vous saurez exactement ce qui se passe dans votre base de données.

Section suivante : 19.6.4. Backup et DR (Disaster Recovery)

⏭️ Backup et DR