A l’issue de cette formation, vous serez capable de :

• 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.

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
• Les nouvelles fonctionnalités de la version 16

Installation

• Pré-installation avec les sources sous Linux
• Installation avec les sources sous Linux
• Post-installation
• Autres méthodes d'installation
• Via des paquetages (rpm, apt-get, yum, DNF)
• 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)
• Nouveaux paramètres liés à la version 16
• 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
• Intégration avec SYSTEM-D

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"
• Chiffrement des mots de passe SCRAM-SHA-256
• 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)

• Multiples 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 sur un serveur fermé
• Le mode Archive (archivage des WAL)
• Sauvegardes physiques sur un 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)
• Nouvelle présentation de la 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