🔝 Retour au Sommaire

21.5. Roadmap de Montée en Compétence

21.5.2. Parcours DevOps/SRE PostgreSQL

Ce parcours est conçu pour guider les professionnels DevOps et SRE (Site Reliability Engineer) dans leur apprentissage de PostgreSQL. L'accent est mis sur le déploiement, l'automatisation, le monitoring, la haute disponibilité et la gestion en production plutôt que sur le développement SQL.

Note importante : Les durées indiquées sont des estimations basées sur un apprentissage régulier. Votre progression peut varier selon votre expérience préalable en administration système, votre familiarité avec les bases de données et l'intensité de votre pratique.

Prérequis : Ce parcours suppose une connaissance de base de Linux/Unix, des concepts réseau, et idéalement une familiarité avec les outils d'automatisation (Ansible, Terraform) et les conteneurs (Docker).

Phase 1 : Fondations Opérationnelles (0-6 mois)

Objectif de cette phase

Acquérir les compétences essentielles pour installer, configurer et gérer une instance PostgreSQL. À la fin de cette phase, vous serez capable de déployer PostgreSQL, comprendre son architecture interne et effectuer les opérations de maintenance de base.

Compétences à développer

Mois 1-2 : Architecture et Installation

Comprendre l'architecture PostgreSQL :

Avant de déployer PostgreSQL, il est essentiel de comprendre comment il fonctionne en interne.

Modèle client-serveur : PostgreSQL fonctionne avec un processus principal (postmaster) qui gère les connexions entrantes et spawne des processus backend pour chaque connexion client.
Processus d'arrière-plan essentiels :
- postmaster : processus principal, gère les connexions
- background writer : écrit les pages modifiées sur disque
- checkpointer : crée des points de contrôle réguliers
- walwriter : écrit les journaux de transactions (WAL)
- autovacuum launcher : coordonne le nettoyage automatique
- stats collector : collecte les statistiques d'utilisation
Gestion de la mémoire :
- shared_buffers : cache partagé entre tous les processus
- work_mem : mémoire par opération (tri, jointure)
- maintenance_work_mem : mémoire pour les opérations de maintenance
- effective_cache_size : estimation du cache système disponible
Structure physique des données :
- Répertoire de données (PGDATA) : contient toutes les données
- Fichiers de configuration : postgresql.conf, pg_hba.conf, pg_ident.conf
- WAL (Write-Ahead Log) : journaux de transactions pour la durabilité
- Tablespaces : emplacements alternatifs pour les données

Installation de PostgreSQL :

Installation depuis les paquets officiels (méthode recommandée) :
- Dépôt PGDG (PostgreSQL Global Development Group) pour les versions récentes
- Différences entre distributions : Debian/Ubuntu, RHEL/CentOS/Rocky, etc.
- Comprendre ce qui est installé : binaires, bibliothèques, scripts
Structure du système de fichiers après installation :
- /usr/lib/postgresql/<version>/ : binaires
- /etc/postgresql/<version>/<cluster>/ : configuration (Debian)
- /var/lib/postgresql/<version>/<cluster>/ : données (Debian)
- Variations selon les distributions
Initialisation d'un cluster :
- Commande initdb : création du répertoire de données
- Options importantes : --encoding, --locale, --data-checksums
- Nouveauté PostgreSQL 18 : checksums activés par défaut
Gestion du service :
- systemctl : start, stop, restart, reload, status
- pg_ctl : outil natif PostgreSQL pour le contrôle
- Différence entre restart et reload (paramètres nécessitant un redémarrage)

Mois 3-4 : Configuration et Sécurité de Base

Fichiers de configuration essentiels :

postgresql.conf — Configuration principale :

