Accueil    Formations    Informatique    Bases de données    MySQL et PostgreSQL    PostgreSQL - Administration

Partager cette formation Télécharger au format pdf Ajouter à mes favoris

Vidéo de présentation

Cette formation peut être financée grâce au CPF (Compte Personnel de Formation). Le Compte Personnel de Formation permet d'acquérir des droits à la formation professionnelle, comptabilisés en euros, que chacun peut utiliser avec ou sans l'accord de son employeur tout au long de sa vie active (y compris en période de chômage) pour suivre une formation qualifiante ou certifiante. 

Consultez le montant de vos droits CPF

Objectifs pédagogiques / Compétences visées

  • Présenter une vue d'ensemble du SGDBR PostgreSQL, de ses fonctionnalités et des différentes sources d'information utiles
  • Comparer les différentes possibilités d'installation de PostgreSQL
  • Créer, configurer, contrôler une instance PostgreSQL (database cluster)
  • Mettre en oeuvre les différentes bases de données d'une instance PostgreSQL et utiliser les "tablespaces"
  • Gérer les connexions aux différentes bases de données d'une instance PostgreSQL
  • Développer la sécurité des accès au serveur et aux objets de ce serveur PostgreSQL
  • Reconnaître les mécanismes de journalisation mis en oeuvre sous PostgreSQL
  • Citer les principales opérations de maintenance à effectuer sur les bases de données d'un serveur PostgreSQL
  • Utiliser les outils psql et pgAdmin4
  • Présenter une vue d'ensemble des tables et des index d'un serveur PostgreSQL
  • Mettre en oeuvre les éléments nécessaires pour sauvegarder et restaurer de manière cohérente les données des bases d'un serveur PostgreSQL
  • Décrire les premiers éléments d'optimisation d'un serveur PostgreSQL.

Niveau requis

Avoir connaissance d'un système d'exploitation de Linux et/ou Windows, des concepts SGBDR et maîtriser le langage SQL.

Public concerné

Gestionnaires de données, administrateurs et analystes-exploitants.

Programme

Jour 1

Présentation générale

  • Préface
    • Communauté PostgreSQL
    • Site Internet...
  • Fonctionnalités
  • Documentation
  • Les projets annexes
  • Déclaration d'un bug
  • Les versions mineures et majeures

Installation

  • Pré-installation avec les sources sous Linux
  • Installation avec les sources sous Linux
  • Post-installation
  • Autres méthodes d'installation
    • Via des rpm
    • Via des "Graphical Installer" (exemple sous Linux et Windows)
  • Upgrading des bases de données existantes
Exemples de travaux pratiques (à titre indicatif)
  • Prise en main de l'environnement VirtualBox
  • Installation de PostgreSQL sous Linux via les sources

Mise en oeuvre d'une instance PostgreSQL

  • Création d'une instance PostgreSQL
  • Démarrage et arrêt d'une instance PostgreSQL avec postgres et pg_ctl
  • Configuration de l'instance (fichier postgresql.conf)
  • Démarrage et arrêt automatique de l'instance PostgreSQL
Exemples de travaux pratiques (à titre indicatif)
  • Création d'une instance et contrôle de celle-ci avec pg_ctl
  • Configuration de cette instance avec le fichier postgresql.conf
  • Configuration avec la commande ALTER SYSTEM
  • Mise en démarrage / arrêt automatique de cette instance

Création d'une base

  • Introduction
  • Création d'une base de données avec CREATE DATABASE et createdb
  • Informations sur les bases de données
  • Modification et suppression d'une base
  • Création, modification et suppression de tablespaces
Exemples de travaux pratiques (à titre indicatif)
  • Création de plusieurs bases de données avec CREATE DATABASE et différentes options
  • Création d'un tablespace et d'une base de données dans ce tablespace
  • Suppression d'une base de données
  • Configuration d'une base de données avec ALTER DATABASE

Jour 2

Authentification des clients

  • Configuration du fichier pg_hba.conf
  • Les méthodes d'authentification
  • Mise en place d'un fichier pg_hba.conf personnalisé avec connexions distantes
Exemples de travaux pratiques (à titre indicatif)
  • Personnalisation du fichier pg_hba.conf de votre instance
  • Test des connexions locales et distantes avec ce fichier pg_hba.conf personnalisé

Gestion de la sécurité

  • Concepts des mécanismes de sécurité
  • Gestion des rôles de type "utilisateur" et "groupe"
  • Gestion des privilèges d'accès aux objets
Exemples de travaux pratiques (à titre indicatif)
  • Création d'utilisateurs et de groupes d'utilisateurs
  • Création de tables de démonstration pour la mise en oeuvre des droits d'accès aux objets pour les utilisateurs / groupes créés précédemment
  • Vérification et test des droits d'accès mis en place

Journalisation

  • Journalisation "avant" et environnement transactionnel
    • Transaction
    • Journalisation "avant"
    • Commit / rollback
    • Verrous et concurrence d'accès
  • Journalisation WAL
  • Checkpoint
  • Archivage des fichiers WAL et PITR

Maintenance d'une instance PostgreSQL

  • Opérations de maintenance sur un serveur PostgreSQL
  • Gestion de l'espace disque et l'espace disque utilisé
    • Vues système
    • Fonction SQL d'administration
    • Contrib oid2name
  • Nettoyage d'une base de données
  • Autovacuum
  • Utilitaire VACUUM
  • Le fichier de log
  • pgBadger
