PostgreSQL - Administrer une base de données

Objectif : Maîtriser l'architecture interne et les processus d'arrière-plan de PostgreSQL. Installer et initialiser proprement une instance PostgreSQL sous Linux.

• Concepts clés : l'architecture multi-processus, le rôle du Postmaster et des processus de service.
• Organisation de la mémoire : Shared Buffers, Work Mem, Maintenance Work Mem.
• Le processus d'installation système sous environnement Linux (gestion des paquets officiels).
• Initialiser l'instance : l'utilitaire initdb et la structure physique des répertoires (PGDATA).

Atelier pratique : Installation complète de PostgreSQL et organisation de l'arborescence des fichiers système de données.

Cas métier : Installation et initialisation d'une instance PostgreSQL sous environnement Linux avec isolation des fichiers de données (PGDATA) et des journaux de transaction (WAL) sur des volumes disques distincts.

Compétence acquise : Être en mesure de mettre en production proprement une nouvelle instance de base de données.

Livrable : Une instance PostgreSQL fonctionnelle, propre et structurée.

Objectif : Ouvrir et filtrer les accès réseau au serveur PostgreSQL de façon sécurisée. Structurer et administrer l'authentification des utilisateurs.

• Configurer l'écoute réseau : les paramètres postgresql.conf (listen_addresses, port).
• Gérer le contrôle d'accès basé sur l'hôte : la syntaxe et la logique du fichier pg_hba.conf.
• Rôles et connexions : distinction entre utilisateurs d'application, administrateurs et groupes.
• Chiffrement des connexions : mise en œuvre de SSL pour les échanges de paquets.

Atelier pratique : Configuration complète de la sécurité réseau pour permettre à un client externe sécurisé de requêter l'instance.

Cas métier : Configuration du pare-feu logique et filtrage strict des accès réseau (pg_hba.conf) pour autoriser un serveur applicatif distant via un canal chiffré SSL.

Compétence acquise : Savoir configurer le pare-feu logique de PostgreSQL et sécuriser les échanges distants.

Livrable : Fichiers postgresql.conf et pg_hba.conf configurés selon les standards de sécurité d'entreprise.

Objectif : Maîtriser la hiérarchie et la gestion fine des droits d'accès. Organiser la base de données de manière logique grâce aux Schémas.

• Le concept de Schéma : isoler logiquement les tables et fonctions au sein d'une même base de données.
• Attribution des droits : l'instruction GRANT et la révocation via REVOKE.
• Rôles par défaut de PostgreSQL (ex : pg_read_all_data, pg_write_all_data).
• Gérer les droits par défaut sur les futurs objets créés : ALTER DEFAULT PRIVILEGES.

Atelier pratique : Modélisation d'une politique d'accès stricte séparant des profils Lecteurs, Éditeurs et Administrateurs applicatifs.

Cas métier : Conception d'une politique de contrôle d'accès fondée sur une matrice de rôles cloisonnés (Lecteurs, Éditeurs, Administrateurs) au sein des schémas de production.

Compétence acquise : Pouvoir auditer et configurer une gouvernance d'accès propre et cloisonnée sur vos schémas de production.

Livrable : Script SQL de création de rôles, de groupes et de paramétrage par défaut des privilèges.

Objectif : Comprendre le stockage physique (notion de MVCC et de versioning des lignes). Configurer et planifier les tâches de maintenance obligatoires pour éviter l'engorgement (bloat).

• Le fonctionnement de l'isolation des transactions : comprendre l'écriture des versions de lignes (MVCC).
• Le phénomène de « Bloat » (lignes mortes accumulées sur le disque) et l'impact sur les performances.
• Les outils de nettoyage : l'importance capitale des commandes VACUUM, ANALYZE et REINDEX.
• Configurer l'Autovacuum : ajuster les paramètres automatiques pour qu'ils soient efficaces sans saturer les disques.

Atelier pratique : Simulation d'une écriture massive de modifications provoquant de l'engorgement, et exécution de diagnostics de maintenance.

Cas métier : Diagnostic d'engorgement physique (Bloat) à la suite d'une mise à jour massive de lignes et paramétrage sur-mesure de l'Autovacuum pour pérenniser l'espace disque.

Compétence acquise : Savoir maintenir les performances de stockage de l'instance dans la durée en limitant la fragmentation physique.

Livrable : Script de configuration affinée de l'Autovacuum et de requêtes d'analyse du Bloat.

