PostgreSQL, administration

Ce cours permet d’acquérir l’information nécessaire afin de commencer, sereinement et solidement, l’administration d’un serveur PostgreSQL.

Résumé

Ressources, historique, versions.
Outils psql, pgAdminIII, pgAdmin4.
Installation du serveur PostgreSQL : installation binaire, compilation à partir des sources.
Architecture et gestion d’une instance PostgreSQL : démarrage/arrêt, paramétrage, Processus.
Architecture et fichiers d’un cluster PostgreSQL : initialisation, tablespaces, bases de données, stockage des tables, journaux de transactions (WAL).
Sécurité et utilisateurs : contrôle des connexions, privilèges et rôles.
Maintenance et objets dans PostgreSQL, intérêt de VACUUM.
Sauvegarde/restauration : sauvegarde logique via pg_dump, sauvegarde physique des fichiers et archivage continu.

À qui s’adresse ce cours

Ce cours s’adresse à toute personne voulant acquérir la compétence nécessaire à une administration efficace d’un serveur PostgreSQL. Il requiert des connaissances SQL et des connaissance de Unix/Linux.

En savoir plus ∙ plan détaillé

Plan détaillé

Introduction et rappels

Historique et versions.
Introduction à SQL.
Serveur PostgreSQL.
Utilisateurs et schémas.

Architecture et gestion d’une instance PostgreSQL

Initialisation du cluster.
Démarrage/arrêt de PostgreSQL.
Paramétrage de PostgreSQL.
Catalogues système, vues et tables « pg_* », information_schema.
Processus démons de PostgreSQL et leurs rôles.
Extensions et leur paramétrage.
Journal applicatif : configuration, gestion des fichiers du journal.
Journal de transactions.

Travaux pratiques : Configuration et consultation du journal applicatif.

Gestion de l’espace et des objets

Initialisation du cluster et commande initdb.
Gestion des tablepsaces.
Gestion des bases de données.
Espace libre dans les tables, utilisation de l’extension pg_freespacemap.
Index et maintenance, reconstruction d’index, index manquants, index redondnats.
Partitionnement des tables.
Utilisation des vues matérialisées.

Travaux pratiques : Initialisation d’un deuxième cluster, tables partitionnées.

Sécurité et utilisateurs

Introduction à la gestion des utilisateurs.
Contrôle des connexions des clients et fichier pg_hba.conf.
Utilisateurs/rôles dans PostgreSQL.
Utilisation des schémas.
Privilèges et droits d’accès.
Configuration/utilisation des droits par défaut.

Travaux pratiques : contrôle des connexions via pg_hba.conf, création/configuration d’utilisateurs, utilisation des schémas, gestions des privilèges, gestion des privilèges par défaut.

Maintenance des objets d’un cluster PostgreSQL

Création/modification des tables, paramètres de stockage des tables, stockage des colonnes, taille stockage d’une table.
Types d’index dans PostgreSQL.
Organisation d’une table en fonction d’un index (opération CLUSTER).
VACUUM et VACUUM FULL.
Partitionnement de tables.
Foreign Data Wrapper, utilisation des tables FDW.

Travaux pratiques : Organisation d’une table selon un index, partitionnement de tables, utilisation des tables FDW.

Gestion des connexions

Paramétrage des connexions, connexions multi cibles.
Architecture multi tiers et gestion des sessions.
Pools de sessions, utilisation de pgBouncer.
Différents modes d’utilisation de pgBouncer.

Travaux pratiques : Installation, configuration, et utilisation de pg_bouncer.

Exploitation

Extensions : ajout, suppression, interrogation, paramétrage.
Sessions connectées et activité, verrouillage et blocage.
Sauvegarde/restauration logique, pg_dump, pg_restore.
Sauvegarde des fichiers à froid.
Configuration de l’archivage continu.
Sauvegarde à chaud, fonctions pg_start_backup() et pg_stop_backup() .
Restauration/récupération après une panne.

Travaux pratiques : sauvegarde/restauration logique, sauvegarde physique à froid, configuration de l’archivage continu, sauvegarde à chaud, restauration/récupération en archivage continu, verrouillage et blocage.