Par comparaison :

A faire sur le master :
SELECT pg_current_xlog_location();

A faire sur l'esclave
SELECT pg_last_xlog_receive_location();

Par le lag de la réplication :

A faire sur l'esclave
-> détermination par taille:
SELECT
CASE
WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER
END
AS replication_lag;

-> détermination par temps
select now() - pg_last_xact_replay_timestamp() AS replication_delay;


A faire sur le maître :
select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;

 

 

 

Quelques requêtes utiles sur les lignes mortes :

 

  • Tables avec des lignes mortes dans la base :
select relname,n_dead_tup,n_live_tup from pg_stat_all_tables order by n_dead_tup desc;
  • 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';

 

Les tables bloquées :

  • 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;

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;