Paramètres de connexion :
- listen_addresses : interfaces réseau écoutées ('*' pour toutes)
- port : port d'écoute (défaut : 5432)
- max_connections : nombre maximum de connexions simultanées
Paramètres mémoire (règles de base pour commencer) :
- shared_buffers : 25% de la RAM système (maximum ~8GB pour commencer)
- effective_cache_size : 50-75% de la RAM système
- work_mem : 32-64MB (attention, multiplié par le nombre d'opérations)
- maintenance_work_mem : 256MB-1GB selon la RAM disponible
Paramètres WAL :
- wal_level : replica pour la réplication, logical pour la réplication logique
- max_wal_size : taille maximale des WAL avant checkpoint
- checkpoint_timeout : intervalle entre checkpoints
pg_hba.conf — Contrôle d'accès :

Ce fichier définit qui peut se connecter, depuis où, et comment.

Format d'une ligne :
```
TYPE  DATABASE  USER  ADDRESS  METHOD
```
Types de connexion :
- local : connexions via socket Unix
- host : connexions TCP/IP (SSL ou non)
- hostssl : connexions TCP/IP avec SSL obligatoire
- hostnossl : connexions TCP/IP sans SSL
Méthodes d'authentification :
- trust : aucune authentification (JAMAIS en production)
- peer : authentification via le système (utilisateur Unix = utilisateur PostgreSQL)
- md5 : mot de passe hashé MD5 (déprécié)
- scram-sha-256 : méthode recommandée pour les mots de passe
- cert : authentification par certificat SSL
- ldap : authentification via annuaire LDAP
- Nouveauté PostgreSQL 18 : oauth pour OAuth 2.0
pg_ident.conf — Mapping d'utilisateurs :
- Associer des utilisateurs système à des utilisateurs PostgreSQL
- Utile avec l'authentification peer ou cert

Sécurité de base :

Principe du moindre privilège : chaque application/utilisateur n'a accès qu'à ce dont il a besoin
Créer des rôles dédiés par application (ne jamais utiliser postgres pour les applications)
Activer SSL/TLS pour les connexions réseau
Restreindre listen_addresses aux interfaces nécessaires
Configurer le pare-feu système (iptables, firewalld, ufw)

Introduction à psql pour les opérations :

Connexion : psql -h host -p port -U user -d database
Commandes méta essentielles :
- \l : lister les bases de données
- \du : lister les rôles
- \dt : lister les tables
- \conninfo : informations de connexion
- \x : affichage étendu (pratique pour les vues système)

Mois 5-6 : Maintenance et Sauvegarde de Base

Comprendre VACUUM :

VACUUM est l'opération de maintenance la plus importante de PostgreSQL. Elle est nécessaire à cause du fonctionnement de MVCC (Multiversion Concurrency Control).

Pourquoi VACUUM est nécessaire :
- PostgreSQL ne supprime pas immédiatement les lignes lors d'un UPDATE ou DELETE
- Les anciennes versions restent pour les transactions en cours
- VACUUM récupère cet espace pour une réutilisation future
- Sans VACUUM, la base grossit indéfiniment ("bloat")
Types de VACUUM :
- VACUUM : marque l'espace comme réutilisable, ne bloque pas les lectures/écritures
- VACUUM FULL : réécrit entièrement la table, récupère l'espace disque, mais verrouille la table
- VACUUM ANALYZE : combine VACUUM et mise à jour des statistiques
Autovacuum :
- Processus automatique qui lance VACUUM et ANALYZE selon les besoins
- Paramètres clés : autovacuum_vacuum_threshold, autovacuum_vacuum_scale_factor
- Supervision : vérifier que l'autovacuum fonctionne correctement
- Nouveauté PostgreSQL 18 : autovacuum_vacuum_max_threshold pour les grandes tables

Comprendre ANALYZE :

Met à jour les statistiques utilisées par le planificateur de requêtes
Sans statistiques à jour, PostgreSQL peut choisir des plans d'exécution inefficaces
Exécuté automatiquement par autovacuum, mais peut être lancé manuellement après des imports massifs

Sauvegardes logiques avec pg_dump :

pg_dump : sauvegarde une base de données

# Format plain SQL (lisible, restaurable avec psql)
pg_dump -h host -U user dbname > backup.sql

# Format custom (compressé, restauration flexible)
pg_dump -h host -U user -Fc dbname > backup.dump

# Format directory (parallélisable)
pg_dump -h host -U user -Fd -j 4 dbname -f backup_dir/

Options importantes :
- -Fc : format custom (recommandé)
- -j N : parallélisation (format directory uniquement)
- --schema-only : structure uniquement
- --data-only : données uniquement
- -t table : table spécifique
- -n schema : schéma spécifique
pg_dumpall : sauvegarde de l'instance complète
- Inclut les rôles et les tablespaces
- Nécessaire pour une restauration complète
```
pg_dumpall -h host -U postgres > full_backup.sql
```

Restauration :

Depuis un dump SQL plain :

psql -h host -U user -d dbname < backup.sql

Depuis un dump format custom :

pg_restore -h host -U user -d dbname backup.dump

Options utiles de pg_restore :
- -j N : restauration parallèle
- -c : drop des objets avant création
- --if-exists : évite les erreurs si objets absents
- -t table : restaurer une table spécifique

Planification des sauvegardes :

Utiliser cron ou systemd timers pour automatiser
Rotation des sauvegardes (conserver N jours/semaines)
Tester régulièrement les restaurations

Indicateurs de progression (Phase 1)

À la fin de cette phase, vous devriez être capable de :

Installer PostgreSQL depuis les dépôts officiels sur les principales distributions Linux
Configurer les paramètres essentiels de postgresql.conf et pg_hba.conf
Sécuriser l'accès à une instance PostgreSQL (SSL, authentification, pare-feu)
Comprendre le rôle de VACUUM et d'autovacuum
Effectuer des sauvegardes logiques avec pg_dump et les restaurer
Utiliser psql pour les opérations d'administration courantes

Ressources recommandées (Phase 1)

Documentation officielle PostgreSQL : chapitres "Server Setup and Operation" et "Backup and Restore"
Wiki PostgreSQL : articles sur la configuration et le tuning
PGTune (pgtune.leopard.in.ua) : génération de configuration de base

Phase 2 : Opérations Avancées et Automatisation (6-12 mois)

Objectif de cette phase

Maîtriser les opérations avancées de maintenance, mettre en place un monitoring efficace, automatiser les déploiements et comprendre les bases de la réplication. Vous serez capable de gérer PostgreSQL de manière professionnelle avec des outils modernes.

Compétences à développer

Mois 7-8 : Monitoring et Observabilité

Vues système essentielles :

PostgreSQL expose de nombreuses informations via des vues système. Les maîtriser est essentiel pour tout DevOps/SRE.

pg_stat_activity — Activité en temps réel :
```
SELECT pid, usename, application_name, client_addr,
       state, query_start, query
FROM pg_stat_activity
WHERE state != 'idle';
```
- state : idle, active, idle in transaction, etc.
- wait_event_type et wait_event : ce sur quoi le processus attend
- query : requête en cours (attention aux informations sensibles)
pg_stat_database — Statistiques par base :
- numbackends : connexions actives
- xact_commit, xact_rollback : transactions
- blks_read, blks_hit : accès disque vs cache
- tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted : activité
pg_stat_user_tables — Statistiques par table :
- seq_scan, idx_scan : scans séquentiels vs index
- n_tup_ins, n_tup_upd, n_tup_del : modifications
- n_live_tup, n_dead_tup : tuples vivants vs morts (bloat)
- last_vacuum, last_autovacuum, last_analyze : dernières maintenances
pg_stat_user_indexes — Utilisation des index :
- idx_scan : nombre de fois que l'index a été utilisé
- Index avec 0 scans = candidats à la suppression

pg_locks — Verrous actifs :

SELECT l.pid, l.locktype, l.mode, l.granted,
       a.usename, a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid;

Extension pg_stat_statements :

Cette extension est indispensable pour identifier les requêtes problématiques.

Installation :
```
CREATE EXTENSION pg_stat_statements;
```

Configuration dans postgresql.conf :

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Requêtes utiles :

-- Top 10 requêtes par temps total
SELECT query, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Métriques vitales à surveiller :

Cache hit ratio (objectif > 99%) :

SELECT
  sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) as cache_hit_ratio
