Show Menu

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.
sql     index     postgresql     transactions     espace     disque     sauvegardes     utilisateurs     bases     investiguer     analyser     utile

Bases

Afficher la version du serveur Postgr­eSQL
postgres=# SELECT version();
Afficher la version du client Postgr­eSQL
psql -V
Vérifier si Postgres tourne
/etc/i­nit.d/­pos­tgresql status
Démarrer / arrêter / redémarrer Postgres (SysVi­nit)
/etc/i­nit.d/­pos­tgresql start | stop | restart
Démarrer / arrêter / redémarrer Postgres (Syste­md)
systemctl start | stop | restart postgr­esq­l.s­ervice
Saisir nos requêtes dans notre éditeur de prédil­ect­ion
postgres=# \e
Changer le mot de passe root de Postgr­eSQL
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
Supp­rimer une base depuis Postgres
postgres=# DROP DATABASE maDB;
Supp­rimer 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 Postgr­eSQL
postgres=# SELECT typnam­e,t­yplen from pg_type where typtyp­e='b';
Redi­riger 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

Sauv­egarder une seule base
pg_dump -U utilis­ateur nom_DB -f madb.sql
Sauv­egarder toutes les bases
pg_dumpall > tout.sql
Sauv­egarder les objets globaux (users + tables­pac­es)
pg_dumpall -g > tout.sql
Sauv­egarder une table
pg_dump --table articles -U midori nom_DB -f unetab­le.sql
Rest­aurer une base
psql -U midori -d nom_DB -f madb.sql
Rest­aurer toutes les bases
psql -f tout.sql
Rest­aurer une table
psql -f unetab­le.sql nom_DB
Sauv­egarder 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

Cons­ulter le fichier d’hist­ori­que
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_­his­tory, PostgreSQL conserve une liste des commandes exécutées dans le fichier $HOME­/.p­sql­_hi­story.

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;
Supp­rimer un utilis­ateur depuis le Shell
/usr/b­in/­dro­puser midori
Supp­rimer 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;
Supp­rimer 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écu­pé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écu­pé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') );

Download the PostgreSQL Cheat Sheet

3 Pages
//media.cheatography.com/storage/thumb/tme520_postgresql.750.jpg

PDF (recommended)

Alternative Downloads

Share This Cheat Sheet!

Like this cheat sheet? Check out our sponsors!

Readable.io is a collection of tools to make your writing better. More readable content means higher conversion rates and better reader engagement. Measure website and document readability, measure keyword density and more!

Click Here To Get Started!

 

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

          cron Cheat Sheet
          systemd Cheat Sheet