Grafana – Pilotage visuel de Maarch (PostgreSQL en lecture seule)

Docker : Grafana ↔ PostgreSQL (Maarch)

Mise en place d’un pilotage visuel pour Maarch Courrier sans modifier l’application : Grafana en Docker, datasource PostgreSQL (lecture seule), provisioning (datasource + dashboards JSON) pour rejouer l’environnement en une commande.

Docker Compose Grafana OSS PostgreSQL 16 Provisioning Read-only Debian 12 VirtualBox
  • Catégorie : BI / Observabilité
  • Contexte : VM Linux + BDD Maarch existante
  • Livrables : compose.yml, .env, provisioning, dashboards JSON, SQL views
Voir le repository GitHub
1) Cadrage du projet

Objectif : donner au métier un pilotage visuel (volumétrie, délais, charge par entité, SLA…) sans toucher à Maarch.

Contraintes : aller vite, reproductible, ré-utilisable, sécurisé (lecture seule).

Choix : Grafana en Docker, datasource PostgreSQL (read-only), provisioning (datasource + dashboards) pour reconstruire en 1 commande.

2) Architecture cible
  • VM Linux (ou hôte Docker).
  • PostgreSQL Maarch : existant. Création d’un compte lecture seule dédié.
  • Grafana en conteneur exposé sur :3000.
  • Persistance : volume Docker → /var/lib/grafana.
  • Provisioning : monté → /etc/grafana/provisioning.
  • Dashboards JSON versionnés dans Git.
grafana-maarch/
├─ compose.yml
├─ .env
├─ provisioning/
│  ├─ datasources/datasource.yml
│  └─ dashboards/provider.yml
├─ dashboards/
│  ├─ 01_overview.json
│  ├─ 02_performance.json
│  └─ 03_workload.json
└─ sql/
   ├─ views.sql
   └─ sample_queries.md
3) Pré-requis BDD : compte “lecture seule”
-- User RO pour Grafana
CREATE USER grafana_ro WITH PASSWORD 'ChangeMoi!';
GRANT CONNECT ON DATABASE maarch_courrier TO grafana_ro;

-- Selon le schéma (souvent "public")
GRANT USAGE ON SCHEMA public TO grafana_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO grafana_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO grafana_ro;

Si Grafana est sur une autre machine/réseau, autoriser l’accès dans pg_hba.conf et ouvrir le port 5432.

4) Déploiement Docker Compose

.env :

GRAFANA_PORT=3000
GRAFANA_ADMIN_USER=admin
GRAFANA_ADMIN_PASSWORD=Admin-Strong-2025!

PG_HOST=host.docker.internal   # ou "db-mc" si la BDD est en conteneur
PG_PORT=5432
PG_DB=maarch_courrier
PG_USER=grafana_ro
PG_PASSWORD=ChangeMoi!
PG_VERSION=16

compose.yml :

version: "3.8"
services:
  grafana:
    image: grafana/grafana-oss:latest
    container_name: grafana
    restart: unless-stopped
    ports:
      - "${GRAFANA_PORT}:3000"
    environment:
      GF_SECURITY_ADMIN_USER: "${GRAFANA_ADMIN_USER}"
      GF_SECURITY_ADMIN_PASSWORD: "${GRAFANA_ADMIN_PASSWORD}"
      PG_HOST: "${PG_HOST}"
      PG_PORT: "${PG_PORT}"
      PG_DB:   "${PG_DB}"
      PG_USER: "${PG_USER}"
      PG_PASSWORD: "${PG_PASSWORD}"
      PG_VERSION: "${PG_VERSION}"
    volumes:
      - grafana_data:/var/lib/grafana
      - ./provisioning/datasources:/etc/grafana/provisioning/datasources
      - ./provisioning/dashboards:/etc/grafana/provisioning/dashboards
      - ./dashboards:/var/lib/grafana/dashboards
volumes:
  grafana_data:
5) Provisioning (automatiser sans clic)

provisioning/datasources/datasource.yml :

apiVersion: 1
datasources:
  - name: Maarch Postgres
    uid: maarch_postgres
    type: postgres
    access: proxy
    isDefault: true
    url: ${PG_HOST}:${PG_PORT}
    database: ${PG_DB}
    user: ${PG_USER}
    jsonData:
      sslmode: disable
      postgresVersion: ${PG_VERSION}
      timescaledb: false
    secureJsonData:
      password: ${PG_PASSWORD}

