MasterDBA_Cours4 PDF

Administration et gestion de données Cours 4: Commandes de gestion de bases de données, schémas et tablespaces Hassane TAHIR UFR de MATHÉMATIQUES et INFORMATIQUE Master Informatique M1: Parcours Données, Connaissances et Intelligence  UNIVERSITÉ DE PARIS...

Administration et gestion de données Cours 4: Commandes de gestion de bases de données, schémas et tablespaces Hassane TAHIR UFR de MATHÉMATIQUES et INFORMATIQUE Master Informatique M1: Parcours Données, Connaissances et Intelligence  UNIVERSITÉ DE PARIS 2 Feuille de route Cours 4 : Commandes de gestion de bases de données, schémas et tablespaces →Commandes de Gestion de Bases de Données →Création d’une base de données →Modification d’une base de données →Suppression d’une base de données →Copier une base de données →Commandes de Gestion de Schémas de Bases de Données →Introduction à la gestion des schémas →Création des schémas →Modification de la définition des schémas →Suppression des schémas →Commandes de Gestion des Tablespaces →Introduction à la gestion des tablespaces →Création des tablespaces →Modification des tablespaces →Suppression des tablespaces  UNIVERSITÉ DE PARIS 3 Commandes de Gestion de Bases de Données Création d’une base de données Modification d’une base de données Changer les attributs de la base de données Renommer la base de données Changer le propriétaire de la base de données Changer le tablespace par défaut d'une base de données Modifier la valeur par défaut de la session pour une variable de configuration d'exécution pour une base de données Suppression d’une base de données Copier une base de données Copier une base de données sur le même serveur Copier une base de données d’un serveur à un autre  UNIVERSITÉ DE PARIS 4 Création d’une base de données Commande d’une création d’une base PostgreSQL Cette commande est utilisée pour créer une nouvelle base de données. Pour créer une nouvelle base de données, il faut utiliser l'instruction CREATE DATABASE. La base de données doit être unique. Si vous tentez de créer une base de données avec un nom qui existe déjà, PostgreSQL va générer une erreur. Syntaxe de base de l'instruction CREATE DATABASE: CREATE DATABASE Nom_de_base WITH [OWNER = Nom_de_role] [TEMPLATE = template ou modèle] [ENCODING = codage] [LC_COLLATE = collate] [LC_CTYPE = ctype] [TABLESPACE = Nom_de_tablespace] [ALLOW_CONNECTIONS = true | false] [CONNECTION LIMIT = Maximum_des_connexions_simultanées] [IS_TEMPLATE = true | false ];  UNIVERSITÉ DE PARIS 5 Création d’une base de données Paramètre Description OWNER Attribue un rôle qui va être le propriétaire de la base de données. Par défaut, le propriétaire est le rôle qui exécute la commande. Si le paramètre OWNER n’est pas renseigné ou omis dans l’instruction CREATE DATABASE, le propriétaire de la base de données est le rôle que vous utilisez pour exécuter l'instruction CREATE DATABASE. TEMPLATE Spécifie le modèle de la nouvelle base de données que vous allez créer. Par défaut, PostgreSQL utilise la base de données template1 comme modèle de base de données si le paramètre TEMPLATE n’est pas renseigné ou omis dans l’instruction CREATE DATABASE. ENCODING Définit le jeu de caractères pour la nouvelle base de données à créer. LC_COLLATE Spécifie l'ordre de tri (LC_COLLATE) que la nouvelle base de données à créer utilisera. Ce paramètre affecte l'ordre de tri des chaînes de caractères dans les requêtes contenant la clause ORDER BY. Il s'agit par défaut du LC_COLLATE de la base de données modèle (i.e. template). LC_CTYPE Spécifie la classification des caractères (ex: minuscule, majuscule, nombre) que la nouvelle base de données à créer utilisera. Il s'agit par défaut du LC_CTYPE de la base de données modèle (i.e. template). TABLESPACE Spécifie le nom de Tablespace pour la nouvelle base de données à créer. La valeur par défaut est le tablespace de la base de données modèle (i.e. template). CONNECTION LIMIT Spécifie le nombre maximum de connexions simultanées à la nouvelle base de données à créer. La valeur par défaut est -1, ce qui signifie illimité. Ce paramètre peut être utile dans les environnements d'hébergement partagé où vous pouvez configurer le nombre maximal de connexions simultanées pour une base de données particulière. ALLOW_CONNECTIONS Booléen qui permet ou empêche les connexions à la base de données. Si le paramètre a la valeur « False », vous ne pouvez pas vous connecter à la base de données. IS_TEMPLATE Si le paramètre a la valeur « True », tout utilisateur avec le privilège CREATEDB peut cloner la base de données. Si la valeur est « False », seuls les superutilisateurs ou le propriétaire de la base de données peuvent cloner cette base.  UNIVERSITÉ DE PARIS 6 Création d’une base de données Exemple de création d’une base PostgreSQL avec des paramètres par défaut Créer une base de données de gestion des ventes nommée BD_Ventes. CREATE DATABASE BD_Ventes; Exemple de création d’une base PostgreSQL avec des options Créer une base de données de gestion des ressources humaines nommée BD_RH avec un encodage UTF8 et avec un nombre de connexions simultanées à la base de données qui est égal à 50. CREATE DATABASE BD_RH; WITH ENCODING = 'UTF8’ CONNECTION LIMIT = 50;  UNIVERSITÉ DE PARIS 7 Modification d’une base de données Modification d’une base de données Changer les attributs de la base de données Renommer la base de données Changer le propriétaire de la base de données Changer le tablespace par défaut d'une base de données Modifier la valeur par défaut de la session pour une variable de configuration d'exécution pour une base de données  UNIVERSITÉ DE PARIS 8 Modification d’une base de données 1. Commande de modification des attributs d’une base de données PostgreSQL Cette commande est utilisée pour modifier les attributs d’une base de données. Pour modifier une base de données, il faut utiliser l'instruction ALTER DATABASE. Syntaxe de base de l'instruction ALTER DATABASE: ALTER DATABASE Nom_de_base WITH option; L'option peut être : IS_TEMPLATE LIMITE DE CONNEXION ALLOW_CONNECTIONS Notez que seuls les super utilisateurs ou les propriétaires de bases de données peuvent modifier ces paramètres.  UNIVERSITÉ DE PARIS 9 Modification d’une base de données 2. Renommer une base de données PostgreSQL L'instruction ALTER DATABASE RENAME TO suivante renomme une base de données PostgreSQL. Syntaxe de base de l'instruction ALTER DATABASE RENAME: ALTER DATABASE Nom_de_base RENAME TO Nouveau_Nom_de_base; Pour cette instruction, il faut noter les points suivants: Il n'est pas possible de renommer la base de données actuelle. Vous devez donc vous connecter à une autre base de données et la renommer à partir de cette base de données. Seuls les super utilisateurs et les propriétaires de bases de données disposant du privilège CREATEDB peuvent renommer la base de données.  UNIVERSITÉ DE PARIS 10 Modification d’une base de données 3. Changer le propriétaire d’une base de données PostgreSQL L'instruction ALTER DATABASE suivante remplace le propriétaire d'une base de données par le nouveau. Syntaxe de base de l'instruction ALTER DATABASE: ALTER DATABASE Nom_de_base OWNER TO Nouveau_propriétaire | utilisateur_actuel | session_utilisateur ; Pour cette instruction, les utilisateurs suivants peuvent modifier le propriétaire de la base de données : Le propriétaire de la base de données avec le privilège CREATEDB et est un membre direct ou indirect du nouveau rôle propriétaire. Les super utilisateurs.  UNIVERSITÉ DE PARIS 11 Modification d’une base de données 4. Changer le tablespace par défaut d’une base de données PostgreSQL L'instruction ALTER DATABASE suivante remplace le tablespace par défaut d'une base de données. Syntaxe de base de l'instruction ALTER DATABASE: ALTER DATABASE Nom_de_base SET TABLESPACE nouveau_tablespace ; Pour cette instruction, il faut noter les points suivants: L'instruction déplace physiquement les tables et les index de l'ancien tablespace vers le nouveau. Pour définir le nouveau tablespace, celui-ci doit être vide et il existe une connexion à la base de données. Les super utilisateurs et les propriétaires de bases de données peuvent modifier le tablespace par défaut de la base de données.  UNIVERSITÉ DE PARIS 12 Modification d’une base de données 5. Modifier les valeurs par défaut de la session dans une base de données PostgreSQL →Chaque fois que vous vous connectez à une base de données, PostgreSQL charge les variables de configuration du fichier postgresql.conf et utilise ces variables par défaut. →L'instruction ALTER DATABASE SET suivante est utilisée pour remplacer ces paramètres de configuration pour une base de données particulière. Syntaxe de base de l'instruction ALTER DATABASE: ALTER DATABASE Nom_de_base SET [paramètre_de_configuration] = [valeur] ; Seuls les super utilisateurs ou les propriétaires de base de données peuvent modifier la par la session par défaut pour une configuration d'exécution de la base de données.  UNIVERSITÉ DE PARIS 13 Suppression d’une base de données Commande de suppression d’une base de données PostgreSQL L'instruction DROP DATABASE supprime une base de données d'un serveur PostgreSQL. Syntaxe de base de l'instruction DROP DATABASE: DROP DATABASE [IF EXISTS] Nom_de_base [WITH (FORCE)]; Dans cette syntaxe : Spécifier le nom de la base de données que vous souhaitez supprimer après les mots-clés DROP DATABASE. Si vous supprimez une base de données inexistante, PostgreSQL générera une erreur. Pour éviter l'erreur, vous pouvez utiliser l'option [IF EXISTS]. Dans ce cas, PostgreSQL émettra une notification à la place. L'instruction DROP DATABASE échouera s'il existe des connexions actives à la base de données cible, sauf si vous utilisez l'option FORCE. L'option FORCE tentera de mettre fin à toutes les connexions existantes à la base de données cible. L'instruction DROP DATABASE supprime la base de données de l'entrée de catalogue et du répertoire de données. Pour exécuter l'instruction DROP DATABASE, vous devez être le propriétaire de la base de données.  UNIVERSITÉ DE PARIS 14 Copier une base de données 1. Copier une base de données PostgreSQL sur le même serveur Par exemple, copier une base de données PostgreSQL sur un serveur de base de données à des fins de test. Commande pour copier une base de données CREATE DATABASE Nom_de_base_cible WITH TEMPLATE Nom_de_base_source; Syntaxe de base de l'instruction Cette instruction copie la base de données source dans la base de données cible. Remarques En fonction de la taille de la base de données source, la copie peut prendre un certain temps. Si la base de données a des connexions actives, vous rencontrerez l'erreur suivante : ERREUR : la base de données source est accédée par d'autres utilisateurs  UNIVERSITÉ DE PARIS 15 Copier une base de données 2. Copier une base de données PostgreSQL d’un serveur à un autre Il existe plusieurs façons de copier une base de données entre des serveurs de bases de données PostgreSQL. Si la taille de la base de données source est importante (grande volumétrie) et que la connexion entre les serveurs de base de données est lente, vous pouvez sauvegarder la base de données source dans un fichier, copier le fichier sur le serveur distant et le restaurer. Tout d’abord, videz la base de données source (bd_source) dans un fichier. pg_dump -U postgres -d bd_source -f bd_source.sql Deuxièmement, copiez le fichier de vidage sur le serveur distant. Troisièmement, créez une nouvelle base de données (bd_cible) sur le serveur distant : CREATE DATABASE bd_cible; Enfin, restaurez le fichier dump sur le serveur distant : psql -U postgres -d bd_cible -f bd_source.sql  UNIVERSITÉ DE PARIS 16 Commandes de Gestion de Schémas de Bases de Données Introduction à la gestion des schémas Création des schémas Syntaxe de l’instruction de création d’un schéma. Création simple d’un schéma. Création d’un schéma pour un utilisateur. Création d’un schéma et ses objets. Modification de la définition des schémas Syntaxe de l’instruction de modification d’un schéma. Renommer un schéma. Changer le propriétaire d’un schéma. Suppression des schémas Syntaxe de l’instruction de suppression d’un schéma. Suppression de plusieurs schémas. Suppression d’un schéma non vide.  UNIVERSITÉ DE PARIS 17 Introduction à la gestion des schémas Schéma PostgreSQL Un schéma est une collection nommée d'objets de base de données, comprenant des tables, des vues, des index, des types de données, des fonctions, des procédures stockées et des opérateurs. Un schéma permet d'organiser et d'espacer les objets de base de données au sein d'une base de données. Pour accéder à un objet dans un schéma, il faut qualifier l'objet en utilisant la syntaxe suivante : Nom_Schema.Nom_Objet Une base de données peut contenir un ou plusieurs schémas. Cependant, un schéma n'appartient qu'à une seule base de données. De plus, deux schémas peuvent avoir des objets différents partageant le même nom. Les schémas permettent à plusieurs utilisateurs d'utiliser une base de données sans interférer les uns avec les autres. PostgreSQL crée automatiquement un schéma appelé public pour chaque nouvelle base de données. Quel que soit l'objet que vous créez sans spécifier le nom du schéma, PostgreSQL le placera dans ce schéma public. L’Instruction suivante renvoie tous les schémas de la base de données actuelle: SELECT * FROM pg_catalog.pg_namespace ORDER BY nspname;  UNIVERSITÉ DE PARIS 18 Introduction à la gestion des schémas Le chemin de recherche du schéma Lorsque une table est référencée en utilisant uniquement son nom, PostgreSQL recherche la table en utilisant le chemin de recherche de schéma, qui est une liste de schémas à rechercher. PostgreSQL accédera à la première table correspondante dans le chemin de recherche du schéma. S'il n'y a pas de correspondance, une erreur sera renvoyée, même si le nom existe dans un autre schéma de la base de données Le premier schéma du chemin de recherche est appelé schéma actuel. Notez que lorsque vous créez un nouvel objet sans spécifier explicitement de nom de schéma, PostgreSQL utilisera également le schéma actuel pour le nouvel objet. La fonction current_schema() renvoie le schéma actuel. La requête SELECT current_schema() revoie public. La commande SHOW search_path renvoie "$user", public. "$user" spécifie que le premier schéma que PostgreSQL utilisera pour rechercher l'objet, porte le même nom que l'utilisateur actuel. Par exemple, si vous utilisez l'utilisateur postgres pour vous connecter et accéder à une table, PostgreSQL recherchera cette table dans le schéma postgres. S'il ne trouve aucun objet de ce type, il continue à chercher l'objet dans le schéma public. Pour ajouter un nouveau schéma dans le chemin de recherche, il faut utiliser la commande suivante: CREATE SCHEMA nouveau_schema; SET search_path TO nouveau_schema, public;  UNIVERSITÉ DE PARIS 19 Création des schémas 1. Syntaxe de l’instruction de création d’un schéma dans PostgreSQL L'instruction CREATE SCHEMA permet de créer un nouveau schéma dans la base de données actuelle. CREATE SCHEMA [IF NOT EXISTS] Nom_de_schema; Dans cette syntaxe: Le nom de schéma doit être unique dans la base de données actuelle. En option, utiliser IF NOT EXISTS pour créer un schéma avec la condition que ce schéma n’existe pas déjà dans la base de données actuelle. Tenter de créer un nouveau schéma sans l’option IF NOT EXISTS va générer une erreur. L’instruction CREATE SCHEMA doit avoir le privilège CREATE dans la base de données actuelle. 2. Création simple d’un schéma La commande suivante permet de créer un schéma pour la comptabilité nommé sch_compta: CREATE DATABASE sch_compta;  UNIVERSITÉ DE PARIS 20 Création des schémas 3. Création d’un schéma pour un utilisateur dans PostgreSQL L'instruction CREATE SCHEMA permet de créer un schéma pour un utilisateur. CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION Nom_Utilisateur; Dans cette syntaxe: Le nom de schéma est le même que le nom d’utilisateur. Exemple: Voici les étapes pour créer un schéma pour l’utilisateur David. Créer nouveau rôle (expliqué dans le cours 5) nommé David. CREATE ROLE David LOGIN PASSWORD 'Usr20@Dav!25'; Créer un schéma pour l’utilisateur David. CREATE SCHEMA AUTHORIZATION David; Créer maintenant un nouveau schéma DEV qui appartiendra à l’utilisateur David. CREATE SCHEMA IF NOT EXISTS DEV AUTHORIZATION David ;  UNIVERSITÉ DE PARIS 21 Création des schémas 4. Création d’un schéma et ses objets dans PostgreSQL L'instruction CREATE SCHEMA peut être utilisée pour créer un schéma sch_objets et ses objets comme les tables (i.e. table1), les vues (i.e. vue1), etc... CREATE SCHEMA sch_objets CREATE TABLE table1( id SERIAL NOT NULL, champ1 INT NOT NULL, champ2 DATE NOT NULL ) CREATE VIEW vue1 SELECT ID, champ1 FROM table1 WHERE champ2