PostgreSQL 11/12 monitoring

postgresql_monit.sql

-- logs
SET log_statement = 'all';

-- list database(s)
\l+ [database]

-- list schema(s)
\dn

-- list tables in all schemas
\dt *.*

-- current processes
SELECT * FROM pg_stat_activity;

-- all databases sizes (with indexes)
SELECT t1.datname AS db_name, pg_size_pretty(pg_database_size(t1.datname)) AS db_size
FROM pg_database t1 ORDER BY pg_database_size(t1.datname) DESC;

-- database size
SELECT pg_size_pretty(pg_database_size('database'));

-- database schemas size
SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name;

-- database relations size
SELECT table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
FROM information_schema.tables
ORDER BY 3 DESC;
--OR
SELECT 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 10;
--OR https://dba.stackexchange.com/a/96540/23608
SELECT relname AS objectname
, relkind AS objecttype
, reltuples AS entries
, pg_size_pretty(pg_table_size(oid)) AS size
FROM pg_class
WHERE relkind IN ('r', 'i', 'm')
ORDER BY pg_table_size(oid) DESC;

--WITH INDEXES
SELECT relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 5;
                                          
-- unused indexes
SELECT relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes WHERE schemaname = 'public'
AND idx_scan = 0 AND idx_tup_read = 0 AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) DESC;
  
-- schema relations size
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2 DESC;

-- database relations size
-- list all tables from all schemas and their sizes and index sizes. If a table is just an index table, it will show up as 100% index
-- https://stackoverflow.com/a/10006634/644386
with p as (
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)
),
pp as (
SELECT *,
        case when st = s then 0 else s end as size,
        case when st = s then s else st-s end as index
   FROM p
)
SELECT schema,
       name,
       pg_size_pretty(size) as size,        
       pg_size_pretty(index) as index,
       (case st 
             when 0 then 0 
             else index*100 / st 
        end) || '%' ratio,
       st total
FROM pp
ORDER BY st DESC LIMIT 30;
 
-- Index Maintenance - PostgreSQL wiki
-- https://wiki.postgresql.org/wiki/Index_Maintenance

-- List database indexes with sizes
-- @see https://stackoverflow.com/questions/2596624/how-do-you-find-the-disk-size-of-a-postgres-postgresql-table-and-its-indexes
SELECT
    t.tablename,
    indexname,
    c.reltuples AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN indisunique THEN 'Y'
       ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
           JOIN pg_class c ON c.oid = x.indrelid
           JOIN pg_class ipg ON ipg.oid = x.indexrelid
           JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
WHERE t.schemaname='public'
ORDER BY 1,2;

-- List database indexes (superuser)
-- @see https://stackoverflow.com/a/44460269/644386
SELECT
  tnsp.nspname AS schema_name,
  trel.relname AS table_name,
  irel.relname AS index_name,
  array_agg (
     a.attname 
  || ' ' || CASE o.option & 1 WHEN 1 THEN 'DESC' ELSE 'ASC' END
  || ' ' || CASE o.option & 2 WHEN 2 THEN 'NULLS FIRST' ELSE 'NULLS LAST' END
    ORDER BY c.ordinality
  ) AS columns
FROM pg_index AS i
JOIN pg_class AS trel ON trel.oid = i.indrelid
JOIN pg_namespace AS tnsp ON trel.relnamespace = tnsp.oid
JOIN pg_class AS irel ON irel.oid = i.indexrelid
CROSS JOIN LATERAL unnest (i.indkey) WITH ORDINALITY AS c (colnum, ordinality)
LEFT JOIN LATERAL unnest (i.indoption) WITH ORDINALITY AS o (option, ordinality)
  ON c.ordinality = o.ordinality
JOIN pg_attribute AS a ON trel.oid = a.attrelid AND a.attnum = c.colnum
GROUP BY tnsp.nspname, trel.relname, irel.relname;

-- List database indexes excluding system tables
-- @see https://stackoverflow.com/a/27383452/644386
SELECT
  U.usename                AS user_name,
  ns.nspname               AS schema_name,
  idx.indrelid :: REGCLASS AS table_name,
  i.relname                AS index_name,
  idx.indisunique          AS is_unique,
  idx.indisprimary         AS is_primary,
  am.amname                AS index_type,
  idx.indkey,
       ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS index_keys,
  (idx.indexprs IS NOT NULL) OR (idx.indkey::int[] @> array[0]) AS is_functional,
  idx.indpred IS NOT NULL AS is_partial
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
  JOIN pg_user AS U ON i.relowner = U.usesysid
WHERE NOT nspname LIKE 'pg%';

 -- Disk Usage - PostgreSQL wiki
 -- https://wiki.postgresql.org/wiki/Disk_Usage

-- functions definitions
SELECT p.proname FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid
WHERE n.nspname = 'public' ORDER BY p.proname;

-- functions definitions via pg_get_functiondef
SELECT pg_get_functiondef(p.oid)
FROM pg_catalog.pg_proc p
INNER JOIN pg_catalog.pg_namespace n ON (p.pronamespace = n.oid)
WHERE n.nspname = 'public' ORDER BY p.proname;
                                                            
-- pg_stat_activity: slow queries
SELECT pid, now() - query_start as "runtime", usename, datname, wait_event, state, query
FROM  pg_stat_activity
WHERE now() - query_start > '1 minutes'::interval and state = 'active'
ORDER BY runtime DESC;

-- list locks with pg_locks
SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
FROM pg_locks l, pg_stat_all_tables t
WHERE l.relation = t.relid ORDER BY relation ASC;

-- list locks with pg_blocking_pids() 9.6+, @see https://stackoverflow.com/a/43363536/644386
SELECT pid, usename, pg_blocking_pids(pid) as blocked_by, query as blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;