반응형
/****************************************************************************************************************
-- Title : [PGS9.2] DB Monitoring View for PostgreSQL
-- Reference : dbrang.tistory.com
-- Key word : 관리 스크립트 관리스크립트 management script 모니터링 뷰 monitoring view postgresql
****************************************************************************************************************/

---------------------------------------------
-- 테이블 스페이스 정보
-- select * from sp_tablespace0;
---------------------------------------------
drop view public.sp_tablespace0;

create or replace view public.sp_tablespace0
as
    select aa.tbs_ownr, aa.tbs_id, aa.tbs_nm, aa.dflt_tbs
         , case when aa.tbs_size ~* 'kb' then cast(cast(replace(lower(aa.tbs_size), ' kb', '') as bigint) / 1000. 
                      as numeric(15,2))
          else cast(cast(replace(lower(aa.tbs_size), ' mb', '') as bigint) as numeric(15,2))
           end "tbs_size_mb"
         , aa.tbs_location
    from
    (
        select pg_get_userbyid(t.spcowner) "tbs_ownr", t.oid "tbs_id", t.spcname "tbs_nm"
       , pg_size_pretty(pg_tablespace_size(t.oid)) "tbs_size"
       , case when pg_tablespace_location(t.oid) = '' 
        then u.tbs_location
        else pg_tablespace_location(t.oid)
         end "tbs_location"
       , case when t.spcname in (select distinct b.spcname "dflt_tbs_nm"
               from pg_database a
               inner join pg_tablespace b
               on a.dattablespace = b.oid
              )
        then 'default'
        else ''
         end "dflt_tbs"
        from pg_tablespace t
        left join (select a.setting || '/' || case when b.tbs_nm = 'default' 
                     then 'base' else b.tbs_nm 
                end "tbs_location"
            , 'pg_' || b.tbs_nm "tbs_nm"
             from pg_settings a
             cross join (select 'default' "tbs_nm" union all
             select 'global'
            ) b
             where name = 'data_directory'
            ) u
        on t.spcname = u.tbs_nm
    ) aa
    order by tbs_ownr, tbs_nm;
  
  
---------------------------------------------
-- 테이블 정보
-- select * from sp_table0;
---------------------------------------------
drop view public.sp_table0;

create or replace view public.sp_table0
as
    select n.nspname "sch_nm" 
         , c.relname "rel_nm" 
         , case when c.relkind in ('r','v','i') and c.reltablespace = 0
                then u.bas_tbs_nm
                when c.relkind in ('r','v','i') and c.reltablespace <> 0
                then t.spcname
                else null
           end "tbs_nm"
         , 'table space' "tbs_tp"
         , NULL "lob_tp"
         , ( 
                SELECT n_tup_ins - n_tup_del 
                FROM pg_stat_all_tables
                where relname = c.relname
                AND schemaname = n.nspname
           ) "row_cnt" 
         , CASE WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'es'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' bytes','') :: int  / 1024
                WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'kb'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' kb','') :: int 
                WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'mb'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' mb','') :: int  * 1024
                WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'gb'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' gb','') :: int  * 1024 * 1024
                ELSE 999999
           END "rsvt_kb"
         , CASE WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'es'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' bytes','') :: int  / 1024
                WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'kb'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' kb','') :: int 
                WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'mb'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' mb','') :: int  * 1024
                WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'gb'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' gb','') :: int  * 1024 * 1024
                ELSE 999999
           END "data_kb"
         , CASE WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'es'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' bytes','') :: int  / 1024
                WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'kb'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' kb','') :: int 
                WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'mb'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' mb','') :: int  * 1024
                WHEN RIGHT(pg_size_pretty(pg_table_size(c.oid)),2) = 'gb'
                THEN REPLACE(pg_size_pretty(pg_table_size(c.oid)),' gb','') :: int  * 1024 * 1024
                ELSE 999999
           END
         - CASE WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'es'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' bytes','') :: int  / 1024
                WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'kb'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' kb','') :: int 
                WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'mb'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' mb','') :: int  * 1024
                WHEN RIGHT(pg_size_pretty(pg_relation_size(c.oid)),2) = 'gb'
                THEN REPLACE(pg_size_pretty(pg_relation_size(c.oid)),' gb','') :: int  * 1024 * 1024
                ELSE 999999
           END "odx_kb"
         , NULL "nuse_kb"
    from pg_class c
    left join pg_namespace n
    on c.relnamespace = n.oid
    left join pg_tablespace t
    on c.reltablespace = t.oid
    cross join ( 
                    select a.oid "bas_tbs_id", a.spcname "bas_tbs_nm"
                    from pg_tablespace a
                    inner join
                    (  
                        select dattablespace
                        from pg_database
                        where datname = current_database()
                    ) b
                    on a.oid = b.dattablespace
               ) as u  /* basis tablespace join */
    where n.nspname <> 'pg_catalog'
    and n.nspname <> 'information_schema'
    and n.nspname !~ '^pg_toast'
    and c.relkind = 'r';


