Requêtes pour connaitre les tailles  :

  • Taille des tables et index (global à une table) avec ratio :
SELECT
    e1.relname AS table,
    pg_size_pretty(pg_relation_size(e1.oid)) AS "table size",
    CASE
        WHEN e3.oid IS NULL THEN 'O bytes'
        WHEN e3.oid IS NOT NULL THEN pg_size_pretty(pg_total_relation_size(e3.oid))
    END AS "TOAST size",
    pg_size_pretty(sum(pg_relation_size(e2.oid))) AS "Index size",
    pg_size_pretty(pg_total_relation_size(e1.oid)) AS "total table size",
    round(sum(pg_relation_size(e2.oid))*100/pg_total_relation_size(e1.oid)) AS "ratio"
FROM pg_class e1
    JOIN pg_index i ON e1.oid=i.indrelid
    JOIN pg_class e2 ON i.indexrelid=e2.oid
    LEFT JOIN pg_class e3 ON e1.reltoastrelid=e3.oid
WHERE
    (e1.relkind='r' OR e1.relkind='t')
    AND pg_relation_size(e1.oid) != 0
GROUP BY e1.relname, e1.oid, e3.oid
ORDER BY sum(pg_relation_size(e2.oid)) DESC;
  • Taille des tables avec taille des index (index par index) :
SELECT
    e1.relname AS table,
    e2.relname AS "index name",
    pg_size_pretty(pg_relation_size(e1.oid)) AS "table size",
    CASE
        WHEN e3.oid IS NULL THEN '0 bytes'
        WHEN e3.oid IS NOT NULL THEN pg_size_pretty(pg_relation_size(e3.oid))
    END AS "TOAST size",
    CASE
        WHEN e2.oid IS NULL THEN '0 bytes'
        WHEN e2.oid IS NOT NULL THEN pg_size_pretty(pg_relation_size(e2.oid))
    END AS "Index size",
    pg_size_pretty(pg_total_relation_size(e1.oid)) AS "total table size"
FROM pg_class e1
    JOIN pg_index i ON e1.oid=i.indrelid
    JOIN pg_class e2 ON i.indexrelid=e2.oid
    LEFT JOIN pg_class e3 ON e1.reltoastrelid=e3.oid
WHERE
    (e1.relkind='r' OR e1.relkind='t')
    AND pg_relation_size(e1.oid) != 0
ORDER BY pg_total_relation_size(e1.oid) DESC;
  • Les 5 plus grosses tables :
SELECT nspname || '.' || relname AS "relation",pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 5;

  • Les 50 plus grosses tables triée de la plus grosse à la moins grosse :

 

select 
   schema,name, pg_size_pretty(CASE WHEN is_index THEN 0 ELSE s END) AS size,
   pg_size_pretty(CASE WHEN is_index THEN s ELSE st - s END) AS index, CASE WHEN st = 0   THEN 0
     WHEN is_index THEN 100 ELSE 100 - ((s*100) / st) END || '%' as ratio, pg_size_pretty(st) as total 
 FROM (SELECT *,st = s AS is_index FROM (SELECT nspname as schema, relname as name, pg_relation_size(nspname || '.' || relname) as s, pg_total_relation_size(nspname || '.' || relname) as st 
 FROM pg_class JOIN pg_namespace ON (relnamespace = pg_namespace.oid)) AS p) AS pp 
ORDER BY st DESC LIMIT 50;
  • Taille des lignes mortes pour chaque table (postgres superieur à 9) :
CREATE EXTENSION pgstattuple;
SELECT relname as table_name, pg_size_pretty(dead_tuple_len) AS dead_tuple_size
FROM
    (SELECT relname, (pgstattuple(oid)).dead_tuple_len AS dead_tuple_len FROM pg_class WHERE relkind='r')
    AS stattuple
ORDER BY dead_tuple_len DESC;
  • Taille des lignes mortes pour la base (postgres superieur à 9) :
SELECT 
   pg_size_pretty(sum((pgstattuple(oid)).dead_tuple_len)) AS total_dead_tuple_len 
FROM pg_class WHERE relkind='r';

  • Taille des tables de TOAST :
SELECT a, n, pg_relation_size(t), pg_size_pretty(pg_relation_size(t))
FROM   (SELECT c.relname, c.reltoastrelid, d.relname
        FROM   pg_class c JOIN pg_class d ON c.reltoastrelid = d.oid
       ) AS x(a, t, n)
WHERE  t > 0 AND pg_relation_size(t) > 0
ORDER BY 3 DESC;

 

Il est possible de donner des permissions spécifiques sur une colonne d'une table :

GRANT <droit> (<colonne>) on <nom_table> to <user> ;

Pour forcer un umask particulier à un utilisateur donné on peut le faire dans /etc/password via la commande :

chfn -o 'umask=<valeur_umask>' <user>

Pour le mot de passe d'un utilisateur on peut le faire via la commande :

usermod -p '*' <user>

ou la commande :

chage -I -1 -m 0 -M 99999 -E -1 <user>

Pour rappel cela ne mets pas un mot de passe vide, il supprime purement et simplement le mot de passe, ce qui a comme résultat l'impossibilité de se connecter avec un mot de passe.