provisioning/dashboards/provider.yml :

apiVersion: 1
providers:
  - name: "Maarch Dashboards"
    orgId: 1
    folder: "Maarch"
    type: file
    disableDeletion: false
    updateIntervalSeconds: 30
    options:
      path: /var/lib/grafana/dashboards
      # Tous les *.json dans /dashboards seront importés

Résultat : au premier démarrage, Grafana crée la datasource et importe automatiquement les dashboards.

6) Modèle de données & requêtes SQL (les graphes)

Adapte les noms de tables/colonnes à mon schéma Maarch (ex. courrier, mail_type, received_at, closed_at, entity).

Variables Grafana : $__timeFilter(colonne), $entity

-- 1) Volumétrie quotidienne (Entrants / Sortants)
SELECT
  date_trunc('day', received_at) AS "time",
  SUM(CASE WHEN mail_type='in'  THEN 1 ELSE 0 END) AS in_count,
  SUM(CASE WHEN mail_type='out' THEN 1 ELSE 0 END) AS out_count
FROM courrier
WHERE $__timeFilter(received_at)
  AND (${entity:regex} IS NULL OR entity ~* ${entity:regex})
GROUP BY 1
ORDER BY 1;

-- 2) Délai moyen de traitement (jours)
SELECT
  date_trunc('day', closed_at) AS "time",
  AVG(EXTRACT(EPOCH FROM (closed_at - received_at)))/86400 AS avg_days
FROM courrier
WHERE closed_at IS NOT NULL
  AND $__timeFilter(closed_at)
  AND (${entity:regex} IS NULL OR entity ~* ${entity:regex})
GROUP BY 1
ORDER BY 1;

-- 3) SLA en dépassement (> 10 jours)
SELECT
  date_trunc('day', closed_at) AS "time",
  SUM(CASE WHEN (closed_at - received_at) > interval '10 days' THEN 1 ELSE 0 END) AS breaches
FROM courrier
WHERE closed_at IS NOT NULL
  AND $__timeFilter(closed_at)
GROUP BY 1
ORDER BY 1;

-- 4) Charge par entité (Top 10)
SELECT
  entity,
  COUNT(*) AS nb
FROM courrier
WHERE $__timeFilter(received_at)
GROUP BY entity
ORDER BY nb DESC
LIMIT 10;

Astuce : crée des views SQL (dossier sql/views.sql) pour stabiliser les requêtes des panels.

7) Création des dashboards

  • 01_overview.json : tuiles Totaux entrants/sortants, variation %, séries temporelles journalières, répartition par entité.
  • 02_performance.json : courbe délai moyen, histogramme SLA dépassés, table top dossiers les plus longs.
  • 03_workload.json : bar chart Volume par entité (Top 10), filtres entité/statut, heatmap charge par jour/heure.

Tous utilisent des variables (période, entité regex, statut) et se provisionnent automatiquement.

8) Mise en route (Runbook)
# 1) Cloner
git clone https://github.com/avisse/grafana-maarch
cd grafana-maarch

# 2) Remplir .env (hôte/port BDD, user RO, mdp)

# 3) Lancer
docker compose up -d

# 4) Ouvrir Grafana
# → http://<hote>:3000  (admin / mot de passe .env)

# 5) Vérifier
# Folder "Maarch" → 3 dashboards importés automatiquement
# Configuration → Data sources → "Save & Test" OK
9) Sécurité, sauvegarde, MEP
  • RO strict sur PostgreSQL (pas de DDL/DML).
  • Exposition : éviter l’accès public ; sinon reverse proxy + HTTPS.
  • Sauvegardes : snapshot du volume grafana_data + dashboards JSON dans Git.
  • Upgrade : docker compose pull puis up -d.
10) Recette & preuves de bon fonctionnement
  • Datasource : Save & TestOK.
  • Dashboards : courbes non vides sur période avec données.
  • Filtres : entité / temps opérationnels.
  • Rejouabilité : autre VM → clone + compose up -d → mêmes résultats (provisioning).
  • Sécurité : l’utilisateur grafana_ro ne fait que SELECT.