primary.9.cfg

# vim: syntax=sql
[auto_discovery_60]
minutes: 60
inst.lld: select p.setting||':'|| d.setting "{#INST_NAME}"
          from pg_settings p, pg_settings d
          where p.name = 'port'
          and   d.name = 'data_directory'

db.lld: SELECT datname "{#PDB}" FROM pg_database

parm.lld: select p.setting||':'|| d.setting "{#INST_NAME}", s.name "{#PARAMETER}"
          from pg_settings s
          , pg_settings p, pg_settings d
          where p.name = 'port'
          and   d.name = 'data_directory'
          and s.vartype in ('bool','integer','real')

p_ts.lld: select db.datname "{#PDB}", '' "{#TS_NAME}"
          from pg_database db
          union all
          select '' "{#PDB}", spcname "{#TS_NAME}"
          from pg_tablespace
service.lld: SELECT d.datname "{#PDB}", '' "{#INST_NAME}", '' "{#SERVICE_NAME}"
             FROM pg_database d
             where d.datistemplate = 'false'
rman.lld: select 'backup' "{#OBJ_TYPE}" from pg_database limit 1

[checks_01m]
minutes: 1
inst.uptime: select 'inst['||iname||',uptime]', 
            extract(epoch from current_timestamp - pg_postmaster_start_time())::numeric::integer as uptime
            from (select p.setting||':'|| d.setting iname
                      from pg_settings p, pg_settings d
                      where p.name = 'port'
                      and   d.name = 'data_directory') i
db.openmode: select 'db['||datname||',openstatus]', 3 from pg_database
scn: select 'db[current_scn]', r.xlog*'xFFFFFFFF'::bit(32)::bigint + r.offset as location
     from (
     select ('x'||lpad(split_part(pg_current_xlog_location()::text,'/',1),8,'0'))::bit(32)::bigint AS
     xlog
     , ('x'||lpad(split_part(pg_current_xlog_location()::text,'/',2),8,'0'))::bit(32)::bigint AS offset
     ) r
     union all
     select 'db[delta_scn]', r.xlog*'xFFFFFFFF'::bit(32)::bigint + r.offset as location
     from (
     select ('x'||lpad(split_part(pg_current_xlog_location()::text,'/',1),8,'0'))::bit(32)::bigint AS
     xlog
     , ('x'||lpad(split_part(pg_current_xlog_location()::text,'/',2),8,'0'))::bit(32)::bigint AS offset
     ) r

barman: select 'rman[backup,status]',
            case when  pg_is_in_backup() = 'false' then '0'
                                                   else '5'
            end
        union all
        select 'rman[backup,ela]', 
          cast(extract(epoch from current_timestamp - 
                                  pg_backup_start_time())::numeric::integer as varchar) 
          where (extract(epoch from current_timestamp - pg_backup_start_time())) > 0
[checks_05m]
minutes: 5
parm.val: select 'parm['||i.iname||','|| s.name ||','||
     case when s.unit like '%B' then 'size'
          when s.name like '%size' then 'size'
     else 'value'
     end ||']',
            case when s.setting = 'off' then '0'
                 when s.setting = 'on'  then '1'
                 else case when unit = '16MB' then s.setting::numeric*16*1024*1024
                           when unit = '8kB'  then s.setting::numeric*8*1024
                           when unit = 'kB'   then s.setting::numeric*1024
                      else s.setting::numeric
                      end
            end setting
            from pg_settings s, (select p.setting||':'|| d.setting iname
                                  from pg_settings p, pg_settings d
                                  where p.name = 'port'
                                  and   d.name = 'data_directory') i
            where s.vartype in ('bool','integer','real')
service.cnt: SELECT 'service['||datname||',,,sess]', count(*)
             FROM pg_stat_activity
             group by datname
[checks_60m]
minutes: 60
p_ts: with dbsizes as (
      select 'p_ts[,'||spcname||',filesize]' kkey,pg_tablespace_size(spcname)  ssize
            from pg_tablespace
            union all
            select 'p_ts[,'||spcname||',usedbytes]',pg_tablespace_size(spcname)
            from pg_tablespace
            union all
            select 'p_ts['||datname||',,filesize]', pg_database_size(datname) from pg_database
            union all
            select 'p_ts['||datname||',,usedbytes]', pg_database_size(datname) from pg_database
      )
      select * from dbsizes
      union all
      select 'db[filesize]', sum(ssize) from dbsizes where kkey  like '%,filesize]'
                    and kkey not like 'p_ts[,pg_default,%'
      union all
      select 'db[usedbytes]', sum(ssize) from dbsizes where kkey  like '%,usedbytes]'
                    and kkey not like 'p_ts[,pg_default,%'
instname: select 'zbxdb[connect,instance_name]', inet_server_addr()||':'||p.setting||':'|| d.setting
          from pg_settings p, pg_settings d
          where p.name = 'port'
          and   d.name = 'data_directory'
rowcount: commit;
	set transaction read write;
	drop table if exists zbxdb;
	create temporary table zbxdb
	( dbname text
	, live_t  bigint
	, dead_t  bigint
	);
	do $$declare 
		x RECORD;
		conn_string TEXT;
		conn_template text := 'dbname=';
	begin  
	    FOR x IN
		(SELECT * FROM pg_database
		 WHERE datistemplate = false)
	    loop
		conn_string = conn_template || x.datname;
		insert into zbxdb (dbname, live_t, dead_t)
		select dbname, live_t, dead_t 
		  from dblink(conn_string, 
		  'SELECT current_database() dbname, sum(n_live_tup) live_t, sum(n_dead_tup) dead_t 
		  FROM pg_stat_user_tables'
		  ) as y (dbname text, live_t int, dead_t int);
	    end loop;
	end$$;
  commit;
	set transaction read only;
	select 'db['||dbname||',liverows]', live_t from zbxdb
	union all
	select 'db['||dbname||',deadrows]', dead_t from zbxdb
	union all
	select 'total.liverows', sum(live_t) from zbxdb
	union all
	select 'total.deadrows', sum(dead_t) from zbxdb
fullversion: select 'full_version', version()