PostgreSQL - Optimiser l'administration des bases de données

Objectif : Maîtriser le fonctionnement interne de PostgreSQL (processus d'arrière-plan, gestion de la mémoire). Optimiser les paramètres système clés pour s'adapter aux ressources matérielles.

• Architecture détaillée des processus : Postmaster, Writer, Wal Writer, Autovacuum.
• Configuration de la mémoire : shared_buffers, work_mem, maintenance_work_mem, effective_cache_size.
• Optimisation des écritures disques et gestion fine des points de contrôle (Checkpoints).

Atelier pratique : Calcul et application d'un profil d'optimisation mémoire adapté à un serveur de production doté de fortes ressources matérielles.

Cas métier : Redimensionnement des ressources d'un serveur PostgreSQL hébergeant une application ERP à forte charge d'accès concurrents.

Compétence acquise : Savoir dimensionner et configurer les paramètres de performance d'un serveur PostgreSQL de production.

Livrable : Fichier de configuration postgresql.conf optimisé et documenté selon le profil matériel.

Objectif : Identifier, diagnostiquer et résoudre les situations de blocage et de contentions d'accès. Analyser le comportement du nettoyeur de table (Autovacuum).

• Les types de verrous dans PostgreSQL (verrous de lignes, de tables, verrous système).
• Analyse de la table système pg_locks et résolution des interblocages (Deadlocks).
• Mécanisme MVCC : gestion des versions de lignes et impact sur la fragmentation physique.
• Paramétrage approfondi et diagnostic de l'activité de l'Autovacuum.

Atelier pratique : Simulation d'une contention d'écriture sur une table critique, détection des requêtes bloquantes et libération forcée de verrous.

Cas métier : Analyse et résolution de ralentissements applicatifs majeurs causés par des requêtes de mise à jour bloquantes sur un stock de marchandises.

Compétence acquise : Résoudre rapidement les situations de verrouillage et garantir un nettoyage de base de données performant.

Livrable : Script de supervision et de détection des requêtes bloquantes à chaud.

Objectif : Configurer l'archivage continu des journaux de transactions (WAL). Mettre en œuvre une stratégie de restauration fine avec Point-in-Time Recovery (PITR).

• Principes physiques du journal d'écriture anticipée (WAL).
• Configuration du mode archive_mode et scripts d'archivage sécurisés.
• Sauvegardes physiques à chaud à l'aide de l'outil pg_basebackup.
• Restauration pas-à-pas et positionnement de la cible temporelle de restauration (recovery_target_time).

Atelier pratique : Réalisation d'une sauvegarde à chaud, simulation d'un crash de données, et restauration complète de la base de données à une heure précise.

Cas métier : Incident de production critique nécessitant de restaurer le registre comptable de l'entreprise 5 minutes avant une erreur humaine de suppression.

Compétence acquise : Maîtriser les restaurations d'infrastructures les plus exigeantes sans perte de données.

Livrable : Procédure documentée de reprise d'activité post-incident majeure (PRA).

Objectif : Configurer un cluster PostgreSQL répliqué en lecture seule. Comprendre les mécanismes de basculement automatique.

• Principes de la réplication physique par flux (Streaming Replication).
• Configuration du serveur maître (primary) et du serveur esclave (standby)</w:r><m:oMath><m:r><w:rPr><w:rFonts w:ascii="Cambria Math" w:hAnsi="Cambria Math"/></w:rPr><m:t>Standby</m:t></m:r></m:oMath><w:r><w:rPr><w:rFonts w:ascii="Aptos" w:hAnsi="Aptos" w:cs="Aptos"/><w:sz w:val="24"/><w:szCs w:val="24"/></w:rPr><w:t xml:space="preserve">.
• Différence et cas d'usage : réplication synchrone vs asynchrone.
• Introduction aux architectures de haute disponibilité de l'écosystème PostgreSQL (Patroni, repmgr).

Atelier pratique : Déploiement complet d'une architecture maître/esclave avec réplication en temps réel et validation du transfert de charge.

Cas métier : Découplage de la base de production pour déporter la génération des rapports lourds de Business Intelligence sur un serveur secondaire en lecture seule.

Compétence acquise : Savoir concevoir et déployer une architecture PostgreSQL distribuée hautement résiliente.

Livrable : Cluster PostgreSQL opérationnel à deux nœuds répliqués en continu.

Objectif : Sécuriser l'accès réseau et authentifier les utilisateurs selon des protocoles stricts. Mettre en œuvre un système d'audit d'accès aux données sensibles.

• Configuration avancée du fichier d'autorisation réseau pg_hba.conf.
• Gestion fine des rôles, des privilèges système et des privilèges par défaut.
• Configuration des connexions chiffrées en SSL/TLS (certificats clients/serveurs).
• Traçabilité et audit des requêtes sensibles à l'aide de l'extension pgaudit.

Atelier pratique : Sécurisation complète d'une instance PostgreSQL (connexion SSL obligatoire, restriction d'accès IP, mise en place d'un audit de lecture de table confidentielle).

Cas métier : Mise en conformité RGPD d'un serveur contenant des données de santé personnelles en traçant l'ensemble des requêtes de consultation de données.

Compétence acquise : Sécuriser et auditer une infrastructure de base de données d'entreprise selon les standards du marché.

Livrable : Schéma d'authentification blindé et configuration d'audit de sécurité opérationnelle.

Objectif : Gérer de très grands volumes de données grâce au partitionnement natif de tables. Automatiser les tâches de maintenance quotidiennes et la surveillance d'activité.

• Concept et mise en œuvre du partitionnement déclaratif (par liste, par plage de valeurs, par hachage).
• Optimisation des performances sur des tables de plusieurs centaines de millions de lignes.
• Automatisation des indexations, statistiques et réorganisations physiques (utilisation de pg_repack pour défragmenter sans blocage).
• Métriques clés de supervision à surveiller en production.

Atelier pratique : Transformation à chaud d'une table historique monolithique en table partitionnée par mois et validation de la purge rapide de données.

Cas métier : Conception de l'architecture de stockage de données pour un outil de suivi d'activité logistique conservant 5 ans d'historique de mouvements de colis.

Compétence acquise : Gérer l'évolution physique et la maintenance automatisée de bases de données à forte volumétrie.

Livrable : Script de création de table partitionnée dynamiquement et calendrier d'automatisation de maintenance.