---------------------------------------------
-- 컬럼 정보
-- select * from sp_column0;
---------------------------------------------
drop view public.sp_column0;

create or replace view public.sp_column0
as
    SELECT c.table_schema || '.' || c.table_name "TABLE"
         , c.column_name "COLUMN"
         , case when c.data_type = 'character' then 'CHAR(' || c.character_maximum_length || ')'
                when c.data_type = 'character varying' then 'VARCHAR(' || c.character_maximum_length || ')'
                when c.data_type = 'numeric' then 'NUMERIC(' || c.numeric_precision || case when c.numeric_scale = 0 then ')'
                                                                                            else ',' || c.numeric_scale || ')'
                                                                                       end
                else upper(c.data_type)
           end "COLUMN_TYPE"
         , coalesce(c.character_octet_length, c.numeric_precision/8) "LENGTH_BT"
         , case when c.is_nullable = 'YES' then 1 else 0 end "IS_NULL"
         , case when u.PK_nm is not null then 1 else 0 end "IS_PK"
         , case when u.FK_nm is not null then 1 else 0 end "IS_FK"
         , case when c.column_default like '%nextval%' then 1 else 0 end "IS_IDENT"
         , case when c.column_default not like '%nextval%' and c.column_default is not null
                then c.column_default
                else null
           end "DFLT"
         , NULL "COMP"
         , c.ordinal_position "COL_ORD"
    FROM 
    (
        select table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, data_type
             , character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable
        from information_schema.columns
        where (table_schema, table_name) in (select table_schema, table_name from information_schema.tables
                                             where table_type = 'BASE TABLE'
                                            )
    ) c
    left join 
    (
        select a.table_schema, a.table_name, a.column_name
             , max(case when constraint_type = 'PRIMARY KEY' then constraint_name end) as PK_nm
             , max(case when constraint_type = 'FOREIGN KEY' then constraint_name end) as FK_nm
        from 
        (
            select tc.table_catalog, tc.table_schema, tc.table_name "table_name", tc.constraint_name, kc.column_name, tc.constraint_type
            from information_schema.table_constraints tc
            inner join information_schema.key_column_usage kc
            on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
            and tc.constraint_name = kc.constraint_name
            where tc.constraint_type in ('PRIMARY KEY', 'FOREIGN KEY')
        ) a
        group by a.table_schema, a.table_name, a.column_name
    ) u
    on c.table_schema = u.table_schema
    and c.table_name = u.table_name
    and c.column_name = u.column_name
    where c.table_schema not in ('pg_catalog', 'information_schema')
    order by c.table_schema, c.table_name, c.ordinal_position;


---------------------------------------------
-- 서버  정보
-- select * from sp_server0;
---------------------------------------------
drop view public.sp_server0;

