Pour faire le tilde : ~, il faut associer les touches : 'alt' + 'n'

Pour faire l'accolade ouverte : {, il faut associer les touches : 'alt' + '('

Pour faire l'accolade fermée : }, il faut associer les touches : 'alt' + ')'

Pour bénéficier des dernière mise à jour, il y a un repositorie dédié fait et maintenu par les personnes de PosgreSQL.

Deux possibilité :

  • Ajouter dans /etc/apt/sources.list :

deb http://apt.postgresql.org/pub/repos/apt/<VOTRE_VERION_DEBIAN>-pgdg main

  • Créer un fichier <nom_fichier>.list dans /etc/apt/sources.list.d/ contenant :

deb http://apt.postgresql.org/pub/repos/apt/<VOTRE_VERION_DEBIAN>-pgdg main

Clé de signature des paquests

Si une erreur signalant un problème de signature de paquet apparaît lors de la commande apt-get update , tapper la commande :

wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add - ; rm -f /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg

ou

sudo wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add - ; rm -f /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg

 

 

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;