PostgreSQL est le SGBD open source le plus utilisé en entreprise, apprécié pour sa robustesse et sa conformité SQL. Cette formation d’initiation en 3 jours couvre l’essentiel de l’administration : installation, gestion des instances, sécurité, journalisation WAL et sauvegardes. Elle constitue le socle indispensable avant d’aborder les formations avancées sur la réplication et la haute disponibilité.

Public visé

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

Objectifs pédagogiques

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

Présenter une vue d’ensemble de PostgreSQL, ses fonctionnalités et ressources
Comparer les différentes méthodes d’installation de PostgreSQL
Créer, configurer et contrôler les instances PostgreSQL
Implémenter les bases de données et tablespaces
Gérer les connexions et la sécurité des bases de données
Comprendre les mécanismes de journalisation WAL
Effectuer les opérations de maintenance
Utiliser les outils psql et pgAdmin4
Gérer les tables et les index
Exécuter des sauvegardes et restaurations cohérentes
Appliquer les fondamentaux de l’optimisation

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

Travaux pratiques :

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
Sous Linux avec System-D
Sous Windows Gestionnaire des services

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

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

Travaux pratiques :

Exemple simple de lecture d’un plan d’exécution
Utilisation des tables de statistiques
Installation et utilisation de pg_stat_statements et pgbench

Modalités d’évaluation des acquis

En cours de formation, par des travaux pratiques. En fin de formation, par un questionnaire d’auto-évaluation.

Pour aller plus loin

Cette formation donne accès à cette formation :

PostGIS - Prise en main

Voir aussi

Vous serez peut-être intéressés par les formations suivantes :

Formation DBS-POSTGRES-100 — PostgreSQL - Initiation. Domaine : Base de données. Niveau : Initiation. Durée : 3 jours (21 heures). Mots-clés : postgresql, base-de-données, sql, initiation, administration. Cylian Formation, Dijon.