quick queries to transfer

quick_queries.sql

select count(state) as count, state 
FROM (SELECT CASE 
  WHEN state='idle' THEN 'idle'
  WHEN state='idle in transaction' THEN 'idle_in_txn'
  WHEN state='active' THEN 'active'
    ELSE 'unknown' END AS state
    FROM pg_stat_activity) state
    GROUP BY state
UNION
  SELECT COUNT(*) as count, 'waiting' as state
  from pg_stat_activity WHERE waiting;


-- slow queries
select count(0) from pg_stat_activity
where state='active' and now()-query_start > '300 seconds'::interval
AND query ~*'^(insert|update|delete|select)';


-- txn_wraparound
select age(datfrozenxid) as txn_wrap_age from pg_database;


-- replica lag
select case when pg_is_in_recovery='false' then 0
  else coalesce(round(extract(epoch from now() - pg_last_xact_replay_timestamp())),0) end 
  as seconds
from pg_is_in_recovery();

-- checkpoints
select (checkpoints_timed + checkpoints_req) as total_checkpoints
from pg_stat_bgwriter;

-- wal files
select archived_count as count, failed_count as failed 
from pg_stat_archiver;