Kodetis Logo <Kodetis.Blog/>

Solution Challenge CI/CD et Observabilité des Données

#CICD #DevOps #Observabilité #PostgreSQL #Prometheus #Grafana
Wed, January 15, 2025 - 7 min read

Solution Challenge CI/CD et Observabilité des Données

Structure du Projet

cicd-observability-challenge/
├── .gitlab-ci.yml
├── docker-compose.yml
├── prometheus/
│   └── prometheus.yml
├── grafana/
│   ├── dashboards/
│   │   └── postgres-dashboard.json
│   └── provisioning/
│       ├── dashboards/
│       │   └── dashboard.yml
│       └── datasources/
│           └── datasource.yml
├── postgres/
│   └── init.sql
└── README.md

1. Docker Compose Configuration

Fichier : docker-compose.yml

version: '3.8'
 
services:
  postgres:
    image: postgres:15
    container_name: postgres-db
    environment:
      POSTGRES_DB: monitoring_db
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
    networks:
      - monitoring
 
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    container_name: postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:postgres123@postgres:5432/monitoring_db?sslmode=disable"
    ports:
      - "9187:9187"
    depends_on:
      - postgres
    networks:
      - monitoring
 
  prometheus:
    image: prom/prometheus:latest
    container_name: prometheus
    ports:
      - "9090:9090"
    volumes:
      - ./prometheus/prometheus.yml:/etc/prometheus/prometheus.yml
      - prometheus_data:/prometheus
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.path=/prometheus'
      - '--web.console.libraries=/etc/prometheus/console_libraries'
      - '--web.console.templates=/etc/prometheus/consoles'
      - '--storage.tsdb.retention.time=200h'
      - '--web.enable-lifecycle'
    depends_on:
      - postgres-exporter
    networks:
      - monitoring
 
  grafana:
    image: grafana/grafana:latest
    container_name: grafana
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_PASSWORD: admin123
    volumes:
      - grafana_data:/var/lib/grafana
      - ./grafana/provisioning:/etc/grafana/provisioning
      - ./grafana/dashboards:/var/lib/grafana/dashboards
    depends_on:
      - prometheus
    networks:
      - monitoring
 
volumes:
  postgres_data:
  prometheus_data:
  grafana_data:
 
networks:
  monitoring:
    driver: bridge

2. Configuration Prometheus

Fichier : prometheus/prometheus.yml

global:
  scrape_interval: 15s
  evaluation_interval: 15s
 
rule_files:
  # - "first_rules.yml"
  # - "second_rules.yml"
 
scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']
 
  - job_name: 'postgres-exporter'
    static_configs:
      - targets: ['postgres-exporter:9187']
    scrape_interval: 5s
    metrics_path: /metrics

3. Pipeline CI/CD GitLab

Fichier : .gitlab-ci.yml

stages:
  - build
  - deploy
  - test
 
variables:
  DOCKER_DRIVER: overlay2
  DOCKER_TLS_CERTDIR: "/certs"
 
services:
  - docker:20.10.16-dind
 
before_script:
  - docker info
  - docker-compose --version
 
build:
  stage: build
  image: docker:20.10.16
  script:
    - echo "Building custom postgres exporter image..."
    - docker build -t custom-postgres-exporter .
    - echo "Build completed successfully"
  only:
    - main
    - develop
 
deploy:
  stage: deploy
  image: docker/compose:latest
  script:
    - echo "Starting deployment..."
    - docker-compose up -d
    - echo "Waiting for services to be ready..."
    - sleep 30
    - docker-compose ps
    - echo "Checking service health..."
    - docker-compose logs postgres
    - docker-compose logs postgres-exporter
    - docker-compose logs prometheus
    - docker-compose logs grafana
  after_script:
    - docker-compose down
  only:
    - main
 
test:
  stage: test
  image: alpine:latest
  before_script:
    - apk add --no-cache curl
  script:
    - echo "Testing Prometheus metrics collection..."
    - sleep 10
    - curl -f http://prometheus:9090/-/ready || exit 1
    - echo "Testing PostgreSQL exporter metrics..."
    - curl -f http://postgres-exporter:9187/metrics | grep -q "pg_up" || exit 1
    - echo "Testing Grafana availability..."
    - curl -f http://grafana:3000/api/health || exit 1
    - echo "All tests passed!"
  dependencies:
    - deploy
  only:
    - main

