Lernzettel: Introduction à la gestion de bases de données PostgreSQL

📋 Plan du Cours

1. Rôle des bases de données
2. PostgreSQL et exploitation
3. Connexion et exploration du serveur
4. Lecture des données SQL
5. Rôles et gestion des droits
6. Application et base de données
7. Visualisation de données et KPI
8. Grafana et tableaux de bord
9. Chaîne de traitement et limites

📖 1. Rôle des bases de données

🔑 Notions clés & Définitions

• Base de données : Une base de données est un système qui stocke et centralise des informations afin de les rendre exploitables et partagées.

📝 Points essentiels

• Une base de données stocke les données afin de les conserver durablement.
• Une base de données centralise l’information pour la rendre accessible à plusieurs parties du système.
• Une base de données garantit la cohérence des informations stockées.
• Une base de données gère les accès et permet le partage entre applications.

📖 2. PostgreSQL et exploitation

🔑 Notions clés & Définitions

• PostgreSQL : PostgreSQL est un SGBD relationnel open-source utilisé pour stocker et exploiter des données via SQL.
• SGBD relationnel : Un SGBD relationnel gère des données structurées en tables et permet de les interroger avec le langage SQL.

📝 Points essentiels

• PostgreSQL est basé sur des caractéristiques comme SQL standard, robustesse, transactions, sécurité, rôles et privilèges, et extensibilité.
• PostgreSQL est utilisé par des administrations, universités, entreprises, hébergeurs et applications web.
• L’exploitation inclut la lecture des données et l’écriture de requêtes pour les analyser.

📖 3. Connexion et exploration du serveur

🔑 Notions clés & Définitions

• psql : psql est l’outil en ligne de commande utilisé pour se connecter et interagir avec un serveur PostgreSQL.
• utilisateur courant : L’utilisateur courant est le compte effectif avec lequel une session SQL se déroule dans PostgreSQL.

📝 Points essentiels

• La connexion peut démarrer avec sudo -u postgres psql pour utiliser le compte postgres.
• On peut se connecter à une base via psql -U utilisateur -d base.
• Le nom de l’utilisateur courant se récupère avec SELECT current_user;.
• L’exploration passe par \l pour lister les bases, \c nom_base pour se connecter, \dt pour lister les tables, et \d nom_table pour afficher la structure.

📖 4. Lecture des données SQL

🔑 Notions clés & Définitions

• SELECT : SELECT est la commande SQL utilisée pour lire des données depuis une ou plusieurs tables.
• COUNT : COUNT est une fonction SQL qui permet de compter des lignes selon une requête.

📝 Points essentiels

• Une lecture simple utilise SELECT * FROM utilisateur; pour obtenir les lignes d’une table.
• Le nombre de lignes se calcule avec SELECT COUNT(*) FROM utilisateur;.
• On limite un résultat avec SELECT * FROM utilisateur LIMIT 10; pour afficher seulement les premières lignes.

📖 5. Rôles et gestion des droits

🔑 Notions clés & Définitions

• rôle PostgreSQL : Un rôle PostgreSQL regroupe des capacités comme se connecter, lire des données, modifier des tables, ou administrer le serveur.
• privilèges : Les privilèges sont les autorisations accordées ou retirées à un rôle pour agir sur des objets, comme une table.

📝 Points essentiels

• CREATE ROLE etudiant LOGIN PASSWORD 'secret'; permet de créer un rôle avec identifiant et mot de passe.
• GRANT SELECT ON utilisateur TO etudiant; accorde un droit de lecture sur la table utilisateur au rôle etudiant.
• REVOKE SELECT ON utilisateur FROM etudiant; retire un droit de lecture au rôle etudiant.
• Principe fondamental : un utilisateur ne doit avoir que les droits nécessaires pour limiter l’exposition des données.

📖 6. Application et base de données

🔑 Notions clés & Définitions

• interface applicative : Une application peut n’être qu’une interface, tandis que les données réelles résident dans PostgreSQL.
• données applicatives : Les données applicatives sont les informations stockées dans PostgreSQL qui pilotent le comportement de l’application.

📝 Points essentiels

• Modifier des données dans la base modifie le comportement de l’application.
• Les vraies données de l’application incluent par exemple utilisateurs, mots de passe, configurations, permissions et historiques.
• Le lien entre application et base se résume au fait que la base contient l’état que l’interface utilise.

📖 7. Visualisation de données et KPI

🔑 Notions clés & Définitions

• tableau de bord décisionnel : Un tableau de bord décisionnel sert à synthétiser et analyser des données afin de soutenir la prise de décision.
• KPI : Un KPI est un indicateur clé de performance qui mesure une activité pour la suivre et l’évaluer.

