Dans la fenêtre d’impression, choisissez « Enregistrer en PDF » comme destination.

Réf. PGS-ADM

PostgreSQL - Administrer une base de données

Bases de données

Durée

21 à 35 heures (3 à 5 jours) selon le format

Modalités

Présentiel, Classe à distance

Reconnaissance

Formation non certifiante (attestation de fin de formation)

Format de vente

Inter-entreprise ou Intra-entreprise

Délai d’accès

Inter : jusqu’à 5 jours ouvrés avant la session ; intra : en moyenne 15 jours ouvrés après l’acceptation du devis

Tarif intra

Sur devis

Pourquoi suivre cette formation

PostgreSQL est le système de gestion de bases de données libre le plus complet. Cette formation vous apprend à l'installer, le sécuriser, le sauvegarder et le surveiller au quotidien.

À l’issue de cette formation, les participants seront capables de

Installer, initialiser et configurer un cluster PostgreSQL dans le respect des règles de l'art
Gérer les droits d'accès, les utilisateurs et sécuriser les connexions au serveur
Monitorer l'activité de l'instance et analyser les fichiers de trace (logs)
Mettre en œuvre des stratégies de sauvegarde et de restauration fiables (logiques et physiques)
Automatiser les tâches de maintenance récurrentes pour garantir l'intégrité physique de la donnée
Diagnostiquer les premiers niveaux de goulots d'étranglement et de verrous techniques

Formats proposés

ESSENTIELFondamentaux21 heures (3 jours)1 860 € HTpar participant (inter-entreprises)

ACTIONImmersion28 heures (4 jours)2 480 € HTpar participant (inter-entreprises)

IMPACTTerrain35 heures (5 jours) · dont une journée en décalé3 225 € HTpar participant (inter-entreprises)

Chaque format reprend le précédent et y ajoute des journées.

Public visé

Administrateurs de bases de données (DBA) juniors, ingénieurs système, architectes d'infrastructure, ingénieurs DevOps, administrateurs système et réseau ou développeurs souhaitant acquérir la maîtrise opérationnelle de PostgreSQL.

Prérequis

Avoir des connaissances de base en administration système (Linux ou Windows, manipulation des fichiers, ligne de commande) et maîtriser les principes fondamentaux du langage SQL.

Prérequis techniques à distance

La classe virtuelle se tient sur Microsoft Teams : AMBIA fournit à chaque participant un compte pour la durée de la formation. En intra-entreprise, elle peut aussi se tenir sur l’outil que l’entreprise utilise déjà, par exemple Zoom ou Google Meet.

Le participant se connecte depuis un ordinateur équipé d’une caméra, d’un micro, d’un accès à internet et d’un navigateur à jour, dans un lieu calme. Si la formation le demande, le formateur donne accès à un poste de travail à distance, depuis le navigateur.

Pendant la formation, l’assistance technique répond à support@ambia-skills.fr.

Objectifs pédagogiques