FROM pg_stat_database;

Connexions :
- Connexions actives vs max_connections
- Connexions en état "idle in transaction" (problématiques)
Bloat (espace perdu) :
- Ratio n_dead_tup / n_live_tup dans pg_stat_user_tables
- Outils : pgstattuple, requêtes d'estimation du bloat

Réplication lag (si applicable) :

SELECT client_addr, state, sent_lsn, write_lsn,
       flush_lsn, replay_lsn
FROM pg_stat_replication;

Transaction ID age (risque de wraparound) :

SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY age DESC;

Stack de monitoring :

Prometheus + postgres_exporter :
- postgres_exporter expose les métriques au format Prometheus
- Configuration : connexion à PostgreSQL, métriques à collecter
- Alertes sur les métriques critiques
Grafana :
- Dashboards pour visualiser les métriques
- Dashboards communautaires disponibles (ex: Percona PMM)
pgBadger :
- Analyse des logs PostgreSQL
- Génère des rapports HTML détaillés
- Identification des requêtes lentes, erreurs, connexions
Logging PostgreSQL :
- log_statement : none, ddl, mod, all
- log_min_duration_statement : logger les requêtes lentes
- log_line_prefix : format des lignes de log (inclure timestamp, pid, user, db)

Mois 9-10 : Sauvegardes Avancées et PITR

