summary.sh

application/x-sh

Download

#!/usr/bin/env bash
set -euo pipefail

psql <<EOF
\x
\\echo Total Activity
SELECT
    SUM(c.n_dead_tup) as total_n_dead_tup,
    SUM(c.n_mod_since_analyze) as total_n_mod_since_analyze,
    SUM(b.reltuples::numeric) as total_reltuples,
    SUM(c.autovacuum_count) as total_autovacuum_count,
    SUM(c.autoanalyze_count) as total_autoanalyze_count,
    SUM(c.n_tup_upd) as total_n_tup_upd,
    SUM(c.n_tup_ins) as total_n_tup_ins,
    COUNT(*) as table_count
FROM pg_class b,
     pg_stat_all_tables c
WHERE b.oid = c.relid
  AND (UPPER(b.relname) LIKE '%TABL%'
  AND b.relname NOT LIKE 'pg_%');

\\echo Activity By Workload Type
SELECT
    CASE
        WHEN b.relname LIKE 'table_batch%' THEN 'batch_tables'
        WHEN b.relname ~ '^table_[0-9]+$' THEN 'numbered_tables'
    END as table_group,
    ROUND(AVG(c.autovacuum_count), 3) as "**      avg_autovacuum_count",
    ROUND(AVG(c.autoanalyze_count), 3) as "**      avg_autoanalyze_count",
    ROUND(AVG(c.vacuum_count), 3) as avg_vacuum_count,
    ROUND(AVG(c.analyze_count), 3) as avg_analyze_count,
    ROUND(SUM(n_tup_ins), 0) rows_inserted,
    ROUND(SUM(n_tup_upd), 0) rows_updated,
    ROUND(SUM(n_tup_hot_upd), 0) rows_hot_updated,
    COUNT(*) as table_count
FROM pg_class b,
     pg_stat_all_tables c
WHERE b.oid = c.relid
  AND (UPPER(b.relname) LIKE '%TABL%'
  AND b.relname NOT LIKE 'pg_%')
  AND (b.relname LIKE 'table_batch%' OR b.relname ~ '^table_[0-9]+$')
GROUP BY table_group;

\x
SELECT query FROM pg_stat_activity WHERE query LIKE '%autov%' and pid <> pg_backend_pid();

EOF