• postgresql 监控日志的方法


    postgresql数据库运行产生的日志都会输出到日志文件里,因此实时监控该文件就可以大致了解到数据库的运行状况。

    public.sp_gather_pgsql_log_part(pi_time_last integer)

    这个函数的作用是创建目标机的日志外部表

    CREATE OR REPLACE FUNCTION public.sp_gather_pgsql_log_part(pi_time_last integer)
     RETURNS void
     LANGUAGE plpgsql
    AS $function$
    declare
        v_sql        text;
        v_sp_name    varchar(100);
        v_pglogfile  varchar(100);
        v_pglogfile2 varchar(100);
        v_time_last  int4;
        v_time       timestamp without time zone;
    
        c_db RECORD;
        d_db RECORD;
    
    begin 
    
        v_sql := '';
        v_sp_name :='public.sp_gather_pgsql_log_part()';
        v_pglogfile :='';
    
    
        for c_db in (
            select distinct 
                   td.db_num,
                   td.db_version, 
                   td.db_master_role_phyip      as db_mon_ip,
                   td.db_master_role_phyip_port as db_mon_ip_port,
    
                   tdi.db_user,
                   tdi.db_pwd,
                   tdi.default_dbname
              from t_task_database td,
                   t_task_database_ident tdi
             where 1=1
               and td.db_num= tdi.db_num
               and td.db_ismonitor = true
               and td.db_master_role_phyip is not null 
               and td.db_master_role_phyip <>  ''
               and td.db_type = 'pgsql'
               --and td.db_num = 'pg002'
        )
        loop
          v_pglogfile  :='';
          v_pglogfile2 :='';
          BEGIN
               v_sql:='
               with tmp_pglogfile as (
                  select * 
                    from dblink(''dbname='||c_db.default_dbname||' host='||c_db.db_mon_ip||' port='||c_db.db_mon_ip_port||' user='||c_db.db_user||' password='||c_db.db_pwd||''',
                                '' select t00.file,t00.file_ls
                                    from (
                                        select t1.file,
                                               t1.file_ls,
                                               (pg_stat_file(t1.file)).modification as last_update_time,
                                               round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
                                          from (select dir||''''/''''||pg_ls_dir(t0.dir) as file,
                                                       pg_ls_dir(t0.dir) as file_ls
                                                  from ( select setting as dir from pg_settings where name=''''log_directory''''
                                                        ) t0
                                                ) t1 
                                         where 1=1
                                         order by (pg_stat_file(file)).modification desc
                                        ) t00
                                    where 1=1
                                      and t00.file_ls like ''''%.csv''''
                                    limit 1  '') as  t1 (pglogfile varchar,pglogfilels varchar)
               )
               select pglogfile
                 from tmp_pglogfile     
              ';
    
              --获取当前日志文件
              execute v_sql into v_pglogfile;
    
              v_sql:='
               with tmp_pglogfile2 as (
                  select * 
                    from dblink(''dbname='||c_db.default_dbname||' host='||c_db.db_mon_ip||' port='||c_db.db_mon_ip_port||' user='||c_db.db_user||' password='||c_db.db_pwd||''',
                                '' with tmp_t0 as (
                                        select pc.relname,
                                               unnest(pft.ftoptions) as relinfo
                                          from pg_class pc,
                                               pg_foreign_table pft
                                         where 1=1
                                           and pc.oid = pft.ftrelid
                                           and pc.relname like ''''pglog_current''''
                                   )
                                   select replace(relinfo,''''filename='''','''''''') as rel
                                     from tmp_t0
                                    where 1=1
                                      and relinfo like ''''%filename=%''''
                                '') as  t1 (pglogfile2 varchar)
               )
               select pglogfile2
                 from tmp_pglogfile2    
              ';
    
              raise notice 'v_sql= %',v_sql;
    
    
              --获取当前日志文件外部表指向的日志文件
              execute v_sql into v_pglogfile2;
    
    
              raise notice 'v_pglogfile= %,v_pglogfile2= %',v_pglogfile,v_pglogfile2;
    
    
              if v_pglogfile = v_pglogfile2 then
                 continue;
              else 
                 v_sql:='
                  select dblink(''dbname='||c_db.default_dbname||' host='||c_db.db_mon_ip||' port='||c_db.db_mon_ip_port||' user='||c_db.db_user||' password='||c_db.db_pwd||''',
                                '' drop foreign table if exists pglog_current;  '')     
                 ';
    
                 --raise notice 'v_sql= %',v_sql;
    
                 --删除远程机器当前日志文件的外部表
                 execute v_sql;
    
                 v_sql:='
                  select dblink(''dbname='||c_db.default_dbname||' host='||c_db.db_mon_ip||' port='||c_db.db_mon_ip_port||' user='||c_db.db_user||' password='||c_db.db_pwd||''',
                                '' 
                                    CREATE FOREIGN TABLE pglog_current (  
                                      log_time timestamp(3) without time zone,  
                                      user_name text,  
                                      database_name text,  
                                      process_id integer,  
                                      connection_from text,  
                                      session_id text,  
                                      session_line_num bigint, 
                                      command_tag text, 
    
                                      session_start_time timestamp with time zone,  
                                      virtual_transaction_id text,  
                                      transaction_id bigint,  
                                      error_severity text,  
                                      sql_state_code text,  
                                      message text,  
                                      detail text,  
                                      hint text,  
                                      internal_query text,  
                                      internal_query_pos integer,  
                                      context text,  
                                      query text,  
                                      query_pos integer,  
                                      location text,  
                                      application_name text
                                     ) 
                                    server file_fdw_server 
                                    options(filename '''''||v_pglogfile||''''',format ''''csv'''')
                                    ;  '')  
                 ';
    
                 --创建远程机器当前日志文件的外部表
                 execute v_sql;
    
              end if;
    
    
          EXCEPTION 
              WHEN others then
    
              --raise notice 'v_sql= %',v_sql;
              --记录异常日志          
              perform sp_util_exception_writelog(
                 c_db.db_num,
                 c_db.db_mon_ip,
                 now(),
                 'log',
    
                 v_sp_name,
                 sqlstate::varchar,
                 sqlerrm::text,
                 'n20'::varchar,
                 'dba'::varchar
               );
    
          END;
        end loop;
    
        --减少stream 异步复制的报错
        execute 'select pg_sleep(3)';
    
        --插入最近pglog数据,主库和从库的日志都要插入
        delete from t_gather_pgsql_log_part;
    
        select pi_time_last
          into v_time_last
          ;
    
        select LOCALTIMESTAMP(0)
          into v_time
          ;
    
        for d_db in (
           select distinct 
                  t0.db_num,
                  t0.db_version, 
                  t0.db_mon_ip,
                  t0.db_mon_ip_port,
    
                  t0.db_user,
                  t0.db_pwd,
                  t0.default_dbname
           from (
                select distinct 
                       td.db_num,
                       td.db_version, 
                       td.db_master_role_phyip      as db_mon_ip,
                       td.db_master_role_phyip_port as db_mon_ip_port,
    
                       tdi.db_user,
                       tdi.db_pwd,
                       tdi.default_dbname
                  from t_task_database td,
                       t_task_database_ident tdi
                 where 1=1
                   and td.db_num= tdi.db_num
                   and td.db_ismonitor = true
                   and td.db_master_role_phyip is not null 
                   and td.db_master_role_phyip <>  '' 
                   and td.db_type = 'pgsql'
    
                union all
    
                select distinct 
                       td.db_num,
                       td.db_version, 
                       td.db_slave_role_phyip       as db_mon_ip,
                       td.db_slave_role_phyip_port  as db_mon_ip_port,
    
                       tdi.db_user,
                       tdi.db_pwd,
                       tdi.default_dbname
                  from t_task_database td,
                       t_task_database_ident tdi
                 where 1=1
                   and td.db_num= tdi.db_num
                   and td.db_ismonitor = true
                   and td.db_master_role_phyip is not null 
                   and td.db_master_role_phyip <>  '' 
                   and td.db_type = 'pgsql'
                 ) t0
            where 1=1
              --and t0.db_num='pg002'
    
        )
        loop
    
          BEGIN
              v_sql:='
               insert into t_gather_pgsql_log_part(
                db_num,
                db_ip,
                log_time,
                user_name,
                database_name,
                process_id,
                connection_from,
                session_id,
                session_line_num,
                command_tag,
                session_start_time,
                virtual_transaction_id,
                transaction_id,
                error_severity,
                sql_state_code,
                message,
                detail,
                hint,
                internal_query,
                internal_query_pos,
                context,
                query,
                query_pos,
                location,
                application_name
               )
               with tmp_pglogdata as (
                  select * 
                    from dblink(''dbname='||d_db.default_dbname||' host='||d_db.db_mon_ip||' port='||d_db.db_mon_ip_port||' user='||d_db.db_user||' password='||d_db.db_pwd||''',
                                '' select 
                                        log_time,
                                        user_name,
                                        database_name,
                                        process_id,
                                        connection_from,
                                        session_id,
                                        session_line_num,
                                        command_tag,
                                        session_start_time,
                                        virtual_transaction_id,
                                        transaction_id,
                                        error_severity,
                                        sql_state_code,
                                        message,
                                        detail,
                                        hint,
                                        internal_query,
                                        internal_query_pos,
                                        context,
                                        query,
                                        query_pos,
                                        location,
                                        application_name
                                   from pglog_current 
                                   where 1=1
                                     and log_time between '''''||to_char(v_time - (v_time_last::varchar)::interval DAY TO second,'yyyy-mm-dd hh24:mi:ss')||'''''::timestamp without time zone  
                                                      and '''''||to_char(v_time ,'yyyy-mm-dd hh24:mi:ss')||'''''::timestamp without time zone
                               '') as  t1 (   log_time timestamp(3) without time zone,  
                                              user_name text,  
                                              database_name text,  
                                              process_id integer,  
                                              connection_from text,  
                                              session_id text,  
                                              session_line_num bigint, 
                                              command_tag text, 
    
                                              session_start_time timestamp with time zone,  
                                              virtual_transaction_id text,  
                                              transaction_id bigint,  
                                              error_severity text,  
                                              sql_state_code text,  
                                              message text,  
                                              detail text,  
                                              hint text,  
                                              internal_query text,  
                                              internal_query_pos integer,  
                                              context text,  
                                              query text,  
                                              query_pos integer,  
                                              location text,  
                                              application_name text)
               )
               select   '''||d_db.db_num||''' as db_num,
                        '''||d_db.db_mon_ip||''' as db_ip,
                        log_time,
                        user_name,
                        database_name,
                        process_id,
                        connection_from,
                        session_id,
                        session_line_num,
                        command_tag,
                        session_start_time,
                        virtual_transaction_id,
                        transaction_id,
                        error_severity,
                        sql_state_code,
                        message,
                        detail,
                        hint,
                        internal_query,
                        internal_query_pos,
                        context,
                        query,
                        query_pos,
                        location,
                        application_name
                 from tmp_pglogdata     
              ';
    
              --raise notice 'v_sql= %',v_sql;
    
              --获取当前日志文件数据
              execute v_sql ;
    
              --raise notice 'v_pglogfile= %',v_pglogfile;
    
          EXCEPTION 
              WHEN others then
    
              --raise notice 'v_sql= %',v_sql;
              --记录异常日志
              perform sp_util_exception_writelog(
                 d_db.db_num,
                 d_db.db_mon_ip,
                 now(),
                 'log',
    
                 v_sp_name,
                 sqlstate::varchar,
                 sqlerrm::text,
                 'n20'::varchar,
                 'dba'::varchar
               );
    
          END;
        end loop;
    
    
        RETURN;
    end;
    $function$
    

    public.sp_gather_pgsql_log_done()

    这个函数的作用是根据一些预先定义的关键字从临时日志表里获取到关联的信息。

    CREATE OR REPLACE FUNCTION public.sp_gather_pgsql_log_done()
     RETURNS void
     LANGUAGE plpgsql
    AS $function$
    declare
        v_sql  text;
        v_sp_name   varchar(100);
        v_time      timestamp without time zone;
        v_filename  varchar(100);
    
        v_error_severity_list text;
        v_sql_state_code text;
    
        c_db RECORD;
        d_db RECORD;
    
    begin 
    
        v_sql := '';
        v_sp_name :='public.sp_gather_pgsql_log_done()';
    
    
        for c_db in (
    
             select tp.db_num,
                    tp.db_ip as db_mon_ip,
                    to_char(min(tp.log_time),'yyyy-mm-dd hh24:mi:ss') as min_log_time,
                    to_char(max(tp.log_time),'yyyy-mm-dd hh24:mi:ss') as max_log_time,
                    to_char(min(tp.log_time),'yyyymmddhh24miss')      as min_log_time_file,
                    to_char(max(tp.log_time),'yyyymmddhh24miss')      as max_log_time_file
               from public.t_gather_pgsql_log_part tp
              where 1=1
              group by tp.db_num,
                       tp.db_ip
              order by tp.db_num,
                       tp.db_ip         
    
        )
        loop
          v_sql :='';
          v_error_severity_list :='';
          v_sql_state_code :='';
          v_filename :='';
    
          BEGIN
    
              for d_db in (
                 select tp.error_severity,
                        tp.sql_state_code,
                        tp.message,
                        count(1) as cnt,
                        sp_get_ip_to_applist(string_agg( distinct substring( tp.connection_from,1,position(':' in tp.connection_from)-1 ),',') ) as conn_ip
                 from public.t_gather_pgsql_log_part tp
                 where 1=1
                   and tp.db_num = c_db.db_num
                   and tp.db_ip = c_db.db_mon_ip
                   and lower(tp.error_severity) in (
                        'warning',
                        'error',
                        'fatal',
                        'panic',
                        'deadlock'
                       )
                   and tp.message not like '[____]%'
                   --and tp.message not like '%violates unique constraint%'
                 group by tp.error_severity,
                          tp.sql_state_code,
                          tp.message
                 order by case when lower(tp.error_severity) = 'panic'    then 1 
                               when lower(tp.error_severity) = 'fatal'    then 2
                               when lower(tp.error_severity) = 'error'    then 3
                               when lower(tp.error_severity) = 'warning'  then 4
                               when lower(tp.error_severity) = 'deadlock' then 5
                               else 99
                           end,
                          case when tp.message  like '[____]%' then 99
                               else 1
                           end,    
                          cnt desc 
              )
              loop
                begin
                  v_sql := v_sql||'
    '||d_db.cnt||'    '||d_db.error_severity||'    '||d_db.sql_state_code||'    '||d_db.message||'    '||d_db.conn_ip;
    
                  --raise notice 'v_sql= %',v_sql;
                  v_error_severity_list := v_error_severity_list||d_db.error_severity||'##';
                  v_sql_state_code      := v_sql_state_code||d_db.sql_state_code||'##';
                  --execute v_sql;
                end; 
    
              end loop; 
    
              if ( length(v_sql) > 0 ) then
    
                  v_filename:='/tmp/pgsql_pglog_current_done/pglog_'||trim(replace(c_db.db_mon_ip,'.','_'))||'_'||trim(c_db.min_log_time_file)||'_'||trim(c_db.max_log_time_file)||'.csv';
    
    
                  execute ' 
                  copy (
                         select *
                           from public.t_gather_pgsql_log_part tp
                          where 1=1
                            and tp.db_num = '''||c_db.db_num||'''    
                            and tp.db_ip = '''||c_db.db_mon_ip||'''
                            and tp.error_severity in ( select distinct str_tab from (select regexp_split_to_table('''||v_error_severity_list||''',''##'') as str_tab ) t0 where str_tab is not null and str_tab <> '''' )
                            and tp.sql_state_code in ( select distinct str_tab from (select regexp_split_to_table('''||v_sql_state_code||''',''##'') as str_tab ) t0 where str_tab is not null and str_tab <> '''' )
                            and tp.message not like ''[____]%''
                          order by tp.log_time 
                       )
                  to '''||v_filename||''' 
                  CSV HEADER
                  ';
    
    
                  perform sp_util_exception_writelog(
                     c_db.db_num,
                     c_db.db_mon_ip,
                     now(),
                     'log',
    
                     v_sp_name,
                     (c_db.min_log_time||' ~ '||c_db.max_log_time)::varchar,
                     v_sql::text,
                     'n20'::varchar,
                     'dba,sa,kfa,ita,csa'::varchar,
                     --'dba'::varchar,
                     v_filename
                   );
              end if;
    
          EXCEPTION 
              WHEN others then
    
              --raise notice 'v_sql= %',v_sql;
    
              perform sp_util_exception_writelog(
                 c_db.db_num,
                 c_db.db_mon_ip,
                 now(),
                 'log',
    
                 v_sp_name,
                 --(c_db.min_log_time||' ~ '||c_db.max_log_time)::varchar,
                 --v_sql::text,
                 sqlstate::varchar,
                 sqlerrm::text,
                 'n20'::varchar,
                 'dba'::varchar
               );
    
          END;
        end loop;
    
    
        RETURN;
    end;
    $function$
    
  • 相关阅读:
    Ubuntu在命令行开启远程桌面
    Qt5编译项目出现GL/gl.h:No such file or directory错误
    硬盘录像机协议与技术汇总
    js判断IP字符串是否正确
    PHP获取原生POST数据
    hdu 5093 二分匹配
    hdu 4435 bfs+贪心
    hdu 4431 绝对值之和最小公式
    hdu 5073 推公式相邻质心转换
    hdu 3657 最小割(牛逼!!!!)总算理解了
  • 原文地址:https://www.cnblogs.com/ctypyb2002/p/9792961.html
Copyright © 2020-2023  润新知