GPDB 시스템 카탈로그

1.      시스템 카탈로그

1)        시스템 카탈로그 테이블

n  참고 페이지

-      https://gpdb.docs.pivotal.io/43160/ref_guide/system_catalogs/catalog_ref.html

-      http://stephendotcarter.github.io/greenplum-syscat-ref/

-      http://stephendotcarter.github.io/greenplum-syscat-ref/diagram.html
 

스키마명	테이블명	설명
pg_catalog	gp_configuration_history	DB 상태 이력 저장
pg_catalog	gp_distribution_policy	분산키 정보 저장
pg_catalog	gp_segment_configuration	Greenplum 시스템 구성정보
pg_catalog	pg_attribute	컬럼 정보
pg_catalog	pg_class	Object 관리 테이블(Table, Index, View 등)
pg_catalog	pg_constraint	Constraint 관리
pg_catalog	pg_exttable	External Table 정보
pg_catalog	pg_index	인덱스 관리
pg_catalog	pg_language	언어 관리
pg_catalog	pg_namespace	스키마 정보
pg_catalog	pg_partition_rule	파티션 구성 정보
pg_catalog	pg_proc	함수, 프로시저 관리
pg_catalog	pg_resqueue	리소스큐 관리
pg_catalog	pg_stat_last_operation	테이블 이력 관리
pg_catalog	pg_statistic	통계정보
pg_catalog	pg_tablespace	테이블스페이스

2)        시스템 카탈로그 뷰

스키마명	View name	설명
pg_catalog	pg_cursors	open cursors
pg_catalog	pg_group	groups of database users
pg_catalog	pg_indexes	indexes
pg_catalog	pg_locks	currently held locks
pg_catalog	pg_roles	database roles
pg_catalog	pg_rules	rules
pg_catalog	pg_settings	parameter settings
pg_catalog	pg_shadow	database users
pg_catalog	pg_stats	planner statistics
pg_catalog	pg_stat_activity	세션 정보
pg_catalog	pg_tables	tables
pg_catalog	pg_timezone_abbrevs	time zone abbreviations
pg_catalog	pg_timezone_names	time zone names
pg_catalog	pg_user	database users
pg_catalog	pg_views	views
gp_toolkit	gp_bloat_diag	테이블 Bloat 정보 (Analyze 수행해야지만 정확한 값이 나옴)
gp_toolkit	gp_stats_missins	통계 누락 된 테이블리스트 추출

3)        시스템 카탈로그 뷰

스키마명	함수명	설명
pg_catalog	pg_cancel_backend(pid int)	쿼리 종료
pg_catalog	pg_terminate_backend(pid int)	세션 종료
pg_catalog	pg_rotate_logfile()	서버로그 로테이션
pg_catalog	pg_database_size(name)	데이터베이스의 용량
pg_catalog	pg_relation_size(text)	테이블 또는 인덱스의 사이즈 추출
pg_catalog	pg_size_pretty(bigint)	사이즈를 단위로 표시
pg_catalog	pg_total_relation_size(text)	인덱스, Toast 포함 사이즈

2.   권환 관리 스크립트

     1)        권한 확인

SELECT auth.rolname, cl.relkind, cln.nspname,  cl.relname   FROM pg_class cl       JOIN pg_namespace cln ON cl.relnamespace=cln.oid       join pg_shdepend shd  on cl.oid = shd.objid       join pg_authid   auth on auth.oid = shd.refobjid  --WHERE auth.rolname = '계정 OR Group Role'  ORDER BY 1,2,3;   select usename      , nspname || '.' || relname as relation      , case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type      , priv  from pg_class  join pg_namespace    on pg_namespace.oid = pg_class.relnamespace     , pg_user     , (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder) where relkind in ('r', 'v')         and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)   and not (nspname ~ '^pg_' or nspname = 'information_schema') order by 2, 1, 3, privorder;

 

