Quelques requêtes utiles :

  • Liste des bases de données :
\l
  • Liste des bases de données avec leur taille :
\l+
  • Liste des tables :
\dt
  • Liste des tables détaillées (taille, description) :
\dt+
  • Description d'une table :
\d <table>
  • Description détaillée d'une table (type de stockage, description) :
\d+ <table>
  • Liste des extensions :
\dx
  • Liste des objets dans l'extension :
\dx+ <extension>
  • Liste des variables de configuration de postgres :
show all;
  • Liste de toutes les fonctions:
SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

autre possibilité :

\df <nom_schema>.*

ou

\dfS <nom_schema>.*

ou

\dfS+ <nom_schema>.*

pour les fonction interne de postgres 

\df pg_* 
  • Locking , waiting :
  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;
  • Locking , waiting avec une idée sur la duré de l'état (avant 9.2):
  SELECT a.datname,
         c.relname,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.current_query, 
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.procpid 
    FROM  pg_stat_activity a
     JOIN pg_locks         l ON l.pid = a.procpid
     JOIN pg_class         c ON c.oid = l.relation
    ORDER BY a.query_start;
  • Locking , waiting avec une idée sur la duré de l'état (à partir de 9.2):
  SELECT a.datname,
         c.relname,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query, 
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.pid 
    FROM  pg_stat_activity a
     JOIN pg_locks         l ON l.pid = a.pid
     JOIN pg_class         c ON c.oid = l.relation
    ORDER BY a.query_start;

Le but est de récupérer le résultat d'une requête dans un fichier dans un format particulier :

Pré-requis : se connecter avec un compte superuser, et aller dans la base concernée.

  •  Format csv avec entête des colonnes :

requête :

copy (<ecrire_ici_la_requête>) to '</directory/du/fichier/résultat.csv>' with header csv ;

ex : copy (select * from alf_content_url ) to '/tmp/test.csv' with header csv ;

  • Format csv sans entête des colonnes :

requête :

copy (<ecrire_ici_la_requête>) to '</directory/du/fichier/résultat.csv>' with csv ;

ex : copy (select * from alf_content_url ) to '/tmp/test.csv' with csv ;

  •  Format texte avec entête des colonnes et séparateur de notre choix :

requête :

copy (<ecrire_ici_la_requête>) to '</directory/du/fichier/résultat.txt>' with header delimiter '<symbole>' csv ;

ex : copy (select * from alf_content_url ) to '/tmp/test.txt' with header delimiter '|' csv ;

Pour tuer une requête il y a deux possibilité :

  1. par une commande shell
  2. par une commande sql

 

  • Par la commande shell kill :
    • kill <pid_process>

 /!\ NE JAMAIS FAIRE un kill -9 /!\

  • Par une commande sql :
    • commancer par la commande : select pg_cancel_backend(<pid_process>)
    • si cela n'est pas suffisant passer la commande : select pg_terminate_backend(<pid_process>)

Pour loguer uniquement dans le fichier de log la durée d'une requêtes qui dure plus de x millisecondes , il faut modifier le fichier de configuration postgresql.conf.

Chercher et modifier les valeurs des variables dans postgresql.conf :

  • log_statement = 'none'
  • log_duration = off
  • log_min_duration_statement = <valeur_en_millisecondes>

Exemple pour toutes requêtes supérieures à 4 secondes mettre :

 

log_statement = 'none'

log_duration = off

 

log_min_duration_statement = 4000

Pour une meilleur lisibilité, vou pouvez paramétrer la variable : log_line_prefix

exemples :

  1. log_line_prefix = '%m %d '
  2. log_line_prefix = '%t %d '

Explication des valeurs :

  •  %d : nom de la base de données ,
  • %m : date allant jusqu'à la miliseconde ,
  • %t ; date sans les millisecondes