Objectif : Maîtriser l'écriture des scripts de sauvegarde logique. Mettre en œuvre le mécanisme de sauvegarde physique et de synchronisation des journaux de transaction (WAL).

• Sauvegarde logique d'une base ou d'un cluster : utilisation de pg_dump, pg_dumpall et pg_restore.
• Présentation de la sauvegarde physique : le clonage de données avec pg_basebackup.
• Comprendre le cycle de vie du journal de transaction (WAL) et son archivage (archive_mode, archive_command).
• Les stratégies de reprise sur incident d'exploitation.

Atelier pratique : Planification d'une sauvegarde physique à froid et à chaud de l'instance PostgreSQL avec génération de fichiers d'export.

Cas métier : Établissement d'une politique de sauvegarde automatisée couplant des exports logiques (pg_dump) et un archivage continu des journaux de transactions (pg_basebackup / WAL).

Compétence acquise : Être en mesure de concevoir et d'automatiser une politique de sauvegarde système robuste.

Livrable : Script de sauvegarde automatisé configuré et fiches de procédures associées.

Objectif : Surveiller en temps réel l'état de santé et l'activité de vos serveurs de bases de données. Exécuter avec succès une procédure complexe de reconstruction de données après sinistre (Point-in-Time Recovery).

• Les vues de diagnostic système essentielles : pg_stat_activity, pg_stat_database.
• Analyser les verrous bloquants entre sessions concurrentes : exploiter les vues de blocages.
• Paramétrer le fichier de trace pour auditer les requêtes lentes (log_min_duration_statement).
• Procédure complète de PITR (Point-in-Time Recovery) : reconstruire l'instance à la seconde près avant l'incident.

Atelier pratique : Simulation de sinistre (destruction de données métiers de production) et reconstruction pas-à-pas de la base à l'état exact précédant l'erreur de manipulation (PITR).

Cas métier : Simulation de suppression accidentelle de la base de données de production et reconstruction intégrale pas-à-pas de l'instance à la seconde près précédant l'incident (PITR - Point-in-Time Recovery).

Compétence acquise : Savoir réagir avec sang-froid et méthode face à une panne de production et rétablir le service sans perte d'information.

Livrable : Procédure pas-à-pas formalisée de restauration après sinistre (Point-In-Time Recovery) et requêtes d'exploitation de monitoring.

Objectif : Réutiliser en autonomie l'installation, la configuration réseau, les règles d'accès, les rôles et les schémas des journées ESSENTIEL.

• Réactiver les acquis des journées ESSENTIEL : rappel visuel et quiz sur l'architecture de l'instance, postgresql.conf, pg_hba.conf, les rôles, les privilèges et les schémas.
• Préparer une instance pour une nouvelle application : réseau, accès, rôles, schémas et droits.

Atelier pratique : Entraînement en binômes : auditer une configuration volontairement risquée (accès ouvert à tous, superutilisateur partagé, droits trop larges) et la corriger, avec correction croisée.

Cas métier : La nouvelle application métier (cas fil rouge) - Les participants configurent l'instance, ouvrent les accès strictement nécessaires, créent les rôles et les schémas de l'application et vérifient chaque connexion, puis présentent leur configuration au groupe.

Compétence acquise : Savoir mettre en service en autonomie une instance PostgreSQL sécurisée.

Livrable : L'instance configurée et sa fiche d'exploitation, construites avec les mémos des journées ESSENTIEL.

Objectif : Mettre à l'épreuve l'entretien du stockage, les sauvegardes, la surveillance et la restauration face à un incident réaliste.

• Retour sur VACUUM, les sauvegardes logiques et physiques, la surveillance et la restauration vus les jours précédents.
• Détecter un problème grâce à la surveillance et restaurer les données en limitant les pertes.

Atelier pratique : Exercice guidé : restaurer une sauvegarde sur une instance de test et vérifier les données, puis comparer les procédures entre participants.

Cas métier : La cellule d'urgence (cas fil rouge) - Le formateur provoque une suppression accidentelle de données. Chaque participant détecte l'incident, restaure les données à partir des sauvegardes, puis présente sa démarche et ses mesures de prévention au groupe.

Compétence acquise : Savoir surveiller une instance et restaurer des données après un incident.

Livrable : La procédure de sauvegarde et de restauration documentée, issue des modèles des journées ESSENTIEL.