LLI Wiki — My Digital Knowledge Base

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) :

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

Mastodon LLIMastodon SocialFosstodon