Formation DBA PostgreSQL

Leçon 1 • Démarrage, arrêt et rechargement du serveur

Couvre pg_ctl, la gestion des services systemd, et le contrôle par signaux. Les étudiants mettent en pratique les opérations sûres du cycle de vie du serveur utilisées dans chaque flux de travail d'administrateur de base de données.

Leçon 2 • Concepts fondamentaux des bases de données relationnelles

Couvre les principes SGBDR, les propriétés ACID, et comment PostgreSQL s'intègre dans le modèle relationnel. Fournit la base conceptuelle pour tous les sujets d'administration ultérieurs.

Leçon 3 • Structure des répertoires et fichiers clés

Décrit la structure du répertoire de données, les fichiers de configuration et les emplacements des journaux. La connaissance de la disposition des fichiers est essentielle pour les tâches de configuration et de restauration.

Leçon 4 • Architecture des processus et de la mémoire de PostgreSQL

Examine le postmaster, les processus backend, les tampons partagés et le WAL. La compréhension de ces éléments internes guide les décisions d'optimisation et de dépannage tout au long du cours.

Leçon 1 • Bases de données, schémas et espaces de stockage

Explique la hiérarchie de nommage à trois niveaux et le mappage de stockage des espaces de stockage. Une conception correcte de l'espace de nommage évite les conflits d'objets et facilite la gestion du stockage.

Leçon 2 • Séquences et colonnes d'identité

Explique les objets séquence et la syntaxe GENERATED AS IDENTITY pour les clés de substitution. Les étudiants configurent correctement les colonnes à incrémentation automatique pour les schémas de production.

Leçon 3 • Vues, vues matérialisées et fonctions

Présente les vues en lecture seule et modifiables, l'actualisation des vues matérialisées et les fonctions PL/pgSQL de base. Ces objets encapsulent la logique et simplifient les interfaces applicatives.

Leçon 4 • Types d'index et leur utilisation

Passe en revue les index B-tree, Hash, GIN, GiST et BRIN ainsi que leurs cas d'usage appropriés. La sélection des index a un impact direct sur les performances des requêtes, abordées dans les chapitres suivants.

Leçon 1 • Gestion des privilèges au niveau des objets

Explique les commandes GRANT et REVOKE sur les tables, les schémas, les séquences et les fonctions. Les étudiants appliquent le principe du moindre privilège sur une base de données multi-schémas réaliste.

Leçon 2 • Rôles et attributs de rôle

Couvre CREATE ROLE, LOGIN, SUPERUSER, et les attributs CREATEDB. La conception des rôles est le fondement de toute décision de privilège et d'authentification dans PostgreSQL.

Leçon 3 • Politiques de sécurité au niveau des lignes

Présente la création de politiques RLS, les clauses USING et WITH CHECK, et les tests de politiques. Le RLS assure l'isolation des données sans nécessiter de logique de filtrage au niveau applicatif.

Leçon 4 • Audit et durcissement de la sécurité

Couvre l'extension pgaudit, la journalisation des connexions et les paramètres de durcissement. Les étudiants produisent une configuration prête pour l'audit, alignée sur les normes de sécurité courantes.

Leçon 1 • Paramètres de coût du planificateur de requêtes

Explique seq_page_cost, random_page_cost, cpu_tuple_cost et les cibles de statistiques. Des paramètres de coût précis permettent au planificateur de choisir des plans d'exécution optimaux.

Leçon 2 • Réglage du WAL et des points de contrôle

Couvre les paramètres wal_buffers, checkpoint_completion_target, max_wal_size et fsync. Le réglage du WAL équilibre le débit d'écriture et les garanties de durabilité lors de la récupération après incident.

Leçon 3 • Paramètres de configuration mémoire

Explique shared_buffers, work_mem, maintenance_work_mem et effective_cache_size. Une allocation mémoire correcte est le levier de réglage ayant le plus d'impact pour la plupart des charges de travail.

Leçon 4 • Réglage au niveau du système d'exploitation pour PostgreSQL

Aborde les paramètres du noyau, la sélection du planificateur d'E/S, NUMA et les énormes pages transparentes. La configuration du système d'exploitation limite souvent les gains réalisables par les seuls paramètres PostgreSQL.

