Serveurs Base de données

PostgreSQL : Memo des commandes SQL

Cet article a été mis à jour, vous consultez ici une archive de cet article!

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.

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