Comment modifier, renommer, supprimer et copier des bases de données dans PostgreSQL

Ce post examine certaines des commandes disponibles pour gérer les bases de données PostgreSQL à partir de l’invite PSQL. Nous passerons en revue les méthodes pour modifier, renommer, abandonner et copier une base de données.

Rappelons quelques commandes de base de PostgreSQL :

1. Syntaxe de création de base de données:

CREATE DATABASE name user_name ] template ] encoding ] lc_collate ] lc_ctype ] tablespace ] connlimit ] ]

2. Pour obtenir une liste des bases de données dans le cluster actuel:

a. \l+

b. select * from pg_database ;

ALTER database statements

ALTER database statements peut être utilisé pour changer les attributs existants de la base de données cible.

Changer le propriétaire de la base de données

Pour modifier le propriétaire de la base de données, l’utilisateur connecté doit être le propriétaire de la base de données et être également un membre direct ou indirect du nouveau rôle propriétaire, et l’utilisateur connecté doit avoir le privilège CREATEDB.

Syntaxe

ALTER DATABASE <database_name> OWNER TO <new_owner>;

Exemple

ALTER DATABASE test OWNER TO user;

Configuration de la base de données modèle

Par défaut, nous n’avons que deux bases de données modèles présentes dans un cluster-template0 et template1. Les autres bases de données ne peuvent pas être utilisées comme modèle par défaut. Dans le cas où vous devez créer un modèle de base de données personnalisé pour votre environnement, vous pouvez utiliser cette option.

Syntaxe

ALTER DATABASE <database_name> WITH IS_TEMPLATE = true;

Exemple

ALTER DATABASE test WITH IS_TEMPLATE = true;

Restriction des nouvelles connexions pour une base de données spécifique

Cette option est très utile pour maintenir la base de données. Vous pouvez restreindre les nouvelles connexions à une base de données spécifique pendant la fenêtre de maintenance de la base de données. Pour activer cette option, aucun utilisateur n’est connecté à la base de données cible.

Syntaxe

ALTER DATABASE <database_name> WITH ALLOW_CONNECTIONS = false;

Exemple

ALTER DATABASE test2 WITH ALLOW_CONNECTIONS = false;

Restriction de la limite de connexion pour une base de données spécifique.

Pour la sécurité et des performances optimales, vous pouvez limiter le nombre de connexions qui peuvent se connecter à la base de données cible. Par défaut, la valeur de ce paramètre est illimitée, c’est-à-dire -1, pour chaque base de données.

Syntaxe

ALTER DATABASE <database_name> WITH CONNECTION LIMIT <max_number_of_connections>;

Exemple

ALTER DATABASE test2 WITH CONNECTION LIMIT 10;

Changer le tablespace par défaut de la base de données

Vous pouvez déplacer physiquement les objets du tablespace par défaut, comme les tables et les index, du tablespace par défaut vers un nouveau tablespace personnalisé. Le nouveau tablespace par défaut doit être vide pour cette base de données, et personne ne peut être connecté à la base de données pendant cette activité.

Note : Le propriétaire de la base de données ou un superutilisateur doit effectuer ce changement, et l’utilisateur doit avoir des privilèges CREATE pour le nouveau tablespace.

Syntaxe

ALTER DATABASE <database_name> SET TABLESPACE <custom_tablespace_name>;

Exemple

ALTER DATABASE test SET TABLESPACE custom_tblspc;

Enoncés de base de données RENAME

Vous pouvez changer le nom de la base de données actuelle en utilisant RENAME. Cependant, vous ne pouvez pas renommer la base de données pendant qu’elle est accessible par d’autres connexions.

Note : Le propriétaire de la base de données ou le superutilisateur doit effectuer cette modification, et l’utilisateur doit avoir les privilèges CREATEDB.

Syntaxe

ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;

Exemple

ALTER DATABASE test RENAME TO test123;

DROP déclarations de base de données

Vous pouvez DROP la base de données cible. Cela supprime les entrées de catalogue pour la base de données et supprime le répertoire contenant les données. Elle ne peut être exécutée que par le propriétaire de la base de données. De plus, elle ne peut pas être exécutée lorsque vous ou quelqu’un d’autre est connecté à la base de données cible.

Note : DROP DATABASE ne peut pas être annulé.

Syntaxe

DROP DATABASE <database_name>;

Exemple

DROP DATABASE test2;

.