Prometheus - Docker Compose, Part 3: PostgreSQL Monitoring with PostgreSQL Exporter, Grafana Dashboard

192.168.30.10 # Prometheus Docker Compose stack
192.168.30.12 # PostgreSQL & PostgreSQL Exporter Docker Compose Stack

# Create a directory for PostgreSQL
sudo mkdir -p /opt/postgres && cd /opt/postgres

# Create a .env file
sudo vi .env

# PostgreSQL credentials
DB_USERNAME=postgres
DB_PASSWORD=my_secure_password
DB_HOST=postgres
DB_NAME=postgres

# Create a Docker Compose manifest
sudo vi docker-compose.yml

services:
  postgres:
    image: postgres:12
    container_name: postgres
    restart: unless-stopped
    ports:
      - 5432:5432
    volumes:
      - ./postgres-data:/var/lib/postgresql/data
    env_file:
      - .env
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_DB: ${DB_NAME}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 3

  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    container_name: postgres-exporter
    restart: unless-stopped
    ports:
      - 9187:9187
    env_file:
      - .env
    environment:
      DATA_SOURCE_NAME: "postgresql://${DB_USERNAME}:${DB_PASSWORD}@${DB_HOST}/${DB_NAME}?sslmode=disable"
    depends_on:
      postgres:
        condition: service_healthy

# Create the container
sudo docker compose up -d

# List the containers
docker ps

# Shell output:
CONTAINER ID   IMAGE                                   COMMAND                  CREATED          STATUS                    PORTS                                       NAMES
b9c75bd5090a   prometheuscommunity/postgres-exporter   "/bin/postgres_expor…"   14 seconds ago   Up 3 seconds              0.0.0.0:9187->9187/tcp, :::9187->9187/tcp   postgres-exporter
ee872b474b64   postgres:12                             "docker-entrypoint.s…"   14 seconds ago   Up 13 seconds (healthy)   0.0.0.0:5432->5432/tcp, :::5432->5432/tcp   postgres

# Curl the exporter metrics
curl http://localhost:9187/metrics

# Adapt the Prometheus configuration
sudo vi prometheus_conf/prometheus.yml

# prometheus.yml
global:
  scrape_interval: 15s
  evaluation_interval: 10s


rule_files:
  - rules.yml

alerting:
  alertmanagers:
    - scheme: http
      static_configs:
        - targets: [ 'alertmanager:9093' ]


scrape_configs:
  # Prometheus Server
  - job_name: 'Prometheus-Server'
    static_configs:
      - targets:
        -  localhost:9090

  # Linux Servers / Node Exporter
  - job_name: 'Linux-Server'
    static_configs:
      - targets:
        -  192.168.30.11:9100
        -  192.168.30.12:9100

  # MySQL Server
  - job_name: 'MySQL-Server'
    params:
      auth_module: [client]  # Specify authentication mode
    scrape_interval: 5s
    static_configs:
      - targets: ['192.168.30.11:3306']
    relabel_configs:
      - source_labels: [__address__]
        target_label: __param_target
      - source_labels: [__param_target]
        target_label: instance
      - target_label: __address__
        replacement: 192.168.30.11:9104

  # PostgreSQL Server
  - job_name: 'Postgres-Server'
    static_configs:
      - targets: ["192.168.30.12:9187"]
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance
      - target_label: __address__
        replacement: 192.168.30.12:9187

# Adapt the rules.yml configuration
sudo vi prometheus_conf/rules.yml

# rules.yml
groups:
  - name: NodeExporter
    rules:
    - alert: InstanceDown
      expr: up{job="Linux-Server"} == 0
      for: 1m

  - name: MysqldExporter
    rules:
      - alert: MysqlDown
        expr: mysql_up == 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: MySQL down (instance {{ $labels.instance }})
          description: "MySQL instance is down on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

  - name: PostgresExporter
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: PostgreSQL down (instance {{ $labels.instance }})
          description: |
            PostgreSQL instance is down on {{ $labels.instance }}
            VALUE = {{ $value }}
            LABELS = {{ $labels }}

# Restart the Docker container
sudo docker compose restart

# Grafana webinterface
192.168.30.10:3000

# Open the Prometheus webinterface
http://192.168.30.10:9090

# CD into the Docker Compose directory
cd /opt/postgres

# Stop the Docker container
sudo docker compose down

# Verify the alert in the Prometheus webinterface
http://192.168.30.10:9090/alerts

# Verify the alert in the Prometheus webinterface
http://192.168.30.10:9093/#/alerts