2)        일괄 권한 부여 (Table, Function, View)

1) 테이블 $ cat priv_table.sql select ' ALTER TABLE '||schemaname||'.'||tablename||' OWNER TO biadmin; ' --schemaname--, tablename from    pg_tables where   schemaname in ('etl', 'mig', 'report', 'svc', 'tf', 'tf_rep', 'tmp', 'public', 'tdm') and     tablename not like '%1_prt_%' and     tableowner <> 'biadmin' ;   2) Function $ cat priv_proc.sql SELECT 'ALTER FUNCTION ' || quote_ident(ns.nspname) || '.' || quote_ident(proname) || '(' || oidvectortypes(proargtypes) || ') OWNER TO biadmin;' FROM   pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid) inner join pg_roles r on (pg_proc.proowner = r.oid) WHERE ns.nspname in ('public')   3) View cat priv_view.sql select ' ALTER TABLE '||schemaname||'.'||viewname||' OWNER TO biadmin; ' qry from    pg_views where   schemaname in ('public') and     viewowner <> 'biadmin' ;   4) 일괄 권한 부여 $ psql -AtXf priv_table.sql | psql –e $ psql -AtXf priv_ proc.sql | psql –e $ psql -AtXf priv_ view.sql | psql -e

3)        스키마별 Grant  (tables, views, sequences)

CREATE OR REPLACE FUNCTION schema_grant(text, text, text, text) RETURNS integer AS $BODY$ -- Usage: select schema_grant('role','permission','object','schema'); -- Usage: select schema_grant('role','all','%','schema'); DECLARE obj record; num integer; BEGIN num:=0; FOR obj IN SELECT relname FROM pg_class c JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE relkind in ('r','v','S') AND nspname = $4 AND relname LIKE $3 LOOP EXECUTE 'GRANT ' ||$2|| ' ON ' ||$4|| '.' || obj.relname || ' TO ' ||$1; num := num + 1; END LOOP; RETURN num; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

 

4)        스키마별 Revoke (tables, views, sequences)

CREATE OR REPLACE FUNCTION schema_revoke(text, text, text, text)   RETURNS integer AS $BODY$ -- Usage: select schema_revoke('role','permission','object','schema'); -- Usage: select schema_revoke('role','all','%','schema'); DECLARE obj record; num integer; BEGIN num:=0; FOR obj IN SELECT relname FROM pg_class c JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE relkind in ('r','v','S') AND nspname = $4 AND relname LIKE $3 LOOP EXECUTE 'REVOKE ' ||$2|| ' ON ' ||$4|| '.' || obj.relname || ' FROM ' ||$1; num := num + 1; END LOOP; RETURN num; END; $BODY$   LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

 

2.      DB 관리 스크립트

1)        분산키 확인

1) 분산키 확인 select   nspname||'.'||relname as t_name,   string_agg(attname, ', ' order by colorder) FROM   (select localoid, unnest(attrnums) as colnum, generate_series(1, array_upper(attrnums, 1)) as colorder from gp_distribution_policy) d   join pg_attribute a on (d.localoid = a.attrelid and d.colnum = a.attnum)   join pg_class c on (d.localoid = c.oid)   join pg_namespace n on (c.relnamespace = n.oid) GROUP BY nspname, relname;   2) Randomly 분산키 확인 select count(*) cnt from ( select schemaname, tablename from   pg_tables where  schemaname not in ('h_edw_db', 'h_mart_db', 'j_edw_db', 'j_mart_db', 'public') except  SELECT n.nspname, c.relname    FROM gp_distribution_policy d, pg_class c, pg_namespace n   WHERE d.localoid = c.oid   AND   c.relnamespace = n.oid   AND   c.relkind = 'r'::"char"   AND   n.nspname not in ('h_edw_db', 'h_mart_db', 'j_edw_db', 'j_mart_db') and    d.attrnums is  not null ) a ;

2)        SKEW 확인