create or replace view public.sp_server0
as
    select 'version' "name", version "setting"
    from version() union all
    select 'current_dbname', current_database
    from current_database() union all
    select name, setting from pg_settings 
    where name like '%server%' and name not in ('krb_server_keyfile') union all
    select name, setting from pg_settings 
    where name like '%file%' and name not like '%ssl%' union all
    select name, setting from pg_settings 
    where name like '%block%' union all
    select name, setting from pg_settings 
    where name like '%buffer%' union all
    select name, setting from pg_settings 
    where name like '%default%' union all
    select name, setting from pg_settings
    where name like '%archive%'
    or name like '%autovaccum%'
    or name like '%checkpoint%'
    or name like '%fsync%'
    or name like '%WAL%';


---------------------------------------------
-- 블로킹 정보
-- select * from sp_lock0;
---------------------------------------------
drop view public.sp_lock0;

create or replace view public.sp_lock0
as
    select waiting.locktype           as waiting_locktype
         , waiting.relation::regclass as waiting_table
         , waiting_stm.query          as waiting_query
         , waiting.mode               as waiting_mode
         , waiting.pid                as waiting_pid
         , other.locktype             as waitby_locktype
         , other.relation::regclass   as waitby_table
         , other_stm.query            as waitby_query
         , other.mode                 as waitby_mode
         , other.pid                  as waitby_pid
         , other.granted              as waitby_granted
    from pg_catalog.pg_locks as waiting
    inner join pg_catalog.pg_stat_activity as waiting_stm
    on waiting_stm.pid = waiting.pid
    inner join pg_catalog.pg_locks as other
    on ( waiting."database" = other."database"
         and waiting.relation  = other.relation
       ) or waiting.transactionid = other.transactionid
    inner join pg_catalog.pg_stat_activity as other_stm
    on other_stm.pid = other.pid
    where not waiting.granted
    and waiting.pid <> other.pid;


---------------------------------------------
-- 락 정보
-- select * from sp_block0 where depth = 1;
---------------------------------------------
drop view public.sp_block0;

