On peut utiliser openssl pour lire un certificat ssl :

commande :

openssl x509 -text -noout -in <nom_fichier>

 

Voir le 'gonflement des tables , la requête provient de la sonde nagios / icinga  check_postgres de bucardo.org.

 

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
      (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+COUNT(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::NUMERIC) AS bs,
          CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

 

Tables en auto-vacuum

SELECT pid, query FROM pg_stat_activity WHERE query LIKE 'autovacuum: %';

 

Calcul du seuil de déclenchement du vacuum :

vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuples

 

postgres 9.6 :

La vue pg_stat_progress_vacuum peut aider :

                       View "pg_catalog.pg_stat_progress_vacuum"
       Column       |  Type   | Collation | Nullable | Default | Storage  | Description 
--------------------+---------+-----------+----------+---------+----------+-------------
 pid                | integer |           |          |         | plain    | 
 datid              | oid     |           |          |         | plain    | 
 datname            | name    |           |          |         | plain    | 
 relid              | oid     |           |          |         | plain    | 
 phase              | text    |           |          |         | extended | 
 heap_blks_total    | bigint  |           |          |         | plain    | 
 heap_blks_scanned  | bigint  |           |          |         | plain    | 
 heap_blks_vacuumed | bigint  |           |          |         | plain    | 
 index_vacuum_count | bigint  |           |          |         | plain    | 
 max_dead_tuples    | bigint  |           |          |         | plain    | 
 num_dead_tuples    | bigint  |           |          |         | plain    | 
View definition:
 SELECT s.pid,
    s.datid,
    d.datname,
    s.relid,
        CASE s.param1
            WHEN 0 THEN 'initializing'::text
            WHEN 1 THEN 'scanning heap'::text
            WHEN 2 THEN 'vacuuming indexes'::text
            WHEN 3 THEN 'vacuuming heap'::text
            WHEN 4 THEN 'cleaning up indexes'::text
            WHEN 5 THEN 'truncating heap'::text
            WHEN 6 THEN 'performing final cleanup'::text
            ELSE NULL::text
        END AS phase,
    s.param2 AS heap_blks_total,
    s.param3 AS heap_blks_scanned,
    s.param4 AS heap_blks_vacuumed,
    s.param5 AS index_vacuum_count,
    s.param6 AS max_dead_tuples,
    s.param7 AS num_dead_tuples
   FROM pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10)
     JOIN pg_database d ON s.datid = d.oid;

 

Date du dernier vacuum, auto-vacuum, analyse, auto-analyse :

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze  
FROM pg_stat_all_tables;

 

Durée de l'autovacuum

SELECT datname, usename, pid, current_timestamp - xact_start AS xact_runtime, state, query
FROM pg_stat_activity 
WHERE query LIKE '%autovacuum%' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY xact_start;

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