## gpdb 4.X   ## gpdb 3.X or 전수 분석 $ cat skew_base.sql select 'SELECT '''||a.schemaname||'.'||a.tablename||''' tb_nm, Avg(Row_Count), max(Row_Count),(1-(Avg(Row_Count)/Max(Row_Count)))*100 as Skew_Pct FROM (SELECT gp_segment_id, count(*) as Row_Count FROM '||a.schemaname||'.'||a.tablename||' GROUP BY gp_segment_id ) A;' qry from   pg_tables a        left outer join pg_partitions b        on a.tablename = b.tablename        left outer join pg_class c        on a.tablename = c.relname where  b.tablename is null and    relstorage <> 'x' group by a.schemaname, a.tablename order by 1 $ psql -AXtf skew_base.sql > skew_all.sql   # Background 실행 $ nohup psql -ef skew_all.sql > skew_rslt.log 2>&1 &   # 50만건 이상이면서, 분산도가 5% 이상인 리스트 확인 방법 $ grep "|" skew_rslt.log | grep -v tb_nm | awk -F"|" '$4>10 {print$0}' | awk -F"|" '$3>25000 {print$0}'

3)        사이즈 확인(DB / Table / 파티션별)

## gpdb 4.X 1) 테이블 사이즈 SELECT relname as name       , sotdsize as size       , sotdtoastsize as toast       , sotdadditionalsize as other   FROM gp_size_of_table_disk as sotd, pg_class  WHERE sotd.sotdoid=pg_class.oid  ORDER BY relname;   2) 사이즈 관련 View select * from gp_toolkit.gp_size_of_all_table_indexes select * from gp_toolkit.gp_size_of_database select * from gp_toolkit.gp_size_of_index select * from gp_toolkit.gp_size_of_partition_and_indexes_disk select * from gp_toolkit.gp_size_of_schema_disk select * from gp_toolkit.gp_size_of_table_and_indexes_disk select * from gp_toolkit.gp_size_of_table_and_indexes_licensing select * from gp_toolkit.gp_size_of_table_disk select * from gp_toolkit.gp_size_of_table_uncompressed select * from gp_toolkit.gp_disk_free   1) DB 사이즈 select pg_size_pretty(pg_database_size('database명'));   2) TB 사이즈 -- 인덱스 포함 SELECT pg_size_pretty(pg_total_relation_size('faa.d_airlines'));   -- 인덱스 제외 SELECT pg_size_pretty(pg_relation_size('faa.d_airlines'));   -- 쿼리 select schemaname, tb_nm, mb from   ( SELECT schemaname, SPLIT_PART(st.relname, '_1', 1)  tb_nm      , round(sum(pg_relation_size(st.relid) )/1024/1024::int) MB   FROM pg_stat_all_tables st   JOIN pg_class cl     on cl.oid=st.relid  WHERE schemaname like 'bmt%'  group by 1,2  ) a order by 1,2 ;   3) Partition size select schemaname, tb_pt_nm, mb from   ( SELECT schemaname, st.relname  tb_pt_nm        , round(sum(pg_relation_size(st.relid) )/1024/1024::int) MB   FROM pg_stat_all_tables st   JOIN pg_class cl     on cl.oid=st.relid  WHERE schemaname  like 'hcn%'  group by 1,2  ) a order by 1,2 ;   4) 테이블 사이즈(view) create view v_table_size                                                                                                                                     as                                                                                                                                                                SELECT schemaname , SPLIT_PART(st.relname, '_1', 1) tablename                                                                                                                              , (sum(pg_relation_size(st.relid)                                                                                                                                + CASE WHEN cl.reltoastrelid = 0 THEN 0                                                                                                                                  ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)        END                                                                                                                                                             + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=st.relid)::int8, 0))/1024/1024)::int::char(20)|| 'MB'  AS size              FROM pg_stat_all_tables st JOIN pg_class cl on cl.oid=st.relid                                                                                                  WHERE schemaname = 'bmt'                                                                                                                                          group by 1                                                                                                                                                       order by 1;