Décrire l'architecture interne d'un cluster PostgreSQL (processus d'arrière-plan, structures mémoire). Expliquer le fonctionnement du journal de transaction (Write-Ahead Logging - WAL). Réaliser des opérations de sauvegarde et de restauration logiques et physiques robustes. Interpréter les fichiers de traces (logs) pour identifier l'origine d'un dysfonctionnement ou d'une erreur d'accès. Auditer la sécurité des connexions et les rôles affectés aux bases de données. Concevoir des scripts automatisés de maintenance courante (VACUUM, ANALYZE, REINDEX).

Programme détaillé

Jour 1ESSENTIEL

Matin

Architecture de l'instance et Installation

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.

Après-midi

Configuration réseau et Sécurité d'accès

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.

Jour 2ESSENTIEL

Matin

Gestion des privilèges et des Schémas

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.

Après-midi

Gestion du stockage et Maintenance obligatoire

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.

Jour 3ESSENTIEL

Matin

Sauvegardes logiques et physiques

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.

Après-midi

Monitoring et Restauration sur sinistre

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.

Jour 4ACTION

Matin

De la sécurité d'accès à l'action : mettre en service une instance pour une application

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.

Après-midi

Éprouver ses sauvegardes et sa supervision en simulation de sinistre

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.

Jour 5 · en décaléIMPACT

Matin

Audit de l'instance PostgreSQL : accès réseau, rôles et organisation des schémas

Auditer l'exposition réseau, les rôles et l'organisation des schémas de l'instance PostgreSQL réelle de l'entreprise.

Le collaborateur apporte son instance de production ou de pré-production, avec ses utilisateurs et ses applications. Revue de listen_addresses et de pg_hba.conf, contrôle des rôles et des privilèges par schéma, vérification du chiffrement des connexions (SSL/TLS) et suppression des droits excessifs.

Après-midi

Fiabilisation du stockage, des sauvegardes et de la supervision

Mettre en place l'entretien du stockage, des sauvegardes testées et une supervision de base de l'instance réelle.

Travail direct sur l'environnement de test ou de pré-production de l'entreprise. Contrôle de l'espace disque et des tablespaces, planification de VACUUM et ANALYZE, mise en place de pg_dump et pg_basebackup, test de restauration et supervision avec pg_stat_activity.

Les ateliers et cas métier de ce programme pourront être adaptés par le consultant-formateur selon les besoins, le niveau et les prérequis des participants, pour favoriser l’atteinte des objectifs pédagogiques.

Méthodes et moyens pédagogiques

Méthodes mobilisées

Pédagogie active s'appuyant sur des ateliers de manipulation, des simulations d'incidents (pannes d'écriture, corruptions simulées), des démonstrations d'administration guidées en console Linux et des débriefings d'architecture système.

Moyens techniques

Un poste informatique individuel par participant, accès à un serveur virtuel Linux dédié exécutant PostgreSQL, suite d'outils clients d'administration (pgAdmin, psql), accès à l'espace numérique AMBIA et aux scripts de référence.

Moyens humains

Formation animée par un consultant-formateur expert senior, spécialiste reconnu de l'administration PostgreSQL et des architectures de bases de données d'entreprise.

Modalités d’évaluation

Évaluation diagnostique

Questionnaire de positionnement technique en amont (15 jours avant la session) pour évaluer la maîtrise de Linux et du SQL initial.

Évaluation formative

Bilan de montée en compétences tout au long de la formation à travers des exercices pratiques progressifs, des audits de configuration et des simulations de pannes d'exploitation.

Évaluation sommative

Cas d'usage d'évaluation complet en fin de parcours (restauration globale d'une instance corrompue et audit de sécurité), complété par un Questionnaire de validation des acquis sous forme de QCM en fin de formation.

Suivi

Feuilles de présence signées par demi-journée, en présentiel comme à distance.
Attestation de fin de formation remise à chaque stagiaire, mentionnant les objectifs, la nature, la durée et les résultats de l’évaluation.
Certificat de réalisation adressé au client ou au financeur.
Questionnaire de satisfaction remis à l’issue de la formation.

Délai d’accès

Inter-entreprises : L’inscription est possible jusqu’à 5 jours ouvrés avant le début de la session, sous réserve des places disponibles.

Intra-entreprise : La formation débute en moyenne 15 jours ouvrés après l’acceptation du devis.

Ce délai peut être adapté selon la disponibilité des sessions, le positionnement du stagiaire et, le cas échéant, l’instruction du dossier de prise en charge par le financeur.

Accessibilité handicap

Nos parcours sont adaptables aux personnes en situation de handicap. Contactez notre référent pour l'étude des aménagements.

Référent handicap : accessibilite@ambia-skills.fr. Aucune reconnaissance de la qualité de travailleur handicapé ni aucun justificatif médical n’est exigé.

Indicateurs de résultats

Indicateurs publiés à partir de 3 avis.

Mode de calcul : www.ambia-skills.fr/notre-approche#notes