create or replace view public.sp_block0
as
WITH RECURSIVE
    c(requested, current) AS
        ( VALUES ('AccessShareLock'::text, 'AccessExclusiveLock'::text)
              , ('RowShareLock'::text, 'ExclusiveLock'::text)
              , ('RowShareLock'::text, 'AccessExclusiveLock'::text)
              , ('RowExclusiveLock'::text, 'ShareLock'::text)
              , ('RowExclusiveLock'::text, 'ShareRowExclusiveLock'::text)
              , ('RowExclusiveLock'::text, 'ExclusiveLock'::text)
              , ('RowExclusiveLock'::text, 'AccessExclusiveLock'::text)
              , ('ShareUpdateExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text)
              , ('ShareUpdateExclusiveLock'::text, 'ShareLock'::text)
              , ('ShareUpdateExclusiveLock'::text, 'ShareRowExclusiveLock'::text)
              , ('ShareUpdateExclusiveLock'::text, 'ExclusiveLock'::text)
              , ('ShareUpdateExclusiveLock'::text, 'AccessExclusiveLock'::text)
              , ('ShareLock'::text, 'RowExclusiveLock'::text)
              , ('ShareLock'::text, 'ShareUpdateExclusiveLock'::text)
              , ('ShareLock'::text, 'ShareRowExclusiveLock'::text)
              , ('ShareLock'::text, 'ExclusiveLock'::text)
              , ('ShareLock'::text, 'AccessExclusiveLock'::text)
              , ('ShareRowExclusiveLock'::text, 'RowExclusiveLock'::text)
              , ('ShareRowExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text)
              , ('ShareRowExclusiveLock'::text, 'ShareLock'::text)
              , ('ShareRowExclusiveLock'::text, 'ShareRowExclusiveLock'::text)
              , ('ShareRowExclusiveLock'::text, 'ExclusiveLock'::text)
              , ('ShareRowExclusiveLock'::text, 'AccessExclusiveLock'::text)
              , ('ExclusiveLock'::text, 'RowShareLock'::text)
              , ('ExclusiveLock'::text, 'RowExclusiveLock'::text)
              , ('ExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text)
              , ('ExclusiveLock'::text, 'ShareLock'::text)
              , ('ExclusiveLock'::text, 'ShareRowExclusiveLock'::text)
              , ('ExclusiveLock'::text, 'ExclusiveLock'::text)
              , ('ExclusiveLock'::text, 'AccessExclusiveLock'::text)
              , ('AccessExclusiveLock'::text, 'AccessShareLock'::text)
              , ('AccessExclusiveLock'::text, 'RowShareLock'::text)
              , ('AccessExclusiveLock'::text, 'RowExclusiveLock'::text)
              , ('AccessExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text)
              , ('AccessExclusiveLock'::text, 'ShareLock'::text)
              , ('AccessExclusiveLock'::text, 'ShareRowExclusiveLock'::text)
              , ('AccessExclusiveLock'::text, 'ExclusiveLock'::text)
              , ('AccessExclusiveLock'::text, 'AccessExclusiveLock'::text)
        ),
     l AS
       ( SELECT (locktype,DATABASE,relation::regclass::text,page,tuple,virtualxid,transactionid,classid,objid,objsubid) AS target
              , virtualtransaction, pid, mode, granted
           FROM pg_catalog.pg_locks
       ),
     t AS
       ( SELECT blocker.target "blocker_target", blocker.pid "blocker_pid", blocker.mode "blocker_mode"
              , blocked.target "target", blocked.pid "pid", blocked.mode "mode"
           FROM l blocker
           INNER JOIN l blocked
           ON NOT blocked.granted
           AND blocker.granted
           AND blocked.pid != blocker.pid
           AND blocked.target IS NOT DISTINCT FROM blocker.target
           INNER JOIN c 
           ON c.requested = blocked.mode 
           AND c.current = blocker.mode
       ),
     r AS
       ( SELECT blocker_target, blocker_pid, blocker_mode, '1'::int "depth"
              , target, pid,mode, blocker_pid::text || ',' || pid::text "seq"
         FROM t
         UNION ALL
         SELECT blocker.blocker_target, blocker.blocker_pid, blocker.blocker_mode
              , blocker.depth + 1, blocked.target, blocked.pid, blocked.mode
              , blocker.seq || ',' || blocked.pid::text
         FROM r blocker
         INNER JOIN t blocked
         ON blocked.blocker_pid = blocker.pid
         WHERE blocker.depth < 1000
       )
SELECT repeat(' ', aa.depth*2-2) || '┗' "tree", aa.depth
     , aa.pid "blked_pid", aa.target::text "blked_trgt", aa.mode "blked_mode", sb.query "blked_qry"
     , '■'::text  "x"
     , aa.blocker_pid "blkby_pid", aa.blocker_target::text "blkby_trgt", aa.blocker_mode "blkby_mode", sa.query "blkby_qry"
FROM r "aa"
INNER JOIN pg_catalog.pg_stat_activity sa on aa.blocker_pid = sa.pid
INNER JOIN pg_catalog.pg_stat_activity sb on aa.pid = sb.pid
order by aa.seq;


---------------------------------------------
-- 세션 사용 현황
-- select * from sp_who0; 
---------------------------------------------
drop view public.sp_who0;

create or replace view public.sp_who0
as
select pid, state "status", usename "user", client_hostname "host"
     , upper(waiting::varchar(1)) "wait"
     , (select blkby_pid from public.sp_block0 where depth = 1 and blked_pid = a.pid limit 1) "blkby"
     , datname "database", to_char(query_start, 'YYYY-MM-DD HH24:MI:ss.ms') "last_batch", query "sql_text"
from pg_stat_activity a;




반응형

+ Recent posts