{"id":294650,"date":"2019-09-13T09:00:57","date_gmt":"2019-09-13T09:00:57","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=294650"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=294650","title":{"rendered":"pg_stat_statements + pg_stat_activity + loq_query = pg_ash?"},"content":{"rendered":"\n<div class=\"post__text post__text-html js-mediator-article\"><i>\u0412 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u043a\u043e\u0440\u043e\u0442\u043a\u043e\u0433\u043e \u0434\u043e\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043a \u0441\u0442\u0430\u0442\u044c\u0435 <a href=\"https:\/\/habr.com\/ru\/post\/467181\/\">\u041f\u043e\u043f\u044b\u0442\u043a\u0430 \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0430\u043d\u0430\u043b\u043e\u0433 ASH \u0434\u043b\u044f PostgreSQL<\/a>.<\/i><\/p>\n<h1>\u0417\u0430\u0434\u0430\u0447\u0430.<\/h1>\n<p>  \u041d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u0432\u044f\u0437\u0430\u0442\u044c \u0438\u0441\u0442\u043e\u0440\u0438\u044e \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0439 pg_stat_statemenets, pg_stat_activity. \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0440\u0438\u044e \u043f\u043b\u0430\u043d\u043e\u0432 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0438\u0437 \u0441\u0435\u0440\u0432\u0438\u0441\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b log_query, \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043e\u0447\u0435\u043d\u044c \u043c\u043d\u043e\u0433\u043e \u043f\u043e\u043b\u0435\u0437\u043d\u043e\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438, \u0434\u043b\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0432 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u0438\u043d\u0446\u0438\u0434\u0435\u043d\u0442\u043e\u0432 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0438 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432.  <\/p>\n<blockquote><p>\u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435.<br \/>  <i>\u0412 \u0441\u0432\u044f\u0437\u0438 \u0441 \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0435\u043d\u0438\u0435\u043c \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0438 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0438, \u0441\u0442\u0430\u0442\u044c\u044f \u043d\u0435 \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u0435\u0442\u0435\u043d\u0434\u043e\u0432\u0430\u0442\u044c \u043d\u0430 \u043e\u043f\u0438\u0441\u0430\u043d\u0438\u0435 \u0433\u043e\u0442\u043e\u0432\u043e\u0433\u043e \u043f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u043e\u0433\u043e \u0440\u0435\u0448\u0435\u043d\u0438\u044f. <br \/>  \u041a\u0440\u0438\u0442\u0438\u043a\u0430 \u0438 \u0437\u0430\u043c\u0435\u0447\u0430\u043d\u0438\u044f \u043f\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 \u0432\u0441\u044f\u0447\u0435\u0441\u043a\u0438 \u043f\u0440\u0438\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u044e\u0442\u0441\u044f \u0438 \u043e\u0436\u0438\u0434\u0430\u044e\u0442\u0441\u044f.<\/i><\/p><\/blockquote>\n<p>  <a name=\"habracut\"><\/a>  <\/p>\n<h2>\u0412\u0445\u043e\u0434\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435.<\/h2>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u0422\u0430\u0431\u043b\u0438\u0446\u0430 history_pg_stat_activity<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">--ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity; CREATE TABLE activity_hist.history_pg_stat_activity (   timepoint timestamp without time zone ,   datid             oid  ,    datname           name ,   pid               integer,   usesysid          oid    ,   usename           name   ,   application_name  text   ,   client_addr       inet   ,   client_hostname   text   ,   client_port       integer,   backend_start     timestamp without time zone ,   xact_start        timestamp without time zone ,   query_start       timestamp without time zone ,   state_change      timestamp without time zone ,   wait_event_type   text ,                        wait_event        text ,                      state             text ,                     backend_xid       xid  ,                    backend_xmin      xid  ,                   query             text ,                  backend_type      text ,   queryid           bigint );<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u0422\u0430\u0431\u043b\u0438\u0446\u0430 pg_stat_db_queries<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">CREATE TABLE pg_stat_db_queries (   database_id integer ,   queryid bigint ,   query text ,   max_time double precision );(<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u041c\u0430\u0442\u0435\u0440\u0438\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u043d\u043e\u0435 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435 mvw_pg_stat_queries<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">CREATE MATERIALIZED VIEW public.mvw_pg_stat_queries AS  SELECT t.queryid,     t.max_time,     t.query    FROM public.dblink('LINK1'::text, 'SELECT queryid , max_time , query FROM pg_stat_statements WHERE dbid=(SELECT oid FROM  pg_database WHERE datname=current_database() ) AND max_time &gt;= 0 '::text) t(queryid bigint, max_time double precision, query text)   WITH NO DATA; <\/code><\/pre>\n<\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u0422\u0430\u0431\u043b\u0438\u0446\u0430 log_query<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">CREATE TABLE log_query (   id  integer ,   queryid  bigint ,   query_md5hash  text ,   database_id  integer ,   timepoint  timestamp without time zone ,    query  text ,   explained_plan  text[] ,    plan_md5hash  text ,   explained_plan_wo_costs  text[] ,    plan_hash_value  text ,   ip  text,   port  text ,    pid  integer  );<\/code><\/pre>\n<\/div>\n<\/div>\n<h2>\u041e\u0431\u0449\u0438\u0439 \u0430\u043b\u0433\u043e\u0440\u0438\u0442\u043c<\/h2>\n<p>  <\/p>\n<h3>\u041e\u0431\u043d\u043e\u0432\u0438\u0442\u044c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 pg_stat_db_queries<\/h3>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u041e\u0431\u043d\u043e\u0432\u0438\u0442\u044c \u043c\u0430\u0442\u0435\u0440\u0438\u0430\u043b\u044c\u043d\u043e\u0435 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435 mvw_pg_stat_queries<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">CREATE OR REPLACE FUNCTION refresh_pg_stat_queries_list( database_id int) RETURNS BOOLEAN AS $$ DECLARE  result BOOLEAN ;  database_rec record ;   BEGIN      SELECT *   INTO database_rec   FROM endpoint e JOIN database d ON e.id = d.endpoint_id    WHERE d.id = database_id  ;        IF NOT database_rec.is_need_monitoring THEN RAISE NOTICE 'NO NEED MONITORING FOR database_id=%',database_id; return TRUE ; END IF ;      EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' port=5432 dbname='||database_rec.name|| \t\t                                         ' user='||database_rec.s_name||' password='||database_rec.s_pass|| ' '')';       REFRESH MATERIALIZED VIEW mvw_pg_stat_queries ;      PERFORM dblink_disconnect('LINK1');      RETURN result; END $$ LANGUAGE plpgsql;<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u0417\u0430\u043f\u043e\u043b\u043d\u0438\u0442\u044c \u0442\u0430\u0431\u043b\u0438\u0446\u0443 pg_stat_db_queries<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">CREATE OR REPLACE FUNCTION refresh_pg_stat_db_queries( ) RETURNS BOOLEAN AS $$ DECLARE  result BOOLEAN ;  database_rec record ;    pg_stat_rec record ; BEGIN    TRUNCATE pg_stat_db_queries;         FOR database_rec IN   SELECT *   FROM database d    LOOP        IF NOT database_rec.is_need_monitoring THEN RAISE NOTICE 'NO NEED MONITORING FOR database_id=%',database_rec.id; CONTINUE ; END IF ;         PERFORM refresh_pg_stat_queries_list( database_rec.id ) ;  \t \tFOR pg_stat_rec IN \tSELECT *  \tFROM mvw_pg_stat_queries  \tLOOP \t  INSERT INTO pg_stat_db_queries \t  ( database_id , queryid , query , max_time ) \t  VALUES \t  ( database_rec.id , pg_stat_rec.queryid , pg_stat_rec.query , pg_stat_rec.max_time); \tEND LOOP;        END LOOP;     RETURN TRUE; END $$ LANGUAGE plpgsql;<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u0442 \u043d\u043e\u0440\u043c\u0430\u043b\u0438\u0437\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u0442\u0435\u043a\u0441\u0442\u044b \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, queryid, \u043c\u0430\u043a\u0441\u0438\u043c\u0430\u043b\u044c\u043d\u043e\u0435 \u0432\u0440\u0435\u043c\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u0430 \u043d\u0430 \u0442\u0435\u043a\u0443\u0449\u0438\u0439 \u043c\u043e\u043c\u0435\u043d\u0442 (\u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0434\u043b\u044f \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430).<\/p>\n<h3>\u0417\u0430\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435 log_query \u0438 \u0444\u043e\u0440\u043c\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u043f\u043b\u0430\u043d\u043e\u0432 \u0432\u044b\u043f\u043e\u043b\u0435\u043d\u0438\u044f.<\/h3>\n<p>  \u0410\u043a\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0439 \u0442\u0435\u043a\u0441\u0442 \u0437\u0430\u043f\u0440\u043e\u0441\u0430 \u0431\u0435\u0440\u0435\u0442\u0441\u044f \u0438\u0437 log-\u0444\u0430\u0439\u043b\u0430. Log-\u0444\u0430\u0439\u043b \u0441 \u0446\u0435\u043b\u0435\u0432\u043e\u0433\u043e \u0445\u043e\u0441\u0442\u0430 \u043d\u0430 \u0445\u043e\u0441\u0442 \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 \u043f\u043e \u0447\u0430\u0441\u0442\u044f\u043c, bash \u0441\u043a\u0440\u0438\u043f\u0442\u043e\u043c, \u043f\u043e cron. \u0414\u043b\u044f \u044d\u043a\u043e\u043d\u043e\u043c\u0438\u0438 \u043c\u0435\u0441\u0442\u0430 \u0438 \u0432 \u0441\u0432\u044f\u0437\u0438 \u0441 \u0442\u0440\u0438\u0432\u0438\u0430\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0437\u0430\u0434\u0430\u0447\u0438 \u043a\u043e\u043f\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u043a\u0443\u0441\u043a\u0430 \u0442\u0435\u043a\u0441\u0442\u043e\u0432\u043e\u0433\u043e \u0444\u0430\u0439\u043b\u0430 \u0441 \u0445\u043e\u0441\u0442\u0430 \u043d\u0430 \u0445\u043e\u0441\u0442 \u0441\u043a\u0440\u0438\u043f\u0442 \u043d\u0435 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u0441\u044f.  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u041f\u0430\u0440\u0441\u0438\u043d\u0433 log-\u0444\u0430\u0439\u043b\u0430 \u0438 \u0432\u044b\u0434\u0435\u043b\u0435\u043d\u0438\u0435 \u0442\u0435\u043a\u0441\u0442\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u0430<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"bash\">#!\/bin\/bash ######################################################### # upload_log_query.sh # Upload table table from dowloaded aws file  # version 12.0 ###########################################################   echo 'TIMESTAMP:'$(date +%c)' Upload log_query table '  source_file=$1 echo 'source_file='$source_file  database_id=$2 echo 'database_id='$database_id  database_name=$3 echo 'database_name='$database_name   beginer=' ' first_line='1' let \"line_count=0\" sql_line=' ' sql_flag=' '     space=' ' cat $source_file | while read line do   #first line will be passed   if [[ $line_count == '0' ]]; then      let \"line_count++\"      continue    fi      line=\"$space$line\"   #echo 'line='$line    if [[ $first_line == \"1\" ]]; then     beginer=`echo $line | awk -F\" \" '{ print $1}' `     first_line='0'   fi    current_beginer=`echo $line | awk -F\" \" '{ print $1}' `   #echo 'current_beginer='$current_beginer   #echo 'beginer='$beginer       if [[ $current_beginer == $beginer ]]; then    if [[ $sql_flag == '1' ]]; then      sql_flag='0'       #echo 'TIMESTAMP:'$(date +%c)' Upload log_query table : SQL STATEMENT ='\"$sql_line\"       log_date=`echo $sql_line | awk -F\" \" '{ print $1}' `      #echo 'log_date='$log_date       log_time=`echo $sql_line | awk -F\" \" '{ print $2}' `      #echo 'log_time='$log_time            duration=`echo $sql_line | awk -F\" \" '{ print $5}' `      #echo 'duration='$duration  \t connect=`echo $sql_line | awk -F\" \" '{ print $3}' ` \t userdb=`echo $connect | awk -F\":\" '{ print $3}' ` \t userdb2=$userdb'@' \t db_port_log=`echo $connect | awk -F\"@\" '{ print $2}' ` \t log_database_name=`echo $db_port_log | awk -F\":\" '{ print $1}' ` \t  \t  \t #echo 'connect='$connect \t #echo 'userdb='$userdb \t #echo 'userdb2='$userdb2 \t #echo 'db_port_log='$db_port_log \t #echo 'log_database_name='$log_database_name \t  \t if [[  \"$log_database_name\" != \"$database_name\" ]]; \t then \t   echo '*** database_name '$log_database_name' from log is not equal '$database_name' CONTINUE ' \t   continue; \t fi \t       #replace ' to ''      sql_modline=`echo \"$sql_line\" | sed 's\/'\\''\/'\\'''\\''\/g'`      sql_line=' ' \t #echo '*********************************log_query start'      #echo 'pid_str='$pid_str  \t #echo 'ip_port='$ip_port  \t #echo 'database_id='$database_id  \t #echo 'log_date='$log_date \t #echo 'log_time='$log_time \t #echo 'duration='$duration \t #echo 'sql_modline='$sql_modline      if ! psql -U monitor -d monitor -v ON_ERROR_STOP=1 -A -t -q -c \"select log_query( '$pid_str' , '$ip_port' , $database_id , '$log_date' , '$log_time' , '$duration' , '$sql_modline' )\"      then         echo 'FATAL_ERROR - log_query '         exit 1      fi \t #echo '**********************************log_query finish'      fi #if [[ $sql_flag == '1' ]]; then      let \"line_count=line_count+1\"     #echo 'line_count= '$line_count     #echo $line      #check=`echo $line | awk -F\" \" '{ print $8}' `     #check_sql=${check^^}          #echo 'check_sql='$check_sql      \tif [[ ${line^^} =~ \"SELECT\" ]];  \tthen  \t if [[ $line =~ \"duration:\" ]]; \t then \t    test_statement=`echo $line | awk -F\" \" '{ print $8}'`  \t\tis_select=${test_statement^^}\t\t \t\t \t\t#echo 'test_statement='$test_statement \t\t#echo 'is_select='$is_select \t\t         if [[ $is_select == 'SELECT' ]];          then\t\t \t\t  sql_flag='1'     \t\t  sql_line=\"$sql_line$line\" \t\t  ip_port=`echo $sql_line | awk -F\":\" '{ print $4}' ` \t\t  pid_str=`echo $sql_line | awk -F\":\" '{ print $6}' ` \t\tfi \t fi\t      fi   else            #echo $line     #echo 'sql_flag ='$sql_flag      if [[ $sql_flag == '1' ]]; then       sql_line=\"$sql_line$line\"     fi           fi #if [[ $current_beginer == $beginer ]]; then  done<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">\u0417\u0430\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b log_query<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">--log_query.sql --insert new query into log_query table CREATE OR REPLACE FUNCTION log_query( pid_str text , ip_port text ,log_database_id integer , log_date text , log_time text , duration text , sql_line text   ) RETURNS boolean AS $$ DECLARE   result boolean ;   log_timepoint timestamp without time zone ;   log_duration double precision ;    pos integer ;   log_query text ;   activity_string text ;   log_md5hash text ;   log_explain_plan text[] ;      log_planhash text ;   log_plan_wo_costs text[] ;       database_rec record ;      pg_stat_query text ;    test_log_query text ;   metric_rec record;   log_query_rec record;   found_flag boolean;      pg_stat_history_rec record ;   port_start integer ;   port_end integer ;   client_ip text ;   client_port text ;   log_queryid bigint ;   log_query_text text ;   pg_stat_query_text text ;    current_pid_str text ;   current_pid integer;   pid_start_pos integer ;   pid_finish_pos integer ;    BEGIN   result = TRUE ;          IF ip_port != '[local]' THEN     port_start = position('(' in ip_port);     port_end = position(')' in ip_port);     client_ip = substring( ip_port from 1 for port_start-1 );     client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );   ELSE      client_ip = 'local'; \tclient_port = 'local';   END IF;       pid_start_pos = position('[' in pid_str);   pid_finish_pos = position(']' in pid_str);   current_pid_str=substring( pid_str from 2 for pid_finish_pos - pid_start_pos -1 );   current_pid = to_number(current_pid_str , '999999999999');      SELECT e.host , d.name , d.owner_pwd , d.owner_user   INTO database_rec   FROM database d JOIN endpoint e ON e.id = d.endpoint_id   WHERE d.id = log_database_id ;      log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');   log_duration = duration::double precision ;         pos = position ('SELECT' in UPPER(sql_line) );   log_query = substring( sql_line from pos for LENGTH(sql_line));   log_query = regexp_replace(log_query,' +',' ','g');   log_query = regexp_replace(log_query,';+','','g');   log_query = trim(trailing ' ' from log_query);      log_md5hash = md5( log_query::text );      --Explain execution plan-- EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' port=5432 dbname='||database_rec.name||' user='||database_rec.owner_user||' password='||database_rec.owner_pwd||' '')';       log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );   log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );      PERFORM dblink_disconnect('LINK1');   --------------------------   BEGIN \tINSERT INTO log_query \t( \t\tquery_md5hash , \t\tdatabase_id ,  \t\ttimepoint , \t\tduration , \t\tquery , \t\texplained_plan , \t\tplan_md5hash ,  \t\texplained_plan_wo_costs ,  \t\tplan_hash_value ,  \t\tip ,  \t\tport , \t\tpid \t)  \tVALUES  \t( \t\tlog_md5hash , \t\tlog_database_id ,  \t\tlog_timepoint ,  \t\tlog_duration ,  \t\tlog_query , \t\tlog_explain_plan ,  \t\tmd5(log_explain_plan::text) , \t\tlog_plan_wo_costs ,  \t\tmd5(log_plan_wo_costs::text), \t\tclient_ip ,  \t\tclient_port ,  \t\tcurrent_pid \t\t \t); \tactivity_string = \t'New query has logged '|| \t\t\t\t\t\t' database_id = '|| log_database_id || \t\t\t\t\t\t' query_md5hash='||log_md5hash|| \t\t\t\t\t\t' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS'); \tPERFORM pg_log( log_database_id , 'log_query' , activity_string);    \tEXCEPTION \t  WHEN unique_violation THEN \t\tactivity_string = \t'EXCEPTION *** query already has logged '|| \t\t\t\t\t\t\t' database_id = '|| log_database_id || \t\t\t\t\t\t\t' query_md5hash='||log_md5hash|| \t\t\t\t\t\t\t' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');\t\t\t\t\t          PERFORM pg_log( log_database_id , 'log_query' , activity_string); \tEND;  \tSELECT \tqueryid \tINTO   \tlog_queryid \tFROM \tlog_query  \tWHERE \tquery_md5hash = log_md5hash AND \t\t\ttimepoint = log_timepoint;  \tIF log_queryid IS NOT NULL  \tTHEN  \t  RETURN result; \tEND IF; \t \t------------------------------------------------ \tSELECT *  \tINTO log_query_rec \tFROM log_query \tWHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;  \t \tlog_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');  \tFOR pg_stat_history_rec IN \t SELECT              queryid ,   \t    query  \t FROM         pg_stat_db_queries       WHERE   \t   database_id = log_database_id AND        queryid is not null  \tLOOP \t  pg_stat_query = pg_stat_history_rec.query ;  \t  pg_stat_query=regexp_replace(pg_stat_query,'\\n+',' ','g'); \t  pg_stat_query=regexp_replace(pg_stat_query,'\\t+',' ','g'); \t  pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g'); \t  pg_stat_query=regexp_replace(pg_stat_query,'\\$.','%','g'); \t \t  log_query_text = trim(trailing ' ' from log_query_rec.query); \t  pg_stat_query_text = pg_stat_query;    \t   \t  IF (log_query_text LIKE pg_stat_query_text) THEN \t\tfound_flag = TRUE ; \t  ELSE \t\tfound_flag = FALSE ; \t  END IF;\t    \t  IF found_flag THEN \t     \t\tUPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ; \t\tactivity_string = \t' updated queryid = '||pg_stat_history_rec.queryid|| \t\t                    ' for log_query with id = '||log_query_rec.id;\t\t\t\t\t \t\tEXIT ; \t  END IF ;\t   \tEND LOOP ;\t   RETURN result ; END $$ LANGUAGE plpgsql;<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u0442 \u0430\u043a\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0439 \u0442\u0435\u043a\u0441\u0442 \u0437\u0430\u043f\u0440\u043e\u0441\u0430, \u043f\u043b\u0430\u043d\u044b \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f, \u0445\u044d\u0448-\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u043f\u043b\u0430\u043d\u0430 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f, \u0445\u044d\u0448-\u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 \u0442\u0435\u043a\u0441\u0442\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u0430.  <\/p>\n<h3>\u0417\u0430\u043f\u043e\u043b\u043d\u0438\u0442\u044c \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 queryid \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u0435 history_pg_stat_activity<\/h3>\n<p>  <\/p>\n<div class=\"spoiler\"><b class=\"spoiler_title\">update_history_pg_stat_activity_by_queryid.sql<\/b><\/p>\n<div class=\"spoiler_text\">\n<pre><code class=\"pgsql\">--update_history_pg_stat_activity_by_queryid.sql CREATE OR REPLACE FUNCTION update_history_pg_stat_activity_by_queryid() RETURNS boolean AS $$ DECLARE   result boolean ;   history_pg_stat_activity_rec record ;    pg_stat_query text ;   pg_stat_query_text text ;   pg_stat_history_rec record;   found_flag boolean;   history_pg_stat_activity_query text ;    query_text text ;   activity_string text ;     BEGIN   RAISE NOTICE '***update_history_pg_stat_activity_by_queryid';      result = TRUE ;      FOR history_pg_stat_activity_rec IN    SELECT DISTINCT(query) AS query   FROM activity_hist.history_pg_stat_activity   WHERE queryid IS NULL   LOOP \t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_rec.query,'\\n+',' ','g'); \t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,'\\t+',' ','g'); \t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,' +',' ','g'); \t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,';','','g'); \t\tquery_text = trim(trailing ' ' from history_pg_stat_activity_query); \t\t \t\tFOR pg_stat_history_rec IN \t\tSELECT  \t\t\tqueryid , \t\t\tquery  \t\tFROM  \t\t\t--pg_stat_history \t\t\tpg_stat_db_queries \t\tWHERE   \t\t\tqueryid is not null  \t\tGROUP BY queryid ,\tquery  \t\tLOOP \t\t\tpg_stat_query = pg_stat_history_rec.query ;  \t\t\tpg_stat_query=regexp_replace(pg_stat_query,'\\n+',' ','g'); \t\t\tpg_stat_query=regexp_replace(pg_stat_query,'\\t+',' ','g'); \t\t\tpg_stat_query=regexp_replace(pg_stat_query,' +',' ','g'); \t\t\tpg_stat_query=regexp_replace(pg_stat_query,'\\$.','%','g');\t \t\t\t \t\t\tpg_stat_query_text = pg_stat_query;  \t   \t\t\tIF (query_text LIKE pg_stat_query_text) THEN \t\t\t\tfound_flag = TRUE ; \t\t\tELSE \t\t\t\tfound_flag = FALSE ; \t\t\tEND IF;\t   \t\t\t \t\t\tIF found_flag  \t\t\tTHEN \t\t\t\tUPDATE activity_hist.history_pg_stat_activity \t\t\t\tSET queryid = pg_stat_history_rec.queryid \t\t\t\tWHERE regexp_replace(regexp_replace(regexp_replace(regexp_replace(query,'\\n+',' ','g'),'\\t+',' ','g'),' +',' ','g'),';','','g')  \t\t\t\t      LIKE query_text||'%' ; \t\t \t\t\t\tactivity_string = \t'history_pg_stat_activity has updated by queryid = '||pg_stat_history_rec.queryid; \t\t\t\tRAISE NOTICE '%',activity_string;\t \t\t\t\t \t\t\t\tPERFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , activity_string);  \t\t\t\t \t\t\t\tEXIT ; \t\t\t\t \t\t\tEND IF ;\t   \t\tEND LOOP ; \t\t \t\tIF NOT found_flag  \t\tTHEN \t\t\tactivity_string = 'WARNING : Not FOUND queryid for the query : '||query_text ; \t\t\t \t\t\tRAISE NOTICE '%',activity_string;\t \t\t\t\t \t\t\tPERFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , activity_string);  \t\tEND IF ; \t\t \tRAISE NOTICE 'UPDATE log_query if query has not logged in log-file'; \t\t   END LOOP;      RETURN result ; END $$ LANGUAGE plpgsql;<\/code><\/pre>\n<\/div>\n<\/div>\n<p>  \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u0430 \u0441\u043e\u0434\u0435\u0440\u0436\u0438\u0442 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435 queryid \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0435\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044e queryid \u0437\u0430\u043f\u0440\u043e\u0441\u0430.<\/p>\n<h1>\u0418\u0442\u043e\u0433.<\/h1>\n<p>  \u0421\u0432\u044f\u0437\u0430\u0432 pg_stat_activity, pg_stat_statements, log_query, \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043c\u043d\u043e\u0433\u043e \u043f\u043e\u043b\u0435\u0437\u043d\u043e\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438 \u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0435, \u0432 \u0447\u0430\u0441\u0442\u043d\u043e\u0441\u0442\u0438:<br \/>  -\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043f\u043b\u0430\u043d\u043e\u0432 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f.<br \/>  -\u0418\u0441\u0442\u043e\u0440\u0438\u044f CPU-time \u0437\u0430\u043f\u0440\u043e\u0441\u0430.<br \/>  -\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e\u0436\u0438\u0434\u0430\u043d\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441\u0430.<br \/>  \u0414\u0430\u043d\u043d\u044b\u0435 \u0438 \u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u043e \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0445 \u043e\u0442\u0447\u0435\u0442\u043e\u0432, \u0431\u0443\u0434\u0443\u0442 \u043e\u043f\u0438\u0441\u0430\u043d\u044b \u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435.<\/p>\n<h1>\u0420\u0430\u0437\u0432\u0438\u0442\u0438\u0435.<\/h1>\n<p>  \u0421\u0432\u044f\u0437\u0430\u0432 \u0438\u043c\u0435\u044e\u0449\u0443\u044e\u0441\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u0441 \u0438\u0441\u0442\u043e\u0440\u0438\u0435\u0439 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f pg_locks \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044e \u043e \u0442\u043e\u043c, \u043a\u0430\u043a\u043e\u0439 \u043a\u043e\u043d\u043a\u0440\u0435\u0442\u043d\u043e \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0438 \u0436\u0434\u0430\u043b \u0437\u0430\u043f\u0440\u043e\u0441 \u0438 \u0441\u0430\u043c\u043e\u0435 \u0433\u043b\u0430\u0432\u043d\u043e\u0435 \u043a\u0430\u043a\u043e\u0439 \u043f\u0440\u043e\u0446\u0435\u0441\u0441(\u0437\u0430\u043f\u0440\u043e\u0441) \u0443\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u043b \u044d\u0442\u0443 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0443. <br \/>  \u0420\u0435\u0448\u0435\u043d\u0438\u0435 \u044d\u0442\u043e\u0439 \u0437\u0430\u0434\u0430\u0447\u0438 \u0431\u0443\u0434\u0435\u0442 \u043e\u043f\u0438\u0441\u0430\u043d\u043e \u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435. \u0421\u0435\u0439\u0447\u0430\u0441 \u0438\u0434\u0435\u0442 \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438 \u0434\u043e\u0440\u0430\u0431\u043e\u0442\u043a\u0430.<\/div>\n<p>               <script class=\"js-mediator-script\">!function(e){function t(t,n){if(!(n in e)){for(var r,a=e.document,i=a.scripts,o=i.length;o--;)if(-1!==i[o].src.indexOf(t)){r=i[o];break}if(!r){r=a.createElement(\"script\"),r.type=\"text\/javascript\",r.async=!0,r.defer=!0,r.src=t,r.charset=\"UTF-8\";var d=function(){var e=a.getElementsByTagName(\"script\")[0];e.parentNode.insertBefore(r,e)};\"[object Opera]\"==e.opera?a.addEventListener?a.addEventListener(\"DOMContentLoaded\",d,!1):e.attachEvent(\"onload\",d):d()}}}t(\"\/\/mediator.mail.ru\/script\/2820404\/\",\"_mediator\")}(window);<\/script>     <br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/post\/467277\/\"> https:\/\/habr.com\/ru\/post\/467277\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"\n<div class=\"post__text post__text-html js-mediator-article\"><i>\u0412 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u043a\u043e\u0440\u043e\u0442\u043a\u043e\u0433\u043e \u0434\u043e\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043a \u0441\u0442\u0430\u0442\u044c\u0435 <a href=\"https:\/\/habr.com\/ru\/post\/467181\/\">\u041f\u043e\u043f\u044b\u0442\u043a\u0430 \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0430\u043d\u0430\u043b\u043e\u0433 ASH \u0434\u043b\u044f PostgreSQL<\/a>.<\/i><\/p>\n<h1>\u0417\u0430\u0434\u0430\u0447\u0430.<\/h1>\n<p>  \u041d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0441\u0432\u044f\u0437\u0430\u0442\u044c \u0438\u0441\u0442\u043e\u0440\u0438\u044e \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0439 pg_stat_statemenets, pg_stat_activity. \u0412 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u0435, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u0438\u0441\u0442\u043e\u0440\u0438\u044e \u043f\u043b\u0430\u043d\u043e\u0432 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0438\u0437 \u0441\u0435\u0440\u0432\u0438\u0441\u043d\u043e\u0439 \u0442\u0430\u0431\u043b\u0438\u0446\u044b log_query, \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043e\u0447\u0435\u043d\u044c \u043c\u043d\u043e\u0433\u043e \u043f\u043e\u043b\u0435\u0437\u043d\u043e\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438, \u0434\u043b\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u0432 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f \u0438\u043d\u0446\u0438\u0434\u0435\u043d\u0442\u043e\u0432 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0438 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432.  <\/p>\n<blockquote><p>\u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435.<br \/>  <i>\u0412 \u0441\u0432\u044f\u0437\u0438 \u0441 \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0435\u043d\u0438\u0435\u043c \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0438 \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u043a\u0438, \u0441\u0442\u0430\u0442\u044c\u044f \u043d\u0435 \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u0435\u0442\u0435\u043d\u0434\u043e\u0432\u0430\u0442\u044c \u043d\u0430 \u043e\u043f\u0438\u0441\u0430\u043d\u0438\u0435 \u0433\u043e\u0442\u043e\u0432\u043e\u0433\u043e \u043f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u043e\u0433\u043e \u0440\u0435\u0448\u0435\u043d\u0438\u044f. <br \/>  \u041a\u0440\u0438\u0442\u0438\u043a\u0430 \u0438 \u0437\u0430\u043c\u0435\u0447\u0430\u043d\u0438\u044f \u043f\u043e \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438 \u0432\u0441\u044f\u0447\u0435\u0441\u043a\u0438 \u043f\u0440\u0438\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u044e\u0442\u0441\u044f \u0438 \u043e\u0436\u0438\u0434\u0430\u044e\u0442\u0441\u044f.<\/i><\/p><\/blockquote>\n<p>  <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-294650","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/294650","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=294650"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/294650\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=294650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=294650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=294650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}