Exemples de travaux pratiques (à titre indicatif)
  • Utilisation de quelques fonctions systèmes d'administration livrées par PostgreSQL
  • Mises à jour volumineuses sur une table et observation du déclenchement automatique des vacuum / analyze dans le fichier de log du serveur
  • Installation et utilisation de la contrib oid2name
  • Evolution de la taille des objets après mises à jour volumineuses et déclenchements de vacuum simple et full
  • Personnalisation du contenu du fichier de log du serveur
  • Installation et utilisation de pgBadger

psql et pgAdmin 4

  • psql
    • Utilisation en interactif avec ses commandes spécifiques , scripts et batch
  • pgAmin 4
    • Installation
    • Prise en compte d'une nouvelle instance
    • Utilisation
    • Gestion des objets
    • Requêteur
    • Plan d'exécution
Exemples de travaux pratiques (à titre indicatif)
  • Multiple exercices avec psql
  • Installation et utilisation de pgAdmin 4 pour le reste des travaux pratiques

Jour 3

Sauvegardes et restaurations

  • Généralités
  • Sauvegardes logiques
  • Utilisation de pg_dump et pg_dumpall
  • Restaurations logiques
    • A partir d'un fichier texte et psql
    • A partir d'un fichier binaire et pg_restore
  • Sauvegardes physiques serveur fermé
  • Le mode Archive (archivage des WAL)
  • Sauvegardes physiques serveur ouvert
  • Restauration complète ou partielle (PITR) du serveur
Exemples de travaux pratiques (à titre indicatif)
  • Effectuer un pg_dump d'une base de données dans un fichier texte incluant le CREATE DATABASE au début de la sauvegarde
  • Visualiser le contenu du fichier texte produit par pg_dump
  • Supprimer la base de données (drop database) précédemment sauvegardée et la recréer en utilisant psql et le fichier texte produit par pg_dump
  • Effectuer une sauvegarde avec pg_dump d'une autre base de données dans un fichier en format TAR
  • Supprimer la base de données (drop database) précédemment sauvegardée et la recréer en utilisant pg_restore
  • Configurer votre serveur PostgreSQL en mode Archive
  • Générer de l'activité sur votre serveur et vérifier la production des archives
  • Arrêter votre serveur PostgreSQL
  • Sauvegarder (TAR) le répertoire PGDATA et le répertoire du tablespace de votre serveur PostgreSQL

Tables et index

  • Tables relationnelles
  • Types de données
  • Création, modification de la structure et suppression d'une table
  • Index
    • Types d'index
    • Conseils...
  • Création et suppression, réorganisation d'un index
  • Contraintes d'intégrité
  • Types de contraintes
  • Création et suppression d'une contrainte
Exemples de travaux pratiques (à titre indicatif)
  • Création de divers types de tables avec contraintes d'intégrité
  • Création d'index Btree et visualisation de son utilisation via le plan d'exécution

Optimisation

  • Introduction
  • Exécution d'une requête
  • Lecture d'un plan d'exécution
    • Commande EXPLAIN
    • Opérations élémentaires
  • Principaux paramètres d'optimisation liés à la mémoire, espace mémoire consommé et conseils
  • Contrôle de l'activité et statistiques du serveur
  • Contrib pg_stat_statements
  • pgbench
Exemples de travaux pratiques (à titre indicatif)
  • Exemple simple de lecture d'un plan d'exécution
  • Utilisation des tables de statistiques
  • Installation et utilisation de pg_stat_statements et pgbench

Certification (en option)

  • Prévoir l'achat de la certification en supplément
  • L'examen (en français) sera passé le dernier jour, à l'issue de la formation et s'effectuera en ligne
  • La durée moyenne est de 1h00 et le score obtenu attestera d'un niveau de compétence

Modalités d’évaluation des acquis

  • En cours de formation, par des études de cas ou des travaux pratiques
  • Et, en fin de formation, par un questionnaire d'auto-évaluation ou une certification (M2i ou éditeur)

Compétences attestées par la certification

  • Définir l'architecture de la base de données, connaître ses limitations
  • Installer l'instance, la configurer
  • Gérer la ou les bases de données : schémas, tables et contraintes, index
  • Importer, exporter des données
  • Assurer la sécurité des bases de données : accès à l'instance, rôles, droits d'accès
  • Gérer les transactions
  • Prévoir les procédures de sauvegarde et de restauration des bases de données
  • Assurer la maintenance des bases de données.

Partager cette formation Télécharger au format pdf Ajouter à mes favoris

Vous souhaitez suivre cette formation ?

Cette formation est disponible en présentiel ou en classe à distance, avec un programme et une qualité pédagogique identiques.

Choisissez la modalité souhaitée pour vous inscrire :

Votre société a besoin d'une offre personnalisée ? Contactez-nous

Testez vos connaissances

Nos quizz vous guident dans le choix de formation.
Évaluez votre niveau de pré-requis pour découvrir la formation faite pour vous.

Accédez au test de connaissance

Faites évoluer vos compétences

Complétez votre formation

Faites-nous part de votre projet de formation, nous sommes là pour vous guider.

Contactez-nous