PostgreSQL - Prise en main

Objectifs

• Présenter une vue d'ensemble du SGDBR PostgreSQL, de ses fonctionnalités et des différentes sources d'informations utiles
  
• Identifier les différentes possibilités d'installation de PostgreSQL
  
• Créer, configurer et contrôler une instance PostgreSQL
  
• Créer une base de données et les objets de celle-ci
  
• Interroger et mettre à jour les objets de cette base de données avec le langage SQL
  
• Gérer la sécurité des accès aux bases de données et aux objets de cette base
  
• Utiliser les outils psql et pgadmin 4
  
• Lister les principales opérations de maintenance à effectuer sur les bases de données d'une instance PostgreSQL
  
• Importer, exporter et échanger des données entre instances PostgreSQL (pg_dump, pg_dumpall, pg_restore)
  
• Présenter une vue d'ensemble des objets PL/pgSQL.
  

Syllabus

Jour 1
Présentation générale

• Historique
  
• Fonctionnalités
  
• Documentation
  
• Versions mineures et majeures
  

Installation

• Présentation simplifiée des différentes méthodes d'installation de PostgreSQL
  
  
  
  • Sources
    
  • Packages
    
  • Installation graphique
    
  
  
• Création d'une instance et configuration basique de celle-ci
  
• Démarrage / arrêt / statut de cette instance
  
• Création d'une base de données et visualisation d'informations sur les bases de données
  

Exemples de travaux pratiques (à titre indicatif)

• Prise en main de l'environnement VirtualBox
  
• Installation de PostgreSQL
  
• Création d'une instance et contrôle de celle-ci avec pg_ctl
  
• Configuration de cette instance avec le fichier postgresql.conf
  
• Création de plusieurs bases de données avec CREATE DATABASE et différentes options
  
• Suppression d'une base de données
  

Jour 2
Gestion de la sécurité

• Concepts des mécanismes de sécurité
  
• Gestion des rôles de types "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
  

Outils de requêtage SQL en mode ligne et graphique

• Outil en mode ligne psql
  
  
  
  • Principales commandes spécifiques psql (d, c, l, h)
    
  • Principales fonctions systèmes d'administration
    
  
  
• Outil graphique pgAdmin 4
  

Le langage SQL avec PostgreSQL

• Les tables relationnelles
  
  
  
  • Types de données supportées
    
  • Création d'une table
    
  • Modification de sa structure
    
  • Contraintes d'intégrité...
    
  
  
• Les index
  
  
  
  • Généralités
    
  • Types d'index
    
  • Création
    
  • Modification
    
  • Réorganisation
    
  • Conseils...
    
  
  
• Requêtes d'interrogation SELECT
  
  
  
  • Extractions simples
    
  • Conditions de recherche WHERE
    
  • Opérateurs booléens (AND, OR, NOT)
    
  • Opérateurs IN, BETWEEN, LIKE, NULL
    
  • Opérations arithmétiques (+, -, *, / )
    
  • Tri des résultats
    
  • Les jointures
    
  • Sous-interrogations
    
  • Traitements de groupe (SUM, AVG, MIN, MAX, COUNT)
    
  • Regroupements... (GROUP BY, HAVING)
    
  
  
• Mises à jour de la base de données
  
  
  
  • Insertion de nouvelles lignes INSERT
    
  • Modification de lignes existantes UPDATE
    
  • Suppression de lignes DELETE
    
  
  
• Gestion des privilèges d'accès aux objets
  
  
  
  • GRANT
    
  • REVOKE
    
  
  
• Commandes DDL CREATE, ALTER, DROP d'une table / index / vue
  

Exemple de travaux pratiques (à titre indicatif)

• Exercices avec psql
  

Jour 3
Exemples de travaux pratiques (à titre indicatif) - Suite

• Installation et utilisation de pgAdmin 4
  
• Exercices de requêtes SQL d'interrogations (simples, jointures, sous-interrogations, traitements de groupe), de mises à jour des données et de créations d'objets
  

Maintenance des données

• Opérations de maintenance d'une instance PostgreSQL
  
• Gestion de l'espace disque
  
• Nettoyage des bases de données avec "autovacuum" et "autoanalyze"
  
• Import et export logiques des données entre bases de données PostgreSQL
  
  
  
  • pg_dump
    
  • pg_restore
    
  • copy
    
  
  

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
  

Introduction au langage PL/pgSQL

• Les fonctions stockées PL/pgSQL
  
• Les triggers
  

Comment ?

Méthodes pédagogiques

1 poste et 1 support par stagiaire
8 à 10 stagiaires par salle
Remise d'une documentation pédagogique papier ou numérique pendant le stage
La formation est constituée d'apports théoriques, d'exercices pratiques, de réflexions et de retours d'expérience

Évaluation

Auto-évaluation des acquis par le stagiaire via un questionnaire en ligne
Attestation de fin de stage remise au stagiaire