Avant d'installer un certificat il est préférable de vérifier que la clé privé corresponde bien au certificat.

Pour ce faire il faut vérifier  la 'signature du csr, de la clé et du certificat  via la commande openssl :

  • signature du certificat :

openssl x509 -noout -modulus -in <fichier_crt.crt> | openssl <xxx>

  • signature de la clé :

openssl rsa -noout -modulus -in <fichier_cle.key> | openssl <xxx>

  • signature du csr :

openssl req -noout -modulus -in <fichier_csr.csr> | openssl <xxx>

<xxx> peut être : md2 , md4, md5 , sha, sha1, base64

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;