DCA Information SQL

--SELECT 'You are now connected to database '|| current_database()||' as user '||current_user as Connection_details;

 -- 1. Check total no of segments in the DCA

Select count(*) as total_segments_in_the_database from gp_segment_configuration;

 -- 2. check_GP_server_segment status

select dbid, content, role, preferred_role, mode, status, port,hostname, address from gp_segment_configuration where role = 'p' order by dbid;

 --3. check storage utilization per segment server

\c gpperfmon

select ctime,hostname,filesystem,pg_size_pretty(total_bytes) as total_bytes,pg_size_pretty(bytes_used) as used_bytes, pg_size_pretty(bytes_available) as avail_bytes, (round((bytes_used * 100)/total_bytes::numeric,2))||'%' as pct_used from diskspace_now order by (round((bytes_used * 100)/total_bytes::numeric,2)) desc;

-- 4. Disk Space Availability

select total_tb,used_tb, available_tb, round((used_tb*100)/total_tb::numeric,2) as pct_used, round((available_tb*100)/total_tb::numeric,2) as pct_available from

(

select

       sum(round((bytes_used / power(1024,4))::numeric,2)) as used_tb,

       sum(round((bytes_available / power(1024,4))::numeric,2)) as available_tb,

       sum(round((total_bytes / power(1024,4))::numeric,2)) as total_tb

from

(

select date_trunc('hour', ctime) ctime_hour,

       to_char(ctime, 'Month') as "month",

       to_char(ctime, 'ddd') as "day",

       to_char(ctime, 'Day') as weekday,

       to_char(ctime, 'hh24') as "hour",

       hostname,

       filesystem,

       bytes_used,

       bytes_available,

       total_bytes,

       row_number() over (partition by to_char(ctime, 'ddd'), hostname order by total_bytes desc) space_used_rank

from   diskspace_now

where filesystem in ('/data1','/data2','/disk1','/disk2')

and   hostname like 'sdw%' or hostname like 'sachi%'

) a

where space_used_rank = 1

group by day

) as b;