Sauvegardes physiques avec pg_basebackup :

Contrairement à pg_dump (logique), pg_basebackup copie les fichiers physiques de la base.

Avantages :
- Plus rapide pour les grandes bases
- Permet le Point-In-Time Recovery (PITR)
- Base pour la réplication
Utilisation de base :
```
pg_basebackup -h host -U replication_user \
  -D /path/to/backup -Ft -z -P
```
- -Ft : format tar
- -z : compression gzip
- -P : affichage de la progression
- -X stream : inclure les WAL nécessaires

Point-In-Time Recovery (PITR) :

PITR permet de restaurer la base à n'importe quel instant dans le passé.

Principe :
1. Restaurer une sauvegarde de base (pg_basebackup)
2. Rejouer les WAL archivés jusqu'au point souhaité

Configuration de l'archivage WAL :

# postgresql.conf
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

Restauration PITR :
1. Arrêter PostgreSQL
2. Restaurer la sauvegarde de base
3. Configurer recovery.signal et les paramètres de recovery
4. Démarrer PostgreSQL

Paramètres de recovery :

# postgresql.conf (pour la restauration)
restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2025-11-15 14:30:00'
recovery_target_action = 'promote'

Outils de sauvegarde avancés :

pgBackRest :
- Sauvegardes complètes, différentielles, incrémentales
- Compression, chiffrement
- Parallélisation
- Gestion automatique de la rétention
- Vérification d'intégrité
Barman :
- Développé par 2ndQuadrant/EDB
- Gestion centralisée des sauvegardes
- Catalogage des sauvegardes
- Recovery simplifié

Stratégie de sauvegarde 3-2-1 :

3 copies des données
2 supports de stockage différents
1 copie hors site (autre datacenter, cloud)

Mois 11-12 : Automatisation et Infrastructure as Code

Ansible pour PostgreSQL :

Rôles Ansible communautaires :
- geerlingguy.postgresql : installation de base
- ANXS.postgresql : configuration avancée
- Rôles Patroni pour la HA
Tâches typiques à automatiser :
- Installation et configuration initiale
- Création de bases et utilisateurs
- Déploiement de configuration
- Mise à jour des versions mineures

Exemple de playbook simplifié :

- name: Configure PostgreSQL
  hosts: postgres_servers
  roles:
    - role: postgresql
      postgresql_version: 18
      postgresql_listen_addresses: "*"
      postgresql_max_connections: 200

Terraform pour le provisioning :

Provisioning d'instances PostgreSQL managées :
- AWS RDS PostgreSQL
- Azure Database for PostgreSQL
- Google Cloud SQL
Provisioning d'infrastructure pour PostgreSQL self-hosted :
- VMs, réseaux, stockage
- Load balancers pour la HA

Docker et PostgreSQL :

Image officielle : postgres:18
Configuration via variables d'environnement :
- POSTGRES_PASSWORD : mot de passe du superuser
- POSTGRES_USER : utilisateur par défaut
- POSTGRES_DB : base de données par défaut
Volumes pour la persistance :
- /var/lib/postgresql/data : données
- Configuration personnalisée via fichiers montés
Considérations pour la production :
- Stockage persistant approprié (pas de stockage éphémère)
- Configuration mémoire adaptée au conteneur
- Health checks

Docker Compose pour le développement :

services:
  postgres:
    image: postgres:18
    environment:
      POSTGRES_PASSWORD: secret
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - "5432:5432"
volumes:
  pgdata:

Connection Pooling avec PgBouncer :

PostgreSQL crée un processus par connexion, ce qui limite le nombre de connexions simultanées. PgBouncer résout ce problème.

Modes de pooling :
- session : une connexion PostgreSQL par session client
- transaction : partage des connexions entre transactions (recommandé)
- statement : partage maximal, mais limitations importantes

Configuration de base :

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Dimensionnement :
- default_pool_size : connexions PostgreSQL par base/utilisateur
- max_client_conn : connexions clients maximum
- Règle : default_pool_size × nombre de pools < max_connections PostgreSQL

Indicateurs de progression (Phase 2)

À la fin de cette phase, vous devriez être capable de :

Mettre en place un monitoring complet avec Prometheus/Grafana
Identifier les requêtes problématiques avec pg_stat_statements
Configurer l'archivage WAL et effectuer des restaurations PITR
Utiliser pgBackRest ou Barman pour des sauvegardes avancées
Automatiser les déploiements PostgreSQL avec Ansible
Déployer PostgreSQL dans des conteneurs Docker
Configurer PgBouncer pour le connection pooling

Ressources recommandées (Phase 2)

Documentation pgBackRest et Barman
"PostgreSQL 14 Administration Cookbook"
Blogs : Percona, 2ndQuadrant/EDB, Crunchy Data
Ansible Galaxy : rôles PostgreSQL communautaires

Phase 3 : Haute Disponibilité et Expertise Production (12-24 mois)

Objectif de cette phase

Maîtriser les architectures haute disponibilité, la réplication, les migrations majeures et la gestion des incidents. Vous serez capable de concevoir et opérer des infrastructures PostgreSQL critiques et de répondre efficacement aux situations de crise.

Compétences à développer

Mois 13-16 : Réplication et Haute Disponibilité

Réplication physique (Streaming Replication) :

La réplication physique crée une copie exacte du primary sur un ou plusieurs standbys.

Concepts clés :
- Primary (anciennement master) : accepte les écritures
- Standby (anciennement slave) : réplique en lecture seule
- WAL : les modifications sont transmises via les journaux de transaction

Configuration du Primary :

# postgresql.conf
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

# pg_hba.conf
host replication replication_user standby_ip/32 scram-sha-256

Configuration du Standby :
1. Créer avec pg_basebackup :
```
pg_basebackup -h primary -U replication_user -D /data -P -R
```
2. Le flag -R crée automatiquement standby.signal et configure la connexion
Synchrone vs Asynchrone :
- Asynchrone (défaut) : le primary n'attend pas la confirmation du standby
- Synchrone : le primary attend la confirmation avant de confirmer le commit
- Trade-off : durabilité vs performance
Slots de réplication :
- Garantissent que les WAL nécessaires sont conservés
- Attention : peuvent causer une accumulation de WAL si le standby est déconnecté

Réplication logique :

La réplication logique permet de répliquer des tables spécifiques et offre plus de flexibilité.

Cas d'usage :
- Réplication sélective (certaines tables seulement)
- Réplication entre versions PostgreSQL différentes
- Migrations avec temps d'arrêt minimal

Configuration :

# postgresql.conf sur le publisher
wal_level = logical

-- Sur le publisher
CREATE PUBLICATION my_pub FOR TABLE table1, table2;

-- Sur le subscriber
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=publisher dbname=mydb'
  PUBLICATION my_pub;

Solutions de haute disponibilité :

Patroni :
- Solution de HA la plus populaire
- Utilise un système de consensus distribué (etcd, Consul, ZooKeeper)
- Gestion automatique du failover
- API REST pour le management
Architecture typique :
```
[etcd cluster] ←→ [Patroni + PostgreSQL] × 3 nodes
                         ↓
                  [HAProxy/PgBouncer]
                         ↓
                  [Applications]
```
Repmgr :
- Plus simple que Patroni
- Bon pour les environnements moins critiques
- Failover automatique ou manuel
PgPool-II :
- Connection pooling + load balancing + failover
- Alternative tout-en-un, mais plus complexe

Failover et Promotion :

Promotion manuelle :

pg_ctl promote -D /data
# ou
SELECT pg_promote();