4. Configuration Grafana

Fichier : grafana/provisioning/datasources/datasource.yml

apiVersion: 1
 
datasources:
  - name: Prometheus
    type: prometheus
    access: proxy
    url: http://prometheus:9090
    isDefault: true
    editable: true

Fichier : grafana/provisioning/dashboards/dashboard.yml

apiVersion: 1
 
providers:
  - name: 'default'
    orgId: 1
    folder: ''
    type: file
    disableDeletion: false
    updateIntervalSeconds: 10
    allowUiUpdates: true
    options:
      path: /var/lib/grafana/dashboards

Fichier : grafana/dashboards/postgres-dashboard.json

{
  "annotations": {
    "list": [
      {
        "builtIn": 1,
        "datasource": "-- Grafana --",
        "enable": true,
        "hide": true,
        "iconColor": "rgba(0, 211, 255, 1)",
        "name": "Annotations & Alerts",
        "type": "dashboard"
      }
    ]
  },
  "editable": true,
  "gnetId": null,
  "graphTooltip": 0,
  "id": null,
  "links": [],
  "panels": [
    {
      "datasource": "Prometheus",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "palette-classic"
          },
          "custom": {
            "axisLabel": "",
            "axisPlacement": "auto",
            "barAlignment": 0,
            "drawStyle": "line",
            "fillOpacity": 10,
            "gradientMode": "none",
            "hideFrom": {
              "legend": false,
              "tooltip": false,
              "vis": false
            },
            "lineInterpolation": "linear",
            "lineWidth": 1,
            "pointSize": 5,
            "scaleDistribution": {
              "type": "linear"
            },
            "showPoints": "never",
            "spanNulls": true,
            "stacking": {
              "group": "A",
              "mode": "none"
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          },
          "unit": "short"
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 0,
        "y": 0
      },
      "id": 1,
      "options": {
        "legend": {
          "calcs": [],
          "displayMode": "list",
          "placement": "bottom"
        },
        "tooltip": {
          "mode": "single"
        }
      },
      "targets": [
        {
          "expr": "pg_stat_database_numbackends{datname=\"monitoring_db\"}",
          "interval": "",
          "legendFormat": "Active Connections",
          "refId": "A"
        }
      ],
      "title": "PostgreSQL Active Connections",
      "type": "timeseries"
    },
    {
      "datasource": "Prometheus",
      "fieldConfig": {
        "defaults": {
          "color": {
            "mode": "thresholds"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "red",
                "value": null
              },
              {
                "color": "green",
                "value": 1
              }
            ]
          },
          "unit": "short"
        },
        "overrides": []
      },
      "gridPos": {
        "h": 8,
        "w": 12,
        "x": 12,
        "y": 0
      },
      "id": 2,
      "options": {
        "colorMode": "value",
        "graphMode": "area",
        "justifyMode": "auto",
        "orientation": "auto",
        "reduceOptions": {
          "calcs": [
            "lastNotNull"
          ],
          "fields": "",
          "values": false
        },
        "textMode": "auto"
      },
      "pluginVersion": "8.0.0",
      "targets": [
        {
          "expr": "pg_up",
          "interval": "",
          "legendFormat": "PostgreSQL Status",
          "refId": "A"
        }
      ],
      "title": "PostgreSQL Status",
      "type": "stat"
    }
  ],
  "schemaVersion": 27,
  "style": "dark",
  "tags": [
    "postgresql",
    "monitoring"
  ],
  "templating": {
    "list": []
  },
  "time": {
    "from": "now-1h",
    "to": "now"
  },
  "timepicker": {},
  "timezone": "",
  "title": "PostgreSQL Monitoring Dashboard",
  "uid": "postgres-monitoring",
  "version": 1
}

5. Script d’initialisation PostgreSQL

Fichier : postgres/init.sql

-- Création d'une base de données de test
CREATE DATABASE monitoring_db;
 
-- Création d'une table de test pour générer des métriques
\c monitoring_db;
 
CREATE TABLE IF NOT EXISTS user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    session_start TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_end TIMESTAMP,
    active BOOLEAN DEFAULT TRUE
);
 
