Formation PostgreSQL : Administration de la base de données

Objectifs pédagogiques :

• Découvrir PostgreSQL et ses outils
• Installer et administrer un serveur de base de données PostgreSQL
• Comprendre le cycle de vie d’une instance tout en s’opposant aux dysfonctionnements
• Sécuriser un serveur PostgreSQL
• Compléter les compétences fondamentales d’un administrateur PostgreSQL avec le chapitre “Sauvegardes et restauration PostgreSQL”
• Associer Devops et PostgreSQL à travers des applications WEB

Chefs de projet, développeurs, administrateurs de bases de données et administrateurs des systèmes.

Connaissance d’autres bases de données, du langage SQL, et de l’administration de base de Linux (la formation peut cependant se dérouler sous Windows si vous le demandez à l’avance).

Présentation de PostgreSQL et des outils

• Tour d’horizon du vocabulaire PostgreSQL (WAL, réplication, Hot Standby, etc.) Les contributions PostgreSQL (pgbench, oid2name, pg_buffercache, pgcrypto) L’outil en ligne de commande : psql (shell SQL) Le client lourd PgAdmin 4 (administration, développement) L’outil en ligne phpPgAdmin. Le gestionnaire de connexions Pgpool Le SIG PostGIs Les sites et forums pour trouver de l’aide Comparatif avec les autres moteurs SGBDR (avantages/inconvénients) Les grandes différences avec Oracle (11 Grid, 12 Cloud) L’offre de Microsoft SQL Server La version Cloud (Azure)

Travaux pratiques (durée 1H00) :

Prise en main des outils postgreSQL (les participants se connectent sur un environnement existant et exécutent des requêtes prédéfinies afin de bien comprendre le champ fonctionnel de chaque outil tout en commençant à les prendre en main)

Installation de PostgreSQL

• Pré-requis selon les besoins applicatifs Installation à partir des sources Installation à partir d’un package L’assistant d’installation graphique

Travaux pratiques (durée 1H00) :

Installation de PostgreSQL à partir des sources.
Premiers réglages post-installation.

Base et instance PostgreSQL

• Création d’une base et de ses objets (schémas, tables, index, domaines, séquences, ..) Les différents types de données, opérateurs et fonctions Retrouver les objets dans le dictionnaire de données Différences entre instance et base de données Arrêt et démarrage d’une instance Architecture des processus du serveur Création d’instances supplémentaires Créer une nouvelle instance ou une nouvelle base ?

Travaux pratiques (durée 1H30) :

Création d’une base de données
Etude des paramètres de base du fichier de configuration d’une instance
Démarrage – Observations des logs
Sécurisation de l’instance

Sécurité et transactions

• Fonctionnement des transactions Les niveaux d’isolations et les verrous sous postgreSQL Vue générale des droits d’accès sur les ressources Les rôles (utilisateurs et groupes) Gratification et révocation des privilèges La sécurité des sessions coté serveur (pg_hba.conf) Quelles applications peuvent utiliser SSL ? Apport des vues pour la sécurité

Travaux pratiques (durée 1H30) :

Observations de l’intégrité et de l’isolation des données durant les transactions
Mise en place d’un environnement utilisateur sécurisé (création de compte, droits sur les objets et les sessions)

Fichiers, stockage, logs et surveillance

• Arborescence standard suite à l’installation de PostgreSQL Emplacement des éléments fondamentaux Répertoires et fichiers créés à la suite d’une création de base Les fichiers à sauvegarder à froid pour une base précise Le fichier fondamental postgreSQL.conf Les logs binaires : Write Ahead Logging (WAL) Surveillance et valeurs recommandées pour la mémoire Le collecteur de statistiques Les logs d’activités Les vues et tables des informations du serveur Outils pour obtenir des informations sur l’état des bases Outils pour vérifier la cohérence des données

Travaux pratiques (durée 2H00) :

Variantes sur le paramétrage de postgresSQL.conf
Analyse des logs selon différents scénarios standards du cycle de vie des instances postgreSQL
Récolte des statistiques correspondantes

Sauvegardes et restauration PostgreSQL

• Stratégies de sauvegardes et commandes associées Différences entre pg_dump et pg_dumpall Les restaurations avec pg_restore et psql Spécificités des grosses bases (gzip, split) Limitations Sauvegarde au niveau OS Migrations entre différentes versions de PostgreSQL

Travaux pratiques (durée 1H30) :

Utilisations typiques de pg_dump en ligne de commande pour sauvegarde totale ou partielle d’une base
Automatisation de la sauvegarde, du transfert, puis de la restauration de la base sur un autre serveur tous les soirs afin de disposer d’un miroir asynchrone.

PostgreSQL dans le contexte applicatif

• Les procédures stockées Les langages « invités » (PL/pgSQL, PL/Perl, etc.) Les triggers (déclencheurs) Les applications web en PHP Les applications web en Java Les pilotes PostgreSQL et PDO Rappels sur le rôle d’un serveur d’application Liens entre PostgresQL et un serveur d’applications

Travaux pratiques (durée 1H00) :

Exemples commentés de procédures stockées et de triggers.
Affichage des données de la base créé précédemment à travers un naviGustur et une application web simple résumant une architecture WEB standard et la place de postgreSQL (PHP, PostgreSQL, serveur d’application, html, css).

Optimisation PostgreSQL

• Savoir lire l’activité et la surcharge de PostgreSQL Sur quels paramètres intervenir ? L’analyse des requêtes avec Explain L’analyse et le nettoyage des espaces de tables et d’index avec Vacuum La reconstruction d’index avec Reindex

Travaux pratiques (durée 1H00) :

Analyse de requêtes et choix d’indexation en conséquence
Impact du cache sur les performances

Troubleshooting

• Présentation des jeux de caractères (UTF, ISO, etc.) Comprendre l’encodage d’une base et son impact sur les applications Les paramètres pouvant empêcher le démarrage d’une instance Problème d’espace disque (psql, vacuum, dbsize, oid2name, analyze, etc.)

Travaux pratiques (durée 1H00) :

Réencodage d’une base PostgreSQL
Diagnostic d’utilisation de l’espace disque et augmentation de l’espace de stockage
Correction de paramètres empêchant le démarrage d’une instance suite à l’analyse des logs

Cette formation PostgreSql vous apprendra à installer et à administrer au quotidien votre serveur de base de données PostgreSQL. Après une présentation des outils généralement utilisés vous installez PostgreSQL et procédez aux premiers réglages post-installation.
Puis vous comprenez comment fonctionne le cycle de vie d’une instance tout en sachant faire face aux dysfonctionnements. Vous apprenez également à sécuriser votre serveur PostgreSQL tout en sachant lire son activité et optimiser ses performances.
Le chapitre important sur les sauvegardes/restaurations vient compléter les compétences fondamentales attendues d’un administrateur PostgreSQL dans un contexte professionnel.
Enfin, dans une approche DevOps, il est important de comprendre comment un serveur PostgreSQL est sollicité en production à travers des applications WEB pour une bonne compréhension des architectures d’entreprise autour de PostgreSQL et surtout une meilleure autonomie technique.