📝 Points essentiels

• Un tableau de bord sert à synthétiser, surveiller, analyser et prendre des décisions.
• Des exemples de sujets de tableau de bord incluent sécurité, réseau, finances, statistiques et activité métier.
• Les exemples de KPI incluent nombre d’utilisateurs, nombre d’incidents, volume d’affaires, taux d’erreur, et temps de réponse.

📖 8. Grafana et tableaux de bord

🔑 Notions clés & Définitions

• Grafana : Grafana est une plateforme open-source de visualisation qui construit des tableaux de bord à partir de sources de données.
• source de données : Une source de données est un système auquel Grafana se connecte pour lire et afficher des informations.

📝 Points essentiels

• Grafana fournit des tableaux de bord, graphiques, cartes, indicateurs et peut gérer des connexions multiples.
• Grafana peut lire depuis PostgreSQL, Prometheus, MySQL et Elasticsearch.
• Grafana ne stocke pas les données : il les lit depuis PostgreSQL.
• La période sélectionnée fait évoluer automatiquement les graphiques selon le temps (dernière heure, 24 heures, 7 jours, 30 jours, 1 an).

📖 9. Chaîne de traitement et limites

🔑 Notions clés & Définitions

• chaîne de traitement : La chaîne de traitement décrit le chemin entre l’application, la base, les requêtes SQL, la visualisation Grafana et les tableaux de bord.
• limites de requête : Une limite de requête est le risque qu’une requête SQL mal conçue devienne trop lente ou inefficace.

📝 Points essentiels

• La chaîne de traitement suit : Application → PostgreSQL → Requêtes SQL → Grafana → Tableaux de bord.
• Une mauvaise requête peut être lente et dégrader les performances.
• Un mauvais droit peut exposer des données.
• Une donnée erronée entraîne une mauvaise analyse via le tableau de bord.

📊 Tableaux de synthèse

Exploitation vs administration

Aspect	Exploitation	Administration
Actions	Lire des données et écrire des requêtes	Gérer des utilisateurs et attribuer des droits
Responsabilités	Créer des tableaux de bord et analyser	Sauvegarder, surveiller et maintenir le serveur
Finalité	Exploiter les informations	Assurer la gestion et le bon fonctionnement du serveur

⚠️ Pièges & confusions fréquents

1. Penser que Grafana stocke les données : il les lit depuis PostgreSQL.
2. Confondre exploitation et administration : l’exploitation vise l’analyse et la lecture, l’administration vise la gestion du serveur et des droits.
3. Accorder des droits plus larges que nécessaire : cela augmente le risque d’exposition des données.
4. Croire qu’un tableau de bord reste fiable si les données sont fausses : une donnée erronée produit une analyse erronée.
5. Oublier l’historique temporel : sans données datées et réalistes, l’analyse sur le temps devient inutile.
6. Demander trop de données sans limiter : utiliser LIMIT aide à contrôler la taille du résultat.

✅ Checklist Examen

1. Expliquer le rôle d’une base de données : stockage, centralisation, cohérence, gestion des accès et partage entre applications.
2. Distinguer exploitation et administration en citant les activités typiques de chacune.
3. Identifier ce qu’est un rôle PostgreSQL et les capacités possibles d’un rôle (connexion, lecture, modification, administration).
4. Savoir connecter PostgreSQL avec sudo -u postgres psql et via psql -U utilisateur -d base.
5. Réaliser une exploration avec \l, \c nom_base, \dt et \d nom_table.
6. Écrire une requête SQL simple de lecture avec SELECT * FROM table.
7. Écrire une requête de comptage avec COUNT(*) et une requête de limitation avec LIMIT.
8. Donner un exemple de création de rôle avec CREATE ROLE... LOGIN... PASSWORD...
9. Savoir accorder et retirer un droit avec GRANT et REVOKE (exemple SELECT sur une table).
10. Décrire le lien application ↔ base et rappeler que modifier la base modifie le comportement de l’application.
11. Définir KPI comme indicateur clé de performance et citer au moins deux exemples de KPI.
12. Décrire Grafana comme outil de visualisation et rappeler qu’il ne stocke pas les données mais les lit depuis la source.
13. Relier Grafana au temps : expliquer pourquoi des données datées et réalistes sont nécessaires aux graphiques temporels.
14. Décrire la chaîne de traitement Application → PostgreSQL → Requêtes SQL → Grafana → Tableaux de bord et citer au moins trois limites/vigilances (requête lente, droits, tableau trompeur, données erronées).