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