4)        통계 정보 및 실행 일자 확인

1) 통계 정보 확인 select nspname, relname, attname, a.* from   pg_statistic a, pg_class b, pg_attribute c, pg_namespace d where  a.starelid = b.oid and    c.attrelid = b.oid and    a.staattnum = c.attnum and    d.oid = b.relnamespace and    relname =’테이블명’ order by 1,2,3,4,5,6;   2) 통계 정보 실행 일자 확인 Select * from pg_stat_operations where schemaname='SCHEMA NAME ' and actionname in ('ANALYZE','VACUUM') order by statime;

5)        테이블/Object Operation 일자 확인

1) 통계 정보 확인 SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time FROM pg_stat_operations WHERE objname='cust';   2) 통계 정보 실행 일자 확인 Select * from pg_stat_operations where schemaname='SCHEMA NAME ' and actionname in ('ANALYZE','VACUUM') order by statime;

3.      유용한 스크립트

    1)        코멘트 달기

/* 코멘트 달기 */ COMMENT ON TABLE t_tf_blog_top_blog_dd IS '트래픽_블로그_'; COMMENT ON COLUMN t_tf_blog_top_blog_dd.sum_dt IS '요약일';

 

2)        테이블 목록 (테이블 Comment 확인)

SELECT       n.nspname,       lower(c.relname) tb_nm,       pg_catalog.col_description(a.attrelid, 0) FROM pg_catalog.pg_attribute a      LEFT JOIN pg_catalog.pg_class c     ON a.attrelid = c.oid      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE a.attnum = 1 --AND NOT a.attisdropped --AND pg_catalog.pg_table_is_visible(c.oid) --AND c.relname like 't_tf%' --and n.nspname = 'tf' and c.relname not like '%_1_prt_p%' ORDER BY 1, 2, 3

 

3)        테이블 정의서  (테이블/컬럼 Comment 확인)

SELECT       n.nspname schema_nm,       upper(c.relname) tb_nm,       pg_catalog.col_description(a.attrelid, 0) as entity_nm,       a.attnum,       upper(a.attname) col_nm,       pg_catalog.col_description(a.attrelid, a.attnum) att_cmt,       replace(replace(pg_catalog.format_type(a.atttypid, a.atttypmod), 'character varying', 'varchar'), 'without time zone', '') data_tp FROM pg_catalog.pg_attribute a      LEFT JOIN pg_catalog.pg_class c     ON a.attrelid = c.oid      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE a.attnum > 0 --AND NOT a.attisdropped --AND pg_catalog.pg_table_is_visible(c.oid) --AND c.relname like 't_tf%' --and n.nspname = 'public' ORDER BY 1, 2, 3, 4

4)        컬럼 정보확인 (분산키, PK 여부, NULL 여부)

select    c.relname as 테이블명      ,    row_number() over (partition by c.relname order by a.attnum) as 컬럼ID      ,    a.attname as 컬럼명      ,    format_type(a.atttypid, a.atttypmod) as 데이터타입      ,    case when d.attrnums is not null then 'Y' else 'N' end as 분산키여부      ,    case when i.indrelid is not null then 'Y' else 'N' end as PK여부      ,    case when a.attnotnull or (t.typtype = 'd'::"char" and t.typnotnull) then 'N' else 'Y' end as NULL여부   from    pg_class c   join    pg_namespace n on c.relnamespace = n.oid   join    pg_attribute a on c.oid = a.attrelid and a.attnum >= 0   join    pg_type t on a.atttypid = t.oid   left    outer join pg_partition_rule pr on c.oid = pr.parchildrelid   left    outer join gp_distribution_policy d on c.oid = d.localoid and a.attnum = ANY (d.attrnums)   left    outer join pg_index i on a.attrelid = i.indrelid and a.attnum = ANY(i.indkey) and i.indisunique where    c.relkind = 'r'    and    pr.parchildrelid is null -- 파티션 child 테이블 제외    and    n.nspname = '스키마명' order by 1,2;

 

