Outils indispensables du DBA

Stack & principes

• Prometheus (scrape) + Grafana (dashboards) — exporters DB.
• Zabbix/Nagios pour checks actifs/synthétiques, ping ports/latence.
• Datadog (SaaS) : métriques, logs, APM, traces distribuées.
• Core KPIs : p95 latence, QPS/TPS, locks, bloat, IOPS, lag réplication, RPO réel.

Exporters

• PG : postgres_exporter, pgbouncer_exporter
• MySQL/MariaDB : mysqld_exporter
• Oracle : agent OEM, scripts SQL + node exporter

Alertes Prometheus (YAML)

groups:
- name: dba.rules
  rules:
  - alert: PostgresReplicationLagHigh
    expr: pg_replication_lag_bytes > 52428800  # 50MB
    for: 5m
    labels: {severity: critical}
    annotations:
      summary: "Lag réplication élevé"
      runbook: "/runbooks/postgres/replication-lag.md"
  - alert: BackupFreshnessExceeded
    expr: time() - last_successful_backup_timestamp > 86400
    for: 15m
    labels: {severity: warning}

Tableau comparatif

Panorama

• Universel : DBeaver (multi-moteur), HeidiSQL (MySQL/MariaDB).
• PG : pgAdmin, psql, pgBouncer UI.
• Oracle : SQL Developer, OEM.
• MySQL/MariaDB : mysql client, MySQL Shell, phpMyAdmin (basique).

Snippets de diagnostic

-- PostgreSQL Top N par temps
SELECT query, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- MySQL InnoDB status
SHOW ENGINE INNODB STATUS\G;

-- Oracle plan
EXPLAIN PLAN FOR SELECT * FROM T WHERE COL = :b1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

Checklist d’usage sécurisé

• Accès via bastion/VPN + MFA, comptes nominatifs.
• Limiter droits (RO vs RW), logs d’audit activés.
• Politique de versions (GUI/clients à jour, compat driver).

Bash/Python (ops rapides)

# Bash — dump Postgres compressé
pg_dump -Fc -j4 appdb | gzip > /bkp/appdb_$(date +%F).dump.gz

# Python — check lag réplicas (psycopg/connector)
import psycopg
with psycopg.connect(conninfo) as con:
    cur=con.execute("SELECT application_name, write_lag FROM pg_stat_replication")
    print(cur.fetchall())

Ansible (post-install & tuning)

- name: Tune PostgreSQL
  hosts: db
  become: yes
  tasks:
    - ini_file:
        path: /var/lib/pgsql/data/postgresql.conf
        option: shared_buffers
        value: 8GB
    - service: name=postgresql state=reloaded

Terraform (DBaaS)

resource "aws_db_instance" "pg" {
  engine="postgres"
  instance_class="db.m6g.large"
  multi_az=true
  allocated_storage=200
  backup_retention_period=35
  deletion_protection=true
  publicly_accessible=false
}

Principes

• Versionner le schéma en Git, idempotent + rollback défini.
• Pattern expand/contract pour zero-downtime (ajout colonne nullable → backfill → rendre NOT NULL).
• Tests : EXPLAIN des nouvelles requêtes + temps de lock/DDL.

Liquibase (XML extrait)

<changeSet id="2025-09-13-001" author="dba">
  <addColumn tableName="orders">
    <column name="status" type="varchar(16)" defaultValue="NEW"/>
  </addColumn>
</changeSet>

Flyway (SQL)

-- V20250913__add_index_orders.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_created
ON orders(customer_id, created_at);

Pipeline CI (pseudo-yaml)

steps:
  - checkout
  - restore-db-from-snapshot
  - run: liquibase updateSQL && liquibase update
  - run: psql -f tests/explain_checks.sql
  - run: python tests/locks_timing.py
  - approve: manual_gate
  - deploy: prod-window

Operators (PostgreSQL)

• Zalando / Crunchy Data : failover automatique, backups S3, users/roles CRDs.
• Resources : StatefulSets, PVC, PDB (budgets de perturbation).
• Snapshots CSI + rétention, restore de contrôle.

# Manifest (extrait CRD Zalando)
apiVersion: acid.zalan.do/v1
kind: postgresql
metadata: {name: app-cluster}
spec:
  numberOfInstances: 3
  volume:
    size: 200Gi
  users:
    app_user: ["reader", "writer"]

Helm (valeurs extraites)

replicaCount: 3
resources:
  requests: {cpu: "2", memory: "8Gi"}
  limits:   {cpu: "4", memory: "16Gi"}
persistence:
  enabled: true
  size: 200Gi
podDisruptionBudget:
  minAvailable: 2

DBaaS

• AWS : RDS/Aurora (Multi-AZ, Global DB).
• GCP : Cloud SQL / AlloyDB.
• Azure : Flexible Server.

Sécurité & secrets

• Secrets K8s → External Secrets + KMS (AWS/GCP/Azure) ; rotation.
• Réseau : policies, privés par défaut, egress restreint.
• Backups : bucket immuable (Object Lock), encryption server-side.

FinOps

• Right-sizing, auto-pause dev, tiering stockage (Standard/GP3/IO2).
• Alertes € /jour, rapports IOPS/latence.

Dashboards indispensables

• Santé : uptime, p95/99, erreurs, connexions, saturation pool.
• Stockage : IOPS, latence, espace libre, bloat/fragmentation.
• Réplication : lag, slots, divergence, switchover logs.
• Backups : fraîcheur, durée, taux succès.

Outils spécifiques

• PostgreSQL : pg_stat_statements, pg_stat_activity, extensions.
• Oracle : AWR/ASH, ADDM, SQL Monitor.
• MySQL : Performance Schema, slow query log.

SLO & budgets d’erreur

• Dispo ≥ 99.95%, RPO ≤ 5m, RTO ≤ 30m ; latence p95 lecture/écriture.
• Alertes “bruit” limitées (agrégation, inhibition, time windows).

Pipelines DB

• PR → lint SQL, dry-run des migrations, EXPLAIN checks, tests de locks/DDL.
• Gate manuel prod, fenêtres de déploiement, backout plan.
• Artifacts : rapports EXPLAIN, AWR/ASH, slow logs.

Policy-as-Code

# Conftest/OPA (règle simplifiée)
package dbpolicy
deny[msg] {
  input.change.type == "DROP_TABLE"
  msg := "DROP TABLE interdit hors fenêtre approuvée"
}

GitOps

• Source of truth : repo schéma/migrations.
• Tagging/versions = déploiement DB synchronisé avec app.
• Auditabilité complète (qui/quoi/quand).