TP n°4 : Gestion de base de données reparties
(PostgreSQL)
Auteur : Badr TAJINI - Course Distributed databases - EFREI - 2023/2024
La réalisation du TD gestion de base de données reparties se fera sur le même cluster avec Citus.
Nous considérons la BD déjà existante (postgresql) comme la BD du site S1 et donc il faudra
créer une nouvelle BD (bd2) qui représentera la BD du site S2. Si la BD du site S1 n'existe pas, il
faudrait la créer avec celle du site S2.
Étape 1 : Création d'une nouvelle base de données
Avant la création de la nouvelle base de données :
1. Assurez-vous que votre instance PostgreSQL est opérationnelle et accessible. Sur Azure,
vous pouvez vérifier cela dans le portail Azure sous la section "Overview" de votre base de
données PostgreSQL Hyperscale (Citus).
2. Connectez-vous à votre instance via pgAdmin.
À travers pgAdmin :
1. Cliquez droit sur "Databases", puis sélectionnez "Create" > "Database..." pour initier la
création d'une nouvelle base de données.
2. Dans la boîte de dialogue, donnez un nom à votre base de données, par exemple "bd2".
3. (Facultatif) Configurez des paramètres supplémentaires selon vos besoins, mais pour un TD,
vous pouvez souvent laisser les autres options par défaut pour simplifier la configuration.
4. Cliquez sur "Save" pour créer la base de données.
Nous disposons maintenant de deux bases de données :
La base de données "citus" sur laquelle nous avons travaillé lors des TP précédents.
La nouvelle base de données "bd2".
Pour chaque instance de la base de données :
1. Dans pgAdmin, vous pouvez ouvrir une fenêtre de requête en cliquant droit sur la base de
données correspondante et en sélectionnant "Query Tool".
2. Pour vous connecter à "bd2" comme administrateur, utilisez les mêmes identifiants que
ceux utilisés pour vous connecter à l'instance Citus.
Étape 2 : Création d'un nouveau compte utilisateur
Dans pgAdmin avec PostgreSQL/Citus :
1. Ouvrez une fenêtre de requête SQL sur votre base de données Citus.
2. Créez un nouveau rôle (utilisateur) avec les commandes suivantes :
-- Créer un nouvel utilisateur sur le nœud coordinateur
CREATE ROLE user1 WITH LOGIN PASSWORD 'pass1';
GRANT ALL PRIVILEGES ON DATABASE citus TO user1;
-- Répéter le processus pour le deuxième utilisateur, si nécessaire
CREATE ROLE user2 WITH LOGIN PASSWORD 'pass2';
GRANT ALL PRIVILEGES ON DATABASE citus TO user2;
3. Une fois les utilisateurs créés, vous pouvez vous connecter avec ces nouveaux comptes en
utilisant pgAdmin ou toute autre interface SQL.
Étape 3 : Connexion entre les bases de données (Foreign Data
Wrappers)
Dans PostgreSQL, au lieu d'utiliser DBLinks, vous utiliserez Foreign Data Wrappers (FDW) :
1. Installez l'extension FDW appropriée, si ce n'est pas déjà fait :
-- Pour postgres_fdw qui permet des connexions entre instances PostgreSQL
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
2. Créez un serveur étranger pour représenter la base de données distante (remplacez
foreign_db et foreign_host par les noms appropriés) :
-- Créer le serveur FDW
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'foreign_host', dbname 'foreign_db', port '5432');
3. Créez un mapping utilisateur pour le serveur étranger :
-- Créer un mapping utilisateur pour le serveur étranger
CREATE USER MAPPING FOR user1
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'foreign_password');
4. Importez la table ou créez une table étrangère pour interagir avec la base de données
distante :
-- Importer ou créer une table étrangère
CREATE FOREIGN TABLE foreign_table_name (...)
SERVER foreign_server
OPTIONS (table_name 'remote_table_name');
Remarque : Dans l'environnement Citus, vous n'aurez probablement pas à créer de liens
manuellement entre les nœuds de travail car Citus gère la distribution des données et les
requêtes entre les nœuds automatiquement. Vous pourriez utiliser FDW si vous devez
connecter des instances PostgreSQL distinctes ou d'autres bases de données hétérogènes,
mais cela ne fait pas partie de la configuration standard de Citus.
Exercice
Une entreprise française spécialisée dans la fabrication de l'ingénierie mécanique, notamment
de la visserie-boulonnerie (vis, écrou, boulons...). Cette entreprise stocke toutes ces informations
sur ses projets , fournisseurs , produits ainsi que les commandes préparées afin de garder
une trace.
Voici le schéma relationnel relatif à cette gestion :
Projet(NumPj, NomPj, Ville)
Produit(NumPd, NomPd, Couleur, Poids, Prix, Ville)
Fournisseur(NumF, NomF, Statut, Ville)
Commande(NumF, NumPd, NumPj, Quantité) où quantité est la quantité du produit.
Création des tables distribuées et de référence :
Dans Citus, vous pouvez créer des tables distribuées qui répartissent les données sur plusieurs
nœuds de travail en fonction d'une clé de distribution. Les tables de référence sont des tables
qui sont entièrement répliquées sur chaque nœud de travail.
1. Connectez-vous à votre instance Citus via pgAdmin.
2. Ouvrez une fenêtre de requête SQL.
3. Créez les tables distribuées et de référence en utilisant la clé de distribution pertinente. Ceci
est un exemple :
-- Créer une table distribuée pour 'Produit'
SELECT create_distributed_table('Produit', 'NumPd');
-- Créer une table de référence pour 'Fournisseur'
SELECT create_reference_table('Fournisseur');
-- Supposons que 'Projet' est une table de référence
SELECT create_reference_table('Projet');
-- 'Commande' peut être une table distribuée si les commandes sont nombreuses
SELECT create_distributed_table('Commande', 'NumPj');
4. Insérez les données dans les tables comme vous le feriez dans une base de données
PostgreSQL standard. Citus gérera la distribution des données en arrière-plan.
Remarque : Dans un environnement distribué Citus, les concepts tels que les sites S1 et S2
ne sont pas utilisés de la même manière que dans Oracle. Citus automatise la répartition
des données et l'exécution des requêtes sur les nœuds de travail (worker). Vous pouvez
cependant simuler une certaine forme de fragmentation en choisissant différentes clés de
distribution pour vos tables distribuées.
Requêtes :
1. Donner tous les produits dont le poids est le plus bas :
2. Donner les numéros des fournisseurs impliqués dans le projet J1 :
3. Donner les numéros des fournisseurs qui fournissent le produit P1 au projet J1 :
4. Donner le numéro de produit qui a le plus été commandé :
Mise à jour des tables :
5. Insérer la ligne (‘F6’, ‘Stephane’, ‘10’, ‘Marseille’) dans la table Fournisseur :
6. Insérer la ligne (‘P7’, ‘boulon’, ‘noir’, ‘20’, ‘Lille’) dans la table Produit :
7. Les prix des produits ont connu une augmentation de 02%, faites la requête de
modification nécessaire :
8. Supprimer toutes les commandes en quantité supérieure à 1000 :
Remarque : Ces requêtes sont basiques et ne prennent pas en compte la distribution des
données dans Citus. Si vos tables sont déjà distribuées, Citus exécutera ces requêtes en
parallèle sur les nœuds de travail. Dans Citus, les opérations d'agrégation et de jointure
sont effectuées de manière transparente sur les nœuds de travail, vous n'avez donc pas
besoin d'utiliser des concepts tels que DBLinks. Si vous devez exécuter ces requêtes sur une
base de données externe à partir de votre cluster Citus, vous pouvez utiliser PostgreSQL
Foreign Data Wrappers (FDW) pour vous connecter à ces bases de données externes.
Fonctionnalités de distribution et de parallélisation des
données
9. : Comment répartir une table de produits dans un cluster Citus pour optimiser les requêtes
par ville ?
10. : Comment pouvez-vous insérer des données dans la table commande qui est distribuée sur
plusieurs nœuds ?
11. : Comment effectuer une requête agrégée pour trouver le total des quantités commandées
pour chaque produit ?
12. : Comment mettre à jour les prix dans la table produit si vous savez que les données sont
réparties sur plusieurs nœuds ?