PostgreSQL : aide-mémoire psql
Aide-mémoire de psql : connexion, méta-commandes, rôles, journaux, configuration et requêtes d'administration courantes.
psql est le client interactif de PostgreSQL. Cette page regroupe les commandes les plus utilisées.
Se connecter
psql -U postgres
psql -U <utilisateur> -h localhost -d postgres
sudo -u postgres psql
Si la connexion échoue avec une erreur d'authentification, vérifier le fichier d'authentification pg_hba.conf (voir plus bas).
Options utiles (psql --help affiche la liste complète) :
-E: affiche les requêtes SQL derrière les commandes à barre oblique inverse (utile pour apprendre).-l: liste les bases de données puis quitte. Utile quand l'utilisateur n'a pas de base par défaut, comme sur les services managés tels qu'AWS RDS.
Méta-commandes
| Commande | Action |
|---|---|
\q |
Quitter |
\c <base> ou \connect <base> |
Se connecter à une base |
\l |
Lister les bases |
\dn |
Lister les schémas |
\dt *.* |
Lister les tables de tous les schémas (sans *.*, seulement ceux du search_path) |
\dv |
Lister les vues |
\di |
Lister les index |
\df |
Lister les fonctions |
\df+ <fonction> |
Afficher le code SQL d'une fonction |
\dy |
Lister les déclencheurs d'événements (event triggers) |
\d <table> |
Afficher la définition d'une table, déclencheurs compris |
\du |
Lister les rôles (utilisateurs) |
\du <rôle> |
Afficher un rôle |
\x |
Basculer l'affichage étendu, plus lisible que les tableaux ASCII |
\copy (SELECT * FROM <table>) TO 'fichier.csv' WITH CSV |
Exporter le résultat d'une requête en CSV |
La plupart des commandes \d acceptent un argument schéma.nom et des jokers comme *.*.
Rôles et utilisateurs
Lister les rôles avec leurs attributs :
SELECT u.usename AS "Role name",
CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
CREATE ROLE role_test1; -- rôle sans connexion
CREATE ROLE role_test2 NOINHERIT LOGIN PASSWORD '<mot_de_passe>';
SET ROLE role_test2; -- change de rôle pour la session courante
GRANT role_test2 TO role_test1; -- role_test1 peut prendre le rôle role_test2
Configuration et journaux
Trouver les fichiers de configuration :
SHOW config_file;
SHOW hba_file;
Les fichiers principaux sont postgresql.conf (paramètres du serveur) et pg_hba.conf (qui peut se connecter, et comment).
Gestion du service sur Debian ou Ubuntu :
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
Pour augmenter la verbosité des journaux, modifier postgresql.conf, redémarrer le service, puis lire le journal :
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1
log_line_prefix = '%t %u %d %a '
sudo service postgresql restart
tail -f /var/log/postgresql/postgresql-<version>-main.log
log_line_prefix ajoute à chaque ligne l'heure (%t), l'utilisateur (%u), la base (%d) et le nom de l'application (%a). debug5 journalise chaque instruction, erreur et tâche de fond, y compris les VACUUM : remettre un niveau normal ensuite.
Requêtes utiles
Sessions et activité :
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
FROM pg_stat_activity
WHERE datname = '<base>';
Sessions en attente d'un verrou ou de données (possiblement bloquées). La colonne waiting a été supprimée dans PostgreSQL 9.6 ; sur les versions récentes, utiliser wait_event_type :
SELECT * FROM pg_stat_activity WHERE waiting = 't'; -- PostgreSQL 9.5 et antérieur
SELECT * FROM pg_stat_activity WHERE wait_event_type IS NOT NULL; -- PostgreSQL 9.6 et ultérieur
Objets et tailles :
SELECT * FROM pg_proc WHERE proname = '<fonction>'; -- fonctions
SELECT * FROM pg_views WHERE viewname = '<vue>'; -- vues, avec leur définition
SELECT * FROM pg_indexes WHERE tablename = '<table>' AND schemaname = '<schéma>';
SELECT pg_size_pretty(pg_total_relation_size('<table>')); -- taille d'une table, index compris
SELECT pg_size_pretty(pg_database_size('<base>')); -- taille d'une base
SHOW statement_timeout; -- délai d'expiration de la session courante
Tous les index de toutes les tables d'un schéma :
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a,
pg_namespace n
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY(ix.indkey)
AND t.relnamespace = n.oid
AND n.nspname = '<schéma>'
ORDER BY
t.relname,
i.relname;
Emplacement des données et conversions :
SHOW data_directory; -- emplacement des fichiers de la base
SELECT * FROM pg_tablespace; -- tablespaces
SELECT CAST(<colonne> AS <type>); -- ou <colonne>::<type>
SELECT '<table>'::regclass::oid; -- OID d'une table
Analyse de requête :
EXPLAIN <requête>; -- plan d'exécution seul
EXPLAIN ANALYZE <requête>; -- exécute la requête et affiche les durées réelles
ANALYZE <table>; -- collecte les statistiques du planificateur
Remarques
- La note d'origine listait aussi une requête sur
pg_stat_get_backend_pidetpg_stat_get_backend_activity. Elle a été retirée :pg_stat_activitydonne la même information sous une forme lisible. - Les commandes qui créent un rôle avec mot de passe doivent recevoir un vrai mot de passe. Ne pas le stocker dans un script partagé.
- Les identifiants, noms d'hôte et nom de schéma de la note d'origine ont été remplacés par des valeurs génériques.