PostgreSQL 15 - Administration

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
• La version 14

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

Le contenu de ce programme peut faire l'objet d'adaptation selon les niveaux, prérequis et besoins des apprenants.

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 et/ou une certification (proposée en option)