5)        압축 테이블 리스트 보기

SELECT NSPNAME AS SCHEMANAME,        RELNAME AS TABLENAME,        CASE C.RELKIND           WHEN 'x' THEN           'external table'           WHEN 'r' THEN           'table'           WHEN 'v' THEN           'view'           WHEN 'i' THEN           'index'           WHEN 'S' THEN           'sequence'           WHEN 's' THEN           'special'         END AS "Type",        CASE C.RELSTORAGE          WHEN 'h' THEN          'heap'          WHEN 'a' THEN          'append only'          WHEN 'v' THEN          'none'          WHEN 'c' THEN          'append only columnar'        END AS "Storage"   FROM PG_CLASS C, PG_NAMESPACE N  WHERE C.RELKIND IN ('r')    AND C.OID NOT IN (SELECT PARCHILDRELID FROM PG_PARTITION_RULE)    AND C.RELNAMESPACE = N.OID    AND N.NSPNAME NOT IN ('pg_catalog', 'pg_toast', 'information_schema')    AND C.RELSTORAGE = 'a'  ORDER BY 1, 2, 3

 

6)            테이블 /파티션별 사이즈/압축모드/압축옵션

select * from  (  SELECT a.schemaname AS schema_nm, a.tb_nm, a.tb_pt_nm, b.partitionname, a.tb_kb, a.tb_tot_kb, Storage, reloptions    FROM ( SELECT st.schemaname                 , split_part(st.relname::text, '_1_prt_'::text, 1) AS tb_nm                 , st.relname AS tb_pt_nm                 , round(sum(pg_relation_size(st.relid)) / 1024::bigint::numeric) AS tb_kb                 , round(sum(pg_total_relation_size(st.relid)) / 1024::bigint::numeric) AS tb_tot_kb                 , CASE cl.RELSTORAGE                        WHEN 'h' THEN          'heap'                        WHEN 'a' THEN          'append only'                        WHEN 'v' THEN          'none'                        WHEN 'c' THEN          'append only columnar'                        END AS Storage                 , cl.reloptions            FROM pg_stat_all_tables st       JOIN pg_class cl ON cl.oid = st.relid      WHERE st.schemaname !~~ 'pg_temp%'::text AND st.schemaname <> 'pg_toast'::name AND cl.relkind <> 'i'::"char"      GROUP BY 1,2,3,6,7      ) a      LEFT OUTER JOIN pg_partitions b      on    a.schemaname = b.schemaname      and   a.tb_pt_nm   = b.partitiontablename      where (a.schemaname, a.tb_pt_nm ) not in (select schemaname, tablename from pg_partitions) ) b where  schema_nm not in ('gp_toolkit', 'err', 'information_schema', 'pg_catalog', 'public' ) and    schema_nm <> 'upload' order by 1, 2, 3

select      n.nspname as schema_name,      t.relname as table_name,      a.attname as column_name,      'btree'::varchar(100) as index_type,      i.relname as index_name,      ix.indisunique unique_yn,      ix.indisprimary pk_yn     from pg_class t, pg_class i, pg_index ix, pg_attribute a, pg_roles r, pg_namespace n where 1=1 and  t.oid = ix.indrelid and  r.oid = t.relowner and i.oid = ix.indexrelid and a.attrelid = t.oid and a.attnum = ANY(ix.indkey) and t.relkind = 'r' and t.relnamespace = n.oid and  n.nspname  not in ('gp_toolkit', 'err', 'information_schema', 'pg_catalog', 'public' , 'upload') --and t.relname = 'a' and ix.indisunique = 't' --and ix.indisprimary = 'f' order by t.relname, i.relname;