Show Menu
Cheatography

PostgreSQL Cheat Sheet by

PostgreSQL est un système de gestion de base de données relationnelle et objet (SGBDRO). Souvent utilisé là où MySQL ne suffit pas et où la lourdeur d’un Oracle n’est pas justifiée, il est réputé pour sa fiabilité. Son langage de programmation intégré, le PL/pgSQL, affiche d’excellentes performances et permet d’aller beaucoup plus loin qu’avec le traditionnel SQL.

Bases

Afficher la version du serveur PostgreSQL
postgres=# SELECT version();
Afficher la version du client PostgreSQL
psql -V
Vérifier si Postgres tourne
/etc/i­nit.d/­pos­tgresql status
Démarrer / arrêter / redémarrer Postgres (SysVinit)
/etc/i­nit.d/­pos­tgresql start | stop | restart
Démarrer / arrêter / redémarrer Postgres (Systemd)
systemctl start | stop | restart postgr­esq­l.s­ervice
Saisir nos requêtes dans notre éditeur de prédil­ection
postgres=# \e
Changer le mot de passe root de PostgreSQL
psql postgres postgres
 
postgres=# ALTER ROLE postgres WITH PASSWORD 'toto';
 
postgres=# \q
Lister les fonctions dispon­ibles
postgres=# \df
Afficher l’aide
postgres=# \?
 
postgres=# \h CREATE
 
postgres=# \h CREATE INDEX
Lister les DB
postgres=# \l
Créer une DB depuis Postgres
postgres=# CREATE DATABASE maDB WITH OWNER akiko;
Créer une DB depuis le Shell
postgr­es@­deb­ian­VM:~$ /usr/b­in/­cre­atedb maDB -O midori
Supprimer une base depuis Postgres
postgres=# DROP DATABASE maDB;
Supprimer une base depuis le Shell
postgr­es@­deb­ian­VM:~$ dropdb maDB
Lister les tables d’une DB
postgres-# \c postgres
 
postgres-# \d
Obtenir une liste des types de données dispon­ibles dans PostgreSQL
postgres=# SELECT typnam­e,t­yplen from pg_type where typtyp­e='b';
Rediriger les résultats d’une requête vers un fichier
postgres=# \o nom_fi­chier (active la redire­ction)
 
postgres=# \o (annule la redire­ction)

Sauveg­ardes

Sauveg­arder une seule base
pg_dump -U utilis­ateur nom_DB -f madb.sql
Sauveg­arder toutes les bases
pg_dumpall > tout.sql
Sauveg­arder les objets globaux (users + tables­paces)
pg_dumpall -g > tout.sql
Sauveg­arder une table
pg_dump --table articles -U midori nom_DB -f unetab­le.sql
Restaurer une base
psql -U midori -d nom_DB -f madb.sql
Restaurer toutes les bases
psql -f tout.sql
Restaurer une table
psql -f unetab­le.sql nom_DB
Sauveg­arder une base locale et la restaurer sur un serveur distant en une ligne
pg_dump nom_DB­_source | psql -h serveur nom_DB­_cible

Index

Voir les index d’une table
postgres=# \d nom_table
Création d’un index sur une table
CREATE INDEX nom ON table USING type_index (colonne);
Postgres supporte 5 types d’index : Balanc­ed-Tree (btree ; utilisé par défaut), hash (trans­actions non-jo­urn­ali­sées, décons­eillé), Genera­lized Search Tree (gist), Genera­lized Inverted Indexes (gin) et Space-­Par­tit­ioned GIST (spgist).

Invest­iguer, analyser

Consulter le fichier d’hist­orique
cat ~/.psq­l_h­istory
Activer / désactiver le timing
postgres=# \timing
Obtenir les détails d’exéc­ution d’une requête sans la faire tourner
postgres=# EXPLAIN SELECT typnam­e,t­yplen from pg_type where typtyp­e='b';
Obtenir les statis­tiques d’une requête qui vient de tourner
postgres=# EXPLAIN ANALYSE SELECT typnam­e,t­yplen from pg_type where typtyp­e='b';
Un peu à la manière de Linux avec son bash_h­istory, PostgreSQL conserve une liste des commandes exécutées dans le fichier $HOME/.ps­ql_­history.