Leçon 1 • VACUUM et VACUUM FULL

Couvre la syntaxe et les effets de VACUUM manuel, VACUUM FULL et VACUUM ANALYZE. Les étudiants distinguent quand chaque forme est appropriée sans provoquer de verrouillage inutile.

Leçon 2 • MVCC et mécanique du gonflement des tables

Explique comment MVCC crée des tuples morts et un gonflement des pages au fil du temps. Comprendre MVCC est un prérequis pour concevoir des stratégies efficaces de VACUUM et d'autovacuum.

Leçon 3 • Prévention du bouclage des identifiants de transaction

Couvre la surveillance de l'âge XID, autovacuum_freeze_max_age et le VACUUM d'urgence. Une défaillance par bouclage rend la base de données totalement indisponible et doit être activement évitée.

Leçon 4 • Maintenance des index et REINDEX

Couvre la détection du gonflement des index, REINDEX CONCURRENTLY et pg_stat_user_indexes. Une maintenance régulière des index préserve les performances des requêtes sans nécessiter de réécriture des tables.

Leçon 1 • Sauvegarde logique avec pg_dump

Couvre les formats pg_dump, pg_dumpall, les vidages parallèles et la restauration sélective avec pg_restore. Les sauvegardes logiques permettent la restauration au niveau des objets et la migration entre versions.

Leçon 2 • Sauvegarde physique avec pg_basebackup

Explique les sauvegardes de base en streaming, les modes de point de contrôle et les options d'inclusion du WAL. Les sauvegardes physiques sont le fondement de la PITR et du provisionnement des serveurs de veille.

Leçon 3 • Procédure de restauration à un instant précis

Guide la restauration à partir de la sauvegarde de base, des paramètres recovery.conf ou postgresql.conf, et de la spécification de la cible. Les étudiants exécutent et vérifient un scénario PITR complet de bout en bout.

Leçon 4 • Configuration de l'archivage WAL

Couvre la configuration de archive_mode, archive_command et archive_cleanup_command. L'archivage continu du WAL permet la restauration à n'importe quel point après la sauvegarde de base.

Leçon 1 • Basculement et HA basée sur Patroni

Couvre la promotion manuelle, pg_ctl promote et la HA automatisée avec Patroni et etcd. Les étudiants simulent une défaillance primaire et vérifient que le basculement automatique s'effectue correctement.

Leçon 2 • Fondamentaux de la réplication logique

Explique les publications, les souscriptions et le filtrage de lignes pour la réplication sélective. La réplication logique prend en charge les mises à niveau inter-versions et la distribution partielle des données.

Leçon 3 • Configuration de la réplication en streaming

Couvre les créneaux de réplication, les entrées de réplication dans pg_hba.conf et primary_conninfo. Les étudiants construisent un serveur de veille en streaming fonctionnel à partir d'un serveur primaire en production.

Leçon 4 • Surveillance du retard de réplication

Explique pg_stat_replication, pg_stat_wal_receiver et le calcul du retard basé sur LSN. La surveillance du retard est essentielle pour la conformité aux SLA et la prise de décision en matière de basculement.

Leçon 1 • Statistiques et précision du planificateur

Couvre pg_statistic, ANALYZE, les statistiques étendues et l'interprétation des histogrammes. Des statistiques obsolètes ou insuffisantes amènent le planificateur à choisir des plans d'exécution sous-optimaux.

Leçon 2 • Stratégies de jointure et indices de plan

Explique les critères de sélection et les techniques de forçage pour le nested loop, le hash join et le merge join. Les inadéquations de stratégie de jointure sont une cause majeure de régressions sévères des performances des requêtes.

Leçon 3 • Identification des requêtes lentes avec pg_stat_statements

Couvre l'installation de pg_stat_statements, les colonnes clés et la normalisation des requêtes. Les données de charge de travail agrégées révèlent les requêtes à plus fort impact à prioriser pour l'optimisation.

Leçon 4 • Lecture de EXPLAIN et EXPLAIN ANALYZE

Décode les nœuds de plan, les estimations de coût, les lignes réelles et la sortie temporelle d'EXPLAIN. Une lecture précise des plans est la compétence préalable à toute technique d'optimisation de requête.