PLB CONSULTANT : 03. PostgreSQL, administration

Jour 1

Présentation de PostgreSQL et des outils principaux

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

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

Installation de PostgreSQL à partir des sources.

Premiers réglages post-installation.

Bases et instances 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

Création d’une base de données

Étude des paramètres de base du fichier de configuration d’une instance

Démarrage – Observations des logs

Sécurisation de l’instance

Quiz de validation des acquis des notions de la première journée de formation

Jour 2

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

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

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.

Quiz de validation des acquis des notions de la deuxième journée de formation.

Jour 3

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

Exemples commentés de procédures stockées et de triggers.

Affichage des données de la base créé précédemment à travers un navigateur 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

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

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

Quiz Final de validation des acquis