Serveurs Base de données

PostgreSQL : Memo des commandes SQL

Cet article est en cours de travaux, des modifications sont en cours de réalisation, revenez plus tard le reconsulter. Merci.
Table des matières

Introduction




A force d'utiliser des commandes SQL, j'ai pensé les mettre dans un seul endroit : sur mon SITE.

Ainsi, je cherche à faire quelque chose, je viens chercher ici.

Si je ne sais pas, je cherche sur Google, et je renseigne ici, je retrouverai plus facilement.

Se connecter à la base



Pour se connecter sous Linux en ligne de commande, on basculera en utilisateur postgres :

Code BASH :
su - postgres


Ensuite on lance la commande :

Code BASH :
psql


Et pour un hôte distant, on précise l'IP :

[code=bash][/code]

Après on peut aussi utiliser des logiciels graphiques comme pgAdmin ou HeidiSQL.


Commandes depuis la console psql



Une fois connecté en ligne de commande on est sur la base postgres :

Code :
postgres=#


Pour lister les bases de données :

Code :
\l


Pour se connecter à une base de données :

Code :
\c baseadrien


On a une sortie nous indiquant la bascule :

Code :
Vous êtes maintenant connecté à la base de données « baseadrien » en tant qu'utilisateur « postgres ».


Le prompt change avec le nom de la base de données :

Code :
baseadrien=#


Pour lister les tables :

Code :
\dt


Code :
         Liste des relations
 Schéma |  Nom   | Type  | Propriétaire 
--------+--------+-------+--------------
 public | table1 | table | adrien
(1 ligne)


Pour quitter :

Code :
\q



Commandes de base



Bases de données




Créer et supprimer des bases




Pour crée une base de données, saisir simplement (exemple avec adrien_test_copie) :

Code SQL :
CREATE DATABASE adrien_test_copie
WITH
OWNER = adrien
ENCODING = 'UTF8'
LC_COLLATE = 'fr_FR.UTF-8'
LC_CTYPE = 'fr_FR.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
 



Pour la supprimer (exemple avec adrien_test_copie) :

Code SQL :
DROP DATABASE adrien_test_copie;


Si vous avez un message du style :

Code TEXT :
ERREUR:  la base de données « adrien_test » est en cours d'utilisation par d'autres utilisateurs


Consultez le paragraphe Killer les sessions plus bas.

Utiliser les bases




Pour se connecter à la base :

[code=sql][/code]


Gestion des utilisateurs




Créer des utilisateurs




Pour créer un utilisateur simple, voici la commande :

Code SQL :
CREATE ROLE adrien WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'SuperMot2Passe';



Attribuer des droits aux utilisateurs



Pour attribuer tous les droits à un utilisateur (en faire en quelque sortes un deuxième root) :




Changer un mot de passe d'utilisateur de PostgreSQL




Cette commande fonctionne uniquement pour PostgreSQL :

Code SQL :
ALTER USER adrien WITH PASSWORD 'SuperMot2Passe!';



Voir les utilisateurs




Pour voir les utilisateurs créés :

Code SQL :
\du



Voir les droits des utilisateurs




Pour un utilisateur donné, on peut voir ses droits de la façon suivante :

[code=sql][/code]


Renommer un utilisateur



Si on souhaite renommer un utilisateur, on utilisera ALTER USER :

Code SQL :
ALTER USER adrien RENAME TO nouveauadrien;



Tables




Créer et supprimer des tables




Pour créer une table simple, voici un exemple :

Code SQL :
CREATE TABLE table1       
(                         
    id serial PRIMARY KEY,
    message text          
);


Ici serial est in entier qui s'autoincrémente.

SELECT




INSERT




UPDATE




DELETE




ALTER



Donner les droits d'une table à un utilisateur (table1 à adrien) :

Code SQL :
ALTER TABLE table1 OWNER TO adrien;


RENAME




Administration avancée



Ici, on retrouve des commandes d'admin avancées.

Recharger la config PostgresSQL



Lorsqu'on modifie le fichier pg_hba.conf, pour prendre en compte les nouveaux paramétrages à chaud (sans interrompre la prod), on va pouvoir lancer la commande SQL suivante :

Code BASH :
SELECT pg_reload_conf();



Changer le owner de toutes les tables



Quand on a une base avec 6300 tables, pas facile de changer le propriétaire des tables.
Voici un script BASH (oneliner) utilisant la commande psql depuis l'utilisateur postgres :

Code BASH :
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" VOTRE_BDD` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" VOTRE_BDD; done


Supprimer toutes les tables d'une BDD



Quand on a une base avec 6300 tables, pas facile de supprimer toutes les tables. L'idée est de réimporter un dump sans recréer la base.
Voici un script BASH (oneliner) utilisant la commande psql depuis l'utilisateur postgres :

Code BASH :
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" VOTRE_BDD` ; do  psql -c "drop table \"$tbl\" CASCADE " VOTRE_BDD ; done


Killer les sessions



Si vous souhaiter réimporter une base (et donc supprimer et recréer la base) et qu'elle est en cours d'utilisation, vous pouvez avoir ce type de message :

Code :
ERREUR:  la base de données « adrien_test » est en cours d'utilisation par d'autres utilisateurs
DÉTAIL : 3 autres sessions utilisent la base de données.


Récupérez les PID des connexions (exemple avec la base adrien_test) :

Code SQL :
SELECT pid FROM pg_stat_activity WHERE datname='adrien_test';


Vous avez une sortie de ce type :

Code TEXT :
   pid   
---------
 2633163
 2633180
 2633196
(3 lignes)


Il suffit de kill les sessions avec cette commande :

Code SQL :
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid IN (2633163, 2633180 ,2633196 );


On a un retour "t" sur chaque connexion terminées :

Code TEXT :
 pg_terminate_backend 
----------------------
 t
 t
 t
(3 lignes)


Cloner une base de données à chaud



On peut le faire en oneliner avec pg_dump suivi de psql (depuis la console Linux connecté en root OU postgres) :

Code BASH :
pg_dump -U postgres adrien_db | psql -d adrien_db_copie -U postgres
Cette page a été vue 17498 fois