Le timing chrono­mètre le temps qu’une requête prend pour s’exéc­uter.
 

Utilis­ateurs et rôles

Créer un utilis­ateur depuis le Shell
/usr/b­in/­cre­ateuser midori
Créer un utilis­ateur depuis Postgres
postgres=# CREATE USER akiko WITH password 'yamete';
Accorder des privilèges à un utilis­ateur
postgres=# GRANT ALL PRIVILEGES ON DATABASE CityHunter TO akiko;
Supprimer un utilis­ateur depuis le Shell
/usr/b­in/­dro­puser midori
Supprimer un utilis­ateur depuis Postgres
postgres=# DROP USER akiko;
Lister les rôles
postgres=# \du
Créer un rôle
postgres=# CREATE ROLE formateur WITH LOGIN ENCRYPTED PASSWORD 'appre­ndre' CREATEDB;
Créer un rôle avec plusieurs privilèges
postgres=# CREATE ROLE demidieu WITH LOGIN ENCRYPTED PASSWORD 'toto' CREATEDB CREATEROLE REPLIC­ATION SUPERUSER;
Modifier un rôle
postgres=# ALTER ROLE demidieu CREATEROLE CREATEDB REPLIC­ATION SUPERUSER;
Supprimer un rôle
postgres=# DROP ROLE formateur;
Ces deux variantes nécess­itent d'être en utilis­ateur postgres (su - postgres).

Espace disque

Calculer l’espace disque occupé par une base
postgres=# SELECT pg_dat­aba­se_­siz­e('­foo­db');
 
postgres=# SELECT pg_siz­e_p­ret­ty(­pg_­dat­aba­se_­siz­e('­foo­db'));
Calculer l’espace disque occupé par une table (index inclus)
postgres=# SELECT pg_siz­e_p­ret­ty(­pg_­tot­al_­rel­ati­on_­siz­e('­gro­sse­_ta­ble'));
Calculer l’espace disque occupé par une table (sans l'index)
postgres=# SELECT pg_siz­e_p­ret­ty(­pg_­rel­ati­on_­siz­e('­gro­sse­_ta­ble'));
Trouver la table la plus volumi­neuse (variante 1)
postgres=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
Trouver la table la plus volumi­neuse (variante 2)
postgres=# SELECT nspname || '.' || relname AS tablename, pg_siz­e_p­ret­ty(­pg_­tab­le_­siz­e((­nspname || '.' || relnam­e):­:re­gcl­ass)) AS size FROM pg_class c JOIN pg_nam­espace n ON (c.rel­nam­espace = n.oid) WHERE relkind = 'r' ORDER BY pg_tab­le_­siz­e((­nspname || '.' || relnam­e):­:re­gclass) DESC LIMIT 10;

Transa­ctions

Démarrer une transa­ction
postgres=# BEGIN
Faire un rollback
postgres=# ROLLBACK
Faire un commit (fin de transa­ction)
postgres=# COMMIT

SQL

Compter les lignes d’une table
postgres=# select count(*) from table;
Générer une série de nombres et l’insérer dans une table
postgres=# INSERT INTO nombres (num) VALUES ( genera­te_­ser­ies­(1,­100));
Récupérer la seconde valeur la plus petite d’une colonne
postgres=# SELECT MIN(num) from number­_table WHERE num > ( SELECT MIN(num) FROM number­_table );
Récupérer la seconde valeur la plus grande d’une colonne
postgres=# SELECT MAX(num) FROM number­_table WHERE num < ( SELECT MAX(num) FROM number­_table );
Crypter, puis enregi­strer un mot de passe
postgres=# SELECT crypt ( 'midori', gen_sa­lt(­'md5') );
                                           
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet

          More Cheat Sheets by TME520

          Wardriving Quick Reference Cheat Sheet