slave.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]', 2 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_last_xlog_receive_location()::text,'/',1),8,'0'))::bit(32)::bigint AS
     xlog
     , ('x'||lpad(split_part(pg_last_xlog_receive_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_last_xlog_receive_location()::text,'/',1),8,'0'))::bit(32)::bigint AS
     xlog
     , ('x'||lpad(split_part(pg_last_xlog_receive_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'
fullversion: select 'full_version', version()