-- Insertion de données de test
INSERT INTO user_sessions (user_id, active)
VALUES
    (1, true),
    (2, true),
    (3, false),
    (4, true),
    (5, false);
 
-- Création d'un utilisateur pour les métriques
CREATE USER monitoring WITH PASSWORD 'monitoring123';
GRANT CONNECT ON DATABASE monitoring_db TO monitoring;
GRANT USAGE ON SCHEMA public TO monitoring;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO monitoring;

6. Dockerfile (optionnel pour build custom)

Fichier : Dockerfile

FROM prometheuscommunity/postgres-exporter:latest
 
# Ajout de configuration personnalisée si nécessaire
COPY postgres-exporter-config.yml /etc/postgres-exporter/
 
# Variables d'environnement par défaut
ENV DATA_SOURCE_NAME="postgresql://postgres:postgres123@postgres:5432/monitoring_db?sslmode=disable"
 
EXPOSE 9187
 
CMD ["postgres_exporter"]

7. Documentation

Fichier : README.md

# Challenge CI/CD et Observabilité - Solution
 
## Description
Stack d'observabilité complète avec PostgreSQL, Prometheus et Grafana, déployée via GitLab CI/CD.
 
## Prérequis
- Docker et Docker Compose
- GitLab Runner configuré
- Accès aux ports 3000, 5432, 9090, 9187
 
## Déploiement Local
 
```bash
# Cloner le repository
git clone <repository-url>
cd cicd-observability-challenge
 
# Lancer la stack
docker-compose up -d
 
# Vérifier le statut
docker-compose ps

Accès aux Services

Vérification des Métriques

# Vérifier que Prometheus collecte les métriques
curl http://localhost:9090/api/v1/query?query=pg_up
 
# Vérifier les métriques de l'exporter
curl http://localhost:9187/metrics | grep pg_up

Dashboard Grafana

Le dashboard PostgreSQL est automatiquement importé et affiche :

  • Nombre de connexions actives à la base de données
  • Statut de PostgreSQL (up/down)
  • Métriques de performance en temps réel

Pipeline CI/CD

Le pipeline GitLab vérifie automatiquement :

  1. Build de l’image Docker personnalisée
  2. Déploiement de la stack complète
  3. Tests de santé des services
  4. Validation de la collecte de métriques

Nettoyage

docker-compose down -v
docker system prune -f

## 8. Tests de Validation

**Script de test : `test-stack.sh`**

```bash
#!/bin/bash

echo "=== Test de la Stack d'Observabilité ==="

# Test 1: Vérification des services
echo "1. Vérification des services..."
docker-compose ps

# Test 2: Test Prometheus
echo "2. Test de Prometheus..."
curl -f http://localhost:9090/-/ready || { echo "Prometheus non disponible"; exit 1; }

# Test 3: Test des métriques PostgreSQL
echo "3. Test des métriques PostgreSQL..."
curl -s http://localhost:9187/metrics | grep -q "pg_up" || { echo "Métriques PostgreSQL manquantes"; exit 1; }

# Test 4: Test Grafana
echo "4. Test de Grafana..."
curl -f http://localhost:3000/api/health || { echo "Grafana non disponible"; exit 1; }

# Test 5: Test de la base de données
echo "5. Test de la base de données..."
docker exec postgres-db psql -U postgres -d monitoring_db -c "SELECT COUNT(*) FROM user_sessions;" || { echo "Base de données non accessible"; exit 1; }

echo "✅ Tous les tests sont passés avec succès!"

Résultats Attendus

Métriques Collectées

  • pg_up: Statut de PostgreSQL (1 = up, 0 = down)
  • pg_stat_database_numbackends: Nombre de connexions actives
  • pg_stat_database_xact_commit: Transactions validées
  • pg_stat_database_xact_rollback: Transactions annulées

Dashboard Grafana

  • Graphique en temps réel des connexions actives
  • Indicateur de statut PostgreSQL
  • Métriques de performance de la base de données

Pipeline CI/CD

  • Build automatique réussi
  • Déploiement sans erreur
  • Tests de santé validés
  • Métriques collectées et vérifiées

Cette solution répond complètement aux exigences du challenge en déployant une stack d’observabilité fonctionnelle avec un pipeline CI/CD automatisé.