Avec Patroni :

patronictl switchover
patronictl failover

Considérations post-failover :
- Mettre à jour les chaînes de connexion ou utiliser un VIP/DNS
- Reconfigurer l'ancien primary comme standby
- Vérifier l'intégrité des données

Mois 17-20 : PostgreSQL sur Kubernetes et Cloud

Kubernetes et PostgreSQL :

StatefulSets :
- Identité stable pour chaque pod
- Stockage persistant avec PersistentVolumeClaims
- Ordre de déploiement/suppression garanti
Operators Kubernetes :

Les operators automatisent la gestion de PostgreSQL sur Kubernetes.
- CloudNativePG (anciennement Cloud Native PostgreSQL) :
  - Développé par EDB
  - Natif Kubernetes, déclaratif
  - Réplication, failover automatique, backups
- Zalando Postgres Operator :
  - Utilisé en production chez Zalando
  - Intégration avec Patroni
  - Gestion du connection pooling
- Crunchy Postgres Operator (PGO) :
  - Solution complète de Crunchy Data
  - Monitoring intégré (pgMonitor)
  - Backups avec pgBackRest
Considérations stockage :
- Utiliser des StorageClasses performantes (SSD)
- Attention aux IOPS et à la latence
- Ne jamais utiliser de stockage éphémère pour les données

PostgreSQL managé dans le cloud :

AWS RDS PostgreSQL :
- Versions PostgreSQL supportées
- Multi-AZ pour la haute disponibilité
- Read replicas pour le scaling en lecture
- Automated backups et PITR
- Performance Insights pour le monitoring
Amazon Aurora PostgreSQL :
- Architecture de stockage distribuée
- Failover plus rapide que RDS standard
- Aurora Serverless pour les charges variables
Azure Database for PostgreSQL :
- Options Flexible Server (recommandé) vs Single Server (legacy)
- Réplication en lecture
- Intégration avec Azure Monitor
Google Cloud SQL / AlloyDB :
- Cloud SQL : PostgreSQL managé classique
- AlloyDB : compatible PostgreSQL avec architecture distribuée

Trade-offs Managed vs Self-hosted :

Aspect	Managed	Self-hosted
Maintenance	Provider	Votre équipe
Personnalisation	Limitée	Totale
Coût	Prévisible, souvent plus élevé	Variable, potentiellement moins cher
Extensions	Liste restreinte	Toutes
Contrôle	Limité	Total

Mois 21-24 : Migrations, Troubleshooting et Expertise

Migrations de versions majeures :

pg_upgrade :

Migration in-place rapide
Modes : --copy (copie les fichiers) ou --link (liens symboliques, plus rapide)
Nouveauté PostgreSQL 18 : option --swap pour upgrade encore plus rapide
Nouveauté PostgreSQL 18 : préservation des statistiques
Nouveauté PostgreSQL 18 : vérifications parallèles avec --jobs

Processus :

# 1. Arrêter l'ancien cluster
# 2. Vérifier la compatibilité
pg_upgrade --check -b /old/bin -B /new/bin -d /old/data -D /new/data

# 3. Effectuer la migration
pg_upgrade -b /old/bin -B /new/bin -d /old/data -D /new/data

# 4. Démarrer le nouveau cluster
# 5. Analyser les statistiques (ou utiliser les statistiques préservées en PG18)

Migration par réplication logique :
- Temps d'arrêt minimal
- Permet de tester avant le basculement
- Plus complexe à mettre en œuvre
Stratégie Blue/Green :
- Deux environnements identiques
- Basculement rapide via DNS ou load balancer
- Possibilité de rollback

Troubleshooting avancé :

Problèmes de verrous :

-- Identifier les sessions bloquantes
SELECT pg_blocking_pids(pid), * FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Terminer une session bloquante (avec précaution)
SELECT pg_terminate_backend(pid);

Transaction ID Wraparound :
- PostgreSQL utilise des identifiants de transaction sur 32 bits
- À ~2 milliards de transactions, risque de wraparound
- VACUUM freeze prévient ce problème
- Alerter si age(datfrozenxid) approche 1 milliard
```
-- Vérifier l'âge des transactions
SELECT datname, age(datfrozenxid),
       2147483647 - age(datfrozenxid) as remaining
FROM pg_database
ORDER BY age DESC;
```
Corruption de données :
- Vérifier les checksums : pg_checksums --check -D /data
- Nouveauté PostgreSQL 18 : checksums activés par défaut
- Outils : pg_amcheck pour vérifier l'intégrité des index
Saturation des connexions :
- Identifier les connexions idle in transaction
- Configurer idle_in_transaction_session_timeout
- Utiliser PgBouncer pour le pooling
Requêtes lentes :
1. Identifier avec pg_stat_statements
2. Analyser avec EXPLAIN (ANALYZE, BUFFERS)
3. Vérifier les index manquants
4. Vérifier les statistiques (ANALYZE)

Sécurité avancée :

Row-Level Security (RLS) :

Contrôle d'accès au niveau des lignes
Utile pour le multi-tenant

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

Audit avec pgAudit :
- Logging détaillé des opérations
- Conformité réglementaire (SOC2, HIPAA, etc.)
Chiffrement :
- En transit : SSL/TLS (obligatoire en production)
- Au repos : chiffrement du système de fichiers ou TDE (Transparent Data Encryption)
- Nouveauté PostgreSQL 18 : configuration TLS 1.3 avec ssl_tls13_ciphers
Gestion des secrets :
- Ne jamais stocker les mots de passe en clair
- Utiliser des gestionnaires de secrets (Vault, AWS Secrets Manager, etc.)
- Rotation régulière des credentials

Documentation et Runbooks :

Documenter l'architecture et les décisions
Créer des runbooks pour les opérations courantes :
- Failover manuel
- Restauration de sauvegarde
- Ajout d'un standby
- Réponse aux alertes
Maintenir un inventaire des instances
Documenter les dépendances et les SLA

Indicateurs de progression (Phase 3)

À la fin de cette phase, vous devriez être capable de :

Concevoir et implémenter une architecture haute disponibilité avec Patroni
Configurer la réplication physique et logique
Déployer PostgreSQL sur Kubernetes avec un operator
Gérer des instances PostgreSQL dans le cloud (RDS, Cloud SQL, etc.)
Effectuer des migrations de versions majeures avec différentes stratégies
Diagnostiquer et résoudre des problèmes complexes (locks, wraparound, corruption)
Mettre en place une sécurité avancée (RLS, audit, chiffrement)
Documenter et créer des runbooks pour les opérations

Ressources recommandées (Phase 3)

Documentation Patroni, CloudNativePG, Zalando Operator
"Mastering PostgreSQL" de Hans-Jürgen Schönig
Conférences : PGConf, KubeCon (sessions PostgreSQL)
Blogs : Percona, Crunchy Data, EDB, Cybertec
Formation Kubernetes si pas encore maîtrisé

Conseils Spécifiques pour le Parcours DevOps/SRE

Privilégier la fiabilité

En tant que DevOps/SRE, votre priorité est la fiabilité du service. Cela signifie :

Toujours avoir des sauvegardes testées et restaurables
Ne jamais déployer une configuration non testée en production
Préférer les approches éprouvées aux solutions innovantes mais risquées

Automatiser, mais comprendre

L'automatisation est essentielle, mais vous devez comprendre ce que font vos scripts et outils. En cas d'incident, vous devrez peut-être intervenir manuellement.

Tester les scénarios de défaillance

Simulez régulièrement des pannes :

Failover de la base de données
Restauration de sauvegarde
Perte d'un nœud du cluster

Monitorer proactivement

N'attendez pas qu'un utilisateur signale un problème. Mettez en place des alertes sur :

Espace disque
Connexions
Lag de réplication
Requêtes lentes
Âge des transactions (wraparound)

Documenter tout

La documentation est critique :

Architecture et diagrammes
Procédures de recovery
Contacts et escalade
Post-mortems des incidents

Collaborer avec les développeurs

Travaillez avec les équipes de développement pour :

Optimiser les requêtes problématiques
Planifier les migrations de schéma
Dimensionner correctement l'infrastructure

Récapitulatif des Phases

Phase	Période	Focus Principal	Compétences Clés
1. Fondations	0-6 mois	Installation, configuration, maintenance de base	Architecture, pg_hba.conf, VACUUM, pg_dump
2. Opérations avancées	6-12 mois	Monitoring, sauvegardes avancées, automatisation	pg_stat_statements, PITR, pgBackRest, Ansible, Docker, PgBouncer
3. HA et Expertise	12-24 mois	Haute disponibilité, Kubernetes, cloud, troubleshooting	Patroni, réplication, operators K8s, pg_upgrade, sécurité avancée

Certifications et Reconnaissance

Bien que PostgreSQL n'ait pas de certification officielle comme Oracle ou Microsoft, plusieurs options existent :

EnterpriseDB (EDB) : Certifications PostgreSQL Professional
Percona : Certifications orientées performance et opérations
Cloud providers : Certifications incluant des modules PostgreSQL (AWS, Azure, GCP)

Ces certifications peuvent valider vos compétences auprès des employeurs, mais l'expérience pratique reste le critère le plus important.

⏭️ Parcours DBA

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

21.5. Roadmap de Montée en Compétence

21.5.2. Parcours DevOps/SRE PostgreSQL

Phase 1 : Fondations Opérationnelles (0-6 mois)

Objectif de cette phase

Compétences à développer

Mois 1-2 : Architecture et Installation

Mois 3-4 : Configuration et Sécurité de Base

Mois 5-6 : Maintenance et Sauvegarde de Base

Indicateurs de progression (Phase 1)

Ressources recommandées (Phase 1)

Phase 2 : Opérations Avancées et Automatisation (6-12 mois)

Objectif de cette phase

Compétences à développer

Mois 7-8 : Monitoring et Observabilité

Mois 9-10 : Sauvegardes Avancées et PITR

Mois 11-12 : Automatisation et Infrastructure as Code

Indicateurs de progression (Phase 2)

Ressources recommandées (Phase 2)

Phase 3 : Haute Disponibilité et Expertise Production (12-24 mois)

Objectif de cette phase

Compétences à développer

Mois 13-16 : Réplication et Haute Disponibilité

Mois 17-20 : PostgreSQL sur Kubernetes et Cloud

Mois 21-24 : Migrations, Troubleshooting et Expertise

Indicateurs de progression (Phase 3)

Ressources recommandées (Phase 3)

Conseils Spécifiques pour le Parcours DevOps/SRE

Privilégier la fiabilité

Automatiser, mais comprendre

Tester les scénarios de défaillance

Monitorer proactivement

Documenter tout

Collaborer avec les développeurs

Récapitulatif des Phases

Certifications et Reconnaissance

FilesExpand file tree

05.2-parcours-devops-sre.md

Latest commit

History

05.2-parcours-devops-sre.md

File metadata and controls

21.5. Roadmap de Montée en Compétence

21.5.2. Parcours DevOps/SRE PostgreSQL

Phase 1 : Fondations Opérationnelles (0-6 mois)

Objectif de cette phase

Compétences à développer

Mois 1-2 : Architecture et Installation

Mois 3-4 : Configuration et Sécurité de Base

Mois 5-6 : Maintenance et Sauvegarde de Base

Indicateurs de progression (Phase 1)

Ressources recommandées (Phase 1)

Phase 2 : Opérations Avancées et Automatisation (6-12 mois)

Objectif de cette phase

Compétences à développer

Mois 7-8 : Monitoring et Observabilité

Mois 9-10 : Sauvegardes Avancées et PITR

Mois 11-12 : Automatisation et Infrastructure as Code

Indicateurs de progression (Phase 2)

Ressources recommandées (Phase 2)

Phase 3 : Haute Disponibilité et Expertise Production (12-24 mois)

Objectif de cette phase

Compétences à développer

Mois 13-16 : Réplication et Haute Disponibilité

Mois 17-20 : PostgreSQL sur Kubernetes et Cloud

Mois 21-24 : Migrations, Troubleshooting et Expertise

Indicateurs de progression (Phase 3)

Ressources recommandées (Phase 3)

Conseils Spécifiques pour le Parcours DevOps/SRE

Privilégier la fiabilité

Automatiser, mais comprendre

Tester les scénarios de défaillance

Monitorer proactivement

Documenter tout

Collaborer avec les développeurs

Récapitulatif des Phases

Certifications et Reconnaissance