Ce cours s’adresse aux personnes souhaitant devenir développeurs ou administrateurs PostgreSQL, et qui veulent avoir une base solide sur PostgreSQL pour la suite de leur carrière. Il nécessite de connaître la programmation (pour la partie PL/pgSQL), et de savoir utiliser un système d’exploitation (Windows ou Unix).
Plan détaillé
Introduction
- Serveur PostgreSQL.
- Modèle relationnel.
- Introduction au langage SQL.
- Outils : psql, pgAdminIII, pgAdmin4.
- Création de tables.
- Introduction aux instructions de mise à jour.
- Utilisation des séquences, types SERIAL et BIGSERIAL.
- Introduction à l’interrogation(SELECT).
- Introduction aux transactions.
- Types basiques et constantes dans PostgreSQL.
Travaux pratiques: utilisation des outils, création de tables, instructions de mise à jour, séquences, instructions SELECT simples.
Interrogation des données
- Tri et suppression de doublons. OFFSET et LIMIT.
- Alias de colonnes, alias de tables, préfixes, opérateurs.
- Utilisation des fonctions.
- Conversion des types.
- NULL dans PostgreSQL.
- Agrégats.
- Groupement.
- Jointure et produit cartésien.
- Requêtes imbriquées : opérateurs IN et EXISTS.
- Opérateurs ensemblistes.
Travaux pratiques: écriture d’instructions SQL concernant tous les sujets traités.
Introduction à PL/PGSQL
- Survol du langage.
- Premier contact avec le langage.
- Introduction syntaxique à PL/pgSQL.
- Conversion des types.
- Interaction avec la base de données, curseurs.
- Définition des sous-programmes.
- Paramètres de sous-programmes.
- Sous-programmes stockés.
- Introduction au traitement des erreurs.
- Déclenchement d’erreurs.
Travaux pratiques : déclaration des variables, curseurs, sous-programmes stockés, gestion des erreurs.
Triggers LMD dans PostgreSQL
- Introduction aux triggers dans PostgreSQL.
- Triggers LMD.
- Triggers de niveau instruction.
- Triggers de niveau ligne.
Travaux pratiques : trigger au niveau ligne, trigger au niveau instruction.
Architecture et objets dans les bases de données
- Utilisation des schémas dans PostgreSQL.
- Création et suppression de tables.
- Modification de la définition d’une table.
- Utilisation des vues.
- Dictionnaire de données.
- Utilisation de tableaux.
- Utilisation du type ENUM.
- Modèle de stockage : tablespaces, segments, fichiers de données.
- Index.
Travaux pratiques : utilisation des schémas, valeurs par défaut des colonnes, ajout de colonnes et de contraintes à une table, utilisation des vues en mise à jour, utilisation des tableaux, utilisation du type ENUM.
Gestion des transactions
- Effet de l’annulation d’instructions, annulation partielle d’une transaction.
- PL/pgSQL et les transactions.
- Gestion de la concurrence.
- Niveaux d’isolations des transactions concurrentes.
- Verrouillage explicite : select … for update, select … for share, lock table.
Travaux pratiques : tester le comportement des transactions concurrentes, utilisation des niveaux d’isolation, utilisation du verrouillage explicite.
Sécurité et utilisateurs
- Rôles et privilèges, schéma associé à un utilisateur.
- Privilèges et types d’objets.
- Gestion des rôles.
- Cas des vues et des traitements stockés.
Travaux pratiques : schémas et utilisateurs, privilèges d’objet, rôles et utilisateurs.
Compléments
- Initialisation du cluster PostgreSQL, répertoire de données.
- Démarrage de PostgreSQL, fichier de paramétrage postgresql.conf.
- Création d’une base de données.
- Journal transactionnel XLOG.
- Processus vacuum et son intérêt.
- Authentification des clients, fichier pg_hba.conf.
