{"id":38131,"date":"2019-10-31T22:21:49","date_gmt":"2019-10-31T19:21:49","guid":{"rendered":"https:\/\/prohoster.info\/blog\/pg_stat_statements-pg_stat_activity-loq_query-pg_ash\/"},"modified":"2019-10-31T22:21:49","modified_gmt":"2019-10-31T19:21:49","slug":"pg_stat_statements-pg_stat_activity-loq_query-pg_ash","status":"publish","type":"post","link":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/pg_stat_statements-pg_stat_activity-loq_query-pg_ash","title":{"rendered":"pg_stat_statements + pg_stat_activity + loq_query = pg_ash?","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><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 <noindex><a rel=\"nofollow\" 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><\/noindex>.<\/i><\/p>\n<h1>\u0417\u0430\u0434\u0430\u0447\u0430<\/h1>\n<p>\n\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.<\/p>\n<p><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. <\/p>\n<p>\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><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>\u0412\u0445\u043e\u0434\u043d\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435<\/h2>\n<p>\n<b class=\"spoiler_title\">\u0422\u0430\u0431\u043b\u0438\u0446\u0430 history_pg_stat_activity<\/b><\/p>\n<pre><code class=\"pgsql\">--ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY\nDROP TABLE IF EXISTS activity_hist.history_pg_stat_activity;\nCREATE TABLE activity_hist.history_pg_stat_activity\n(\n  timepoint timestamp without time zone ,\n  datid             oid  , \n  datname           name ,\n  pid               integer,\n  usesysid          oid    ,\n  usename           name   ,\n  application_name  text   ,\n  client_addr       inet   ,\n  client_hostname   text   ,\n  client_port       integer,\n  backend_start     timestamp without time zone ,\n  xact_start        timestamp without time zone ,\n  query_start       timestamp without time zone ,\n  state_change      timestamp without time zone ,\n  wait_event_type   text ,                     \n  wait_event        text ,                   \n  state             text ,                  \n  backend_xid       xid  ,                 \n  backend_xmin      xid  ,                \n  query             text ,               \n  backend_type      text ,\n  queryid           bigint\n);<\/code><\/pre>\n<p>\n<b class=\"spoiler_title\">\u0422\u0430\u0431\u043b\u0438\u0446\u0430 pg_stat_db_queries<\/b><\/p>\n<pre><code class=\"pgsql\">CREATE TABLE pg_stat_db_queries\n(\n  database_id integer ,\n  queryid bigint ,\n  query text ,\n  max_time double precision\n);(<\/code><\/pre>\n<p>\n<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<pre><code class=\"pgsql\">CREATE MATERIALIZED VIEW public.mvw_pg_stat_queries AS\n SELECT t.queryid,\n    t.max_time,\n    t.query\n   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)\n  WITH NO DATA;\n<\/code><\/pre>\n<p>\n<b class=\"spoiler_title\">\u0422\u0430\u0431\u043b\u0438\u0446\u0430 log_query<\/b><\/p>\n<pre><code class=\"pgsql\">CREATE TABLE log_query\n(\n  id  integer ,\n  queryid  bigint ,\n  query_md5hash  text ,\n  database_id  integer ,\n  timepoint  timestamp without time zone , \n  query  text ,\n  explained_plan  text[] , \n  plan_md5hash  text ,\n  explained_plan_wo_costs  text[] , \n  plan_hash_value  text ,\n  ip  text,\n  port  text , \n  pid  integer \n);<\/code><\/pre>\n<p><\/p>\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>\n<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<pre><code class=\"pgsql\">CREATE OR REPLACE FUNCTION refresh_pg_stat_queries_list( database_id int) RETURNS BOOLEAN AS $$\nDECLARE\n result BOOLEAN ;\n database_rec record ;  \nBEGIN   \n  SELECT *\n  INTO database_rec\n  FROM endpoint e JOIN database d ON e.id = d.endpoint_id \n  WHERE d.id = database_id  ;  \n  \n  IF NOT database_rec.is_need_monitoring THEN RAISE NOTICE 'NO NEED MONITORING FOR database_id=%',database_id; return TRUE ; END IF ;\n  \n  EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' port=5432 dbname='||database_rec.name||\n\t\t                                         ' user='||database_rec.s_name||' password='||database_rec.s_pass|| ' '')';\n   \n  REFRESH MATERIALIZED VIEW mvw_pg_stat_queries ;\n  \n  PERFORM dblink_disconnect('LINK1');  \n\n  RETURN result;\nEND\n$$ LANGUAGE plpgsql;<\/code><\/pre>\n<p>\n<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<pre><code class=\"pgsql\">CREATE OR REPLACE FUNCTION refresh_pg_stat_db_queries( ) RETURNS BOOLEAN AS $$\nDECLARE\n result BOOLEAN ;\n database_rec record ;  \n pg_stat_rec record ;\nBEGIN \n  TRUNCATE pg_stat_db_queries;\n  \n  \n  FOR database_rec IN\n  SELECT *\n  FROM database d \n  LOOP\n  \n    IF NOT database_rec.is_need_monitoring THEN RAISE NOTICE 'NO NEED MONITORING FOR database_id=%',database_rec.id; CONTINUE ; END IF ;\n   \n    PERFORM refresh_pg_stat_queries_list( database_rec.id ) ; \n\t\n\tFOR pg_stat_rec IN\n\tSELECT * \n\tFROM mvw_pg_stat_queries \n\tLOOP\n\t  INSERT INTO pg_stat_db_queries\n\t  ( database_id , queryid , query , max_time )\n\t  VALUES\n\t  ( database_rec.id , pg_stat_rec.queryid , pg_stat_rec.query , pg_stat_rec.max_time);\n\tEND LOOP;     \n  END LOOP; \n\n  RETURN TRUE;\nEND\n$$ LANGUAGE plpgsql;<\/code><\/pre>\n<p>\n\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>\n\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<p><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<pre><code class=\"bash\">#!\/bin\/bash\n#########################################################\n# upload_log_query.sh\n# Upload table table from dowloaded aws file \n# version 12.0\n###########################################################  \necho 'TIMESTAMP:'$(date +%c)' Upload log_query table '\n\nsource_file=$1\necho 'source_file='$source_file\n\ndatabase_id=$2\necho 'database_id='$database_id\n\ndatabase_name=$3\necho 'database_name='$database_name\n\n\nbeginer=' '\nfirst_line='1'\nlet \"line_count=0\"\nsql_line=' '\nsql_flag=' '    \nspace=' '\ncat $source_file | while read line\ndo\n  #first line will be passed\n  if [[ $line_count == '0' ]]; then \n    let \"line_count++\" \n    continue \n  fi\n  \n  line=\"$space$line\"\n  #echo 'line='$line\n\n  if [[ $first_line == \"1\" ]]; then\n    beginer=`echo $line | awk -F\" \" '{ print $1}' `\n    first_line='0'\n  fi\n\n  current_beginer=`echo $line | awk -F\" \" '{ print $1}' `\n  #echo 'current_beginer='$current_beginer\n  #echo 'beginer='$beginer\n  \n\n  if [[ $current_beginer == $beginer ]]; then\n   if [[ $sql_flag == '1' ]]; then\n     sql_flag='0' \n     #echo 'TIMESTAMP:'$(date +%c)' Upload log_query table : SQL STATEMENT ='\"$sql_line\"\n\n     log_date=`echo $sql_line | awk -F\" \" '{ print $1}' `\n     #echo 'log_date='$log_date\n\n     log_time=`echo $sql_line | awk -F\" \" '{ print $2}' `\n     #echo 'log_time='$log_time\n     \n     duration=`echo $sql_line | awk -F\" \" '{ print $5}' `\n     #echo 'duration='$duration\n\n\t connect=`echo $sql_line | awk -F\" \" '{ print $3}' `\n\t userdb=`echo $connect | awk -F\":\" '{ print $3}' `\n\t userdb2=$userdb'@'\n\t db_port_log=`echo $connect | awk -F\"@\" '{ print $2}' `\n\t log_database_name=`echo $db_port_log | awk -F\":\" '{ print $1}' `\n\t \n\t \n\t #echo 'connect='$connect\n\t #echo 'userdb='$userdb\n\t #echo 'userdb2='$userdb2\n\t #echo 'db_port_log='$db_port_log\n\t #echo 'log_database_name='$log_database_name\n\t \n\t if [[  \"$log_database_name\" != \"$database_name\" ]];\n\t then\n\t   echo '*** database_name '$log_database_name' from log is not equal '$database_name' CONTINUE '\n\t   continue;\n\t fi\n\t \n     #replace ' to ''\n     sql_modline=`echo \"$sql_line\" | sed 's\/'''\/''''''\/g'`\n     sql_line=' '\n\t #echo '*********************************log_query start'\n     #echo 'pid_str='$pid_str \n\t #echo 'ip_port='$ip_port \n\t #echo 'database_id='$database_id \n\t #echo 'log_date='$log_date\n\t #echo 'log_time='$log_time\n\t #echo 'duration='$duration\n\t #echo 'sql_modline='$sql_modline\n     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' )\"\n     then\n        echo 'FATAL_ERROR - log_query '\n        exit 1\n     fi\n\t #echo '**********************************log_query finish'\n\n    fi #if [[ $sql_flag == '1' ]]; then\n\n    let \"line_count=line_count+1\"\n    #echo 'line_count= '$line_count\n    #echo $line\n\n    #check=`echo $line | awk -F\" \" '{ print $8}' `\n    #check_sql=${check^^}    \n\n    #echo 'check_sql='$check_sql\n    \n\tif [[ ${line^^} =~ \"SELECT\" ]]; \n\tthen \n\t if [[ $line =~ \"duration:\" ]];\n\t then\n\t    test_statement=`echo $line | awk -F\" \" '{ print $8}'` \n\t\tis_select=${test_statement^^}\t\t\n\t\t\n\t\t#echo 'test_statement='$test_statement\n\t\t#echo 'is_select='$is_select\n\t\t\n        if [[ $is_select == 'SELECT' ]]; \n        then\t\t\n\t\t  sql_flag='1'    \n\t\t  sql_line=\"$sql_line$line\"\n\t\t  ip_port=`echo $sql_line | awk -F\":\" '{ print $4}' `\n\t\t  pid_str=`echo $sql_line | awk -F\":\" '{ print $6}' `\n\t\tfi\n\t fi\t \n    fi\n  else       \n    #echo $line\n    #echo 'sql_flag ='$sql_flag\n\n    if [[ $sql_flag == '1' ]]; then\n      sql_line=\"$sql_line$line\"\n    fi   \n    \n  fi #if [[ $current_beginer == $beginer ]]; then\n\ndone<\/code><\/pre>\n<p>\n<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<pre><code class=\"pgsql\">--log_query.sql\n--insert new query into log_query table\nCREATE 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 $$\nDECLARE\n  result boolean ;\n  log_timepoint timestamp without time zone ;\n  log_duration double precision ; \n  pos integer ;\n  log_query text ;\n  activity_string text ;\n  log_md5hash text ;\n  log_explain_plan text[] ;\n  \n  log_planhash text ;\n  log_plan_wo_costs text[] ; \n  \n  database_rec record ;\n  \n  pg_stat_query text ; \n  test_log_query text ;\n  metric_rec record;\n  log_query_rec record;\n  found_flag boolean;\n  \n  pg_stat_history_rec record ;\n  port_start integer ;\n  port_end integer ;\n  client_ip text ;\n  client_port text ;\n  log_queryid bigint ;\n  log_query_text text ;\n  pg_stat_query_text text ; \n  current_pid_str text ;\n  current_pid integer;\n  pid_start_pos integer ;\n  pid_finish_pos integer ;\n  \nBEGIN\n  result = TRUE ;    \n  \n  IF ip_port != '[local]' THEN\n    port_start = position('(' in ip_port);\n    port_end = position(')' in ip_port);\n    client_ip = substring( ip_port from 1 for port_start-1 );\n    client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );\n  ELSE \n    client_ip = 'local';\n\tclient_port = 'local';\n  END IF; \n  \n  pid_start_pos = position('[' in pid_str);\n  pid_finish_pos = position(']' in pid_str);\n  current_pid_str=substring( pid_str from 2 for pid_finish_pos - pid_start_pos -1 );\n  current_pid = to_number(current_pid_str , '999999999999');\n  \n  SELECT e.host , d.name , d.owner_pwd , d.owner_user\n  INTO database_rec\n  FROM database d JOIN endpoint e ON e.id = d.endpoint_id\n  WHERE d.id = log_database_id ;\n  \n  log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');\n  log_duration = duration::double precision ; \n    \n  pos = position ('SELECT' in UPPER(sql_line) );\n  log_query = substring( sql_line from pos for LENGTH(sql_line));\n  log_query = regexp_replace(log_query,' +',' ','g');\n  log_query = regexp_replace(log_query,';+','','g');\n  log_query = trim(trailing ' ' from log_query);\n  \n  log_md5hash = md5( log_query::text );\n  \n  --Explain execution plan--\nEXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' port=5432 dbname='||database_rec.name||' user='||database_rec.owner_user||' password='||database_rec.owner_pwd||' '')'; \n  \n  log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );\n  log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );\n  \n  PERFORM dblink_disconnect('LINK1');\n  --------------------------\n  BEGIN\n\tINSERT INTO log_query\n\t(\n\t\tquery_md5hash ,\n\t\tdatabase_id , \n\t\ttimepoint ,\n\t\tduration ,\n\t\tquery ,\n\t\texplained_plan ,\n\t\tplan_md5hash , \n\t\texplained_plan_wo_costs , \n\t\tplan_hash_value , \n\t\tip , \n\t\tport ,\n\t\tpid\n\t) \n\tVALUES \n\t(\n\t\tlog_md5hash ,\n\t\tlog_database_id , \n\t\tlog_timepoint , \n\t\tlog_duration , \n\t\tlog_query ,\n\t\tlog_explain_plan , \n\t\tmd5(log_explain_plan::text) ,\n\t\tlog_plan_wo_costs , \n\t\tmd5(log_plan_wo_costs::text),\n\t\tclient_ip , \n\t\tclient_port , \n\t\tcurrent_pid\n\t\t\n\t);\n\tactivity_string = \t'New query has logged '||\n\t\t\t\t\t\t' database_id = '|| log_database_id ||\n\t\t\t\t\t\t' query_md5hash='||log_md5hash||\n\t\t\t\t\t\t' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');\n\tPERFORM pg_log( log_database_id , 'log_query' , activity_string);  \n\n\tEXCEPTION\n\t  WHEN unique_violation THEN\n\t\tactivity_string = \t'EXCEPTION *** query already has logged '||\n\t\t\t\t\t\t\t' database_id = '|| log_database_id ||\n\t\t\t\t\t\t\t' query_md5hash='||log_md5hash||\n\t\t\t\t\t\t\t' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');\t\t\t\t\t \n        PERFORM pg_log( log_database_id , 'log_query' , activity_string);\n\tEND;\n\n\tSELECT \tqueryid\n\tINTO   \tlog_queryid\n\tFROM \tlog_query \n\tWHERE \tquery_md5hash = log_md5hash AND\n\t\t\ttimepoint = log_timepoint;\n\n\tIF log_queryid IS NOT NULL \n\tTHEN \n\t  RETURN result;\n\tEND IF;\n\t\n\t------------------------------------------------\n\tSELECT * \n\tINTO log_query_rec\n\tFROM log_query\n\tWHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ; \n\t\n\tlog_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');\n\n\tFOR pg_stat_history_rec IN\n\t SELECT \n            queryid ,\n  \t    query \n\t FROM \n       pg_stat_db_queries \n     WHERE  \n\t   database_id = log_database_id AND\n       queryid is not null \n\tLOOP\n\t  pg_stat_query = pg_stat_history_rec.query ; \n\t  pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');\n\t  pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');\n\t  pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');\n\t  pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');\n\t\n\t  log_query_text = trim(trailing ' ' from log_query_rec.query);\n\t  pg_stat_query_text = pg_stat_query;   \n\t  \n\t  IF (log_query_text LIKE pg_stat_query_text) THEN\n\t\tfound_flag = TRUE ;\n\t  ELSE\n\t\tfound_flag = FALSE ;\n\t  END IF;\t  \n\n\t  IF found_flag THEN\n\t    \n\t\tUPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;\n\t\tactivity_string = \t' updated queryid = '||pg_stat_history_rec.queryid||\n\t\t                    ' for log_query with id = '||log_query_rec.id;\t\t\t\t\t\n\t\tEXIT ;\n\t  END IF ;\t  \n\tEND LOOP ;\t\n  RETURN result ;\nEND\n$$ LANGUAGE plpgsql;<\/code><\/pre>\n<p>\n\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>\n<b class=\"spoiler_title\">update_history_pg_stat_activity_by_queryid.sql<\/b><\/p>\n<pre><code class=\"pgsql\">--update_history_pg_stat_activity_by_queryid.sql\nCREATE OR REPLACE FUNCTION update_history_pg_stat_activity_by_queryid() RETURNS boolean AS $$\nDECLARE\n  result boolean ;\n  history_pg_stat_activity_rec record ; \n  pg_stat_query text ;\n  pg_stat_query_text text ;\n  pg_stat_history_rec record;\n  found_flag boolean;\n  history_pg_stat_activity_query text ; \n  query_text text ;\n  activity_string text ; \n  \nBEGIN\n  RAISE NOTICE '***update_history_pg_stat_activity_by_queryid';\n  \n  result = TRUE ;\n  \n  FOR history_pg_stat_activity_rec IN \n  SELECT DISTINCT(query) AS query\n  FROM activity_hist.history_pg_stat_activity\n  WHERE queryid IS NULL\n  LOOP\n\t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_rec.query,'n+',' ','g');\n\t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,'t+',' ','g');\n\t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,' +',' ','g');\n\t\thistory_pg_stat_activity_query = regexp_replace(history_pg_stat_activity_query,';','','g');\n\t\tquery_text = trim(trailing ' ' from history_pg_stat_activity_query);\n\t\t\n\t\tFOR pg_stat_history_rec IN\n\t\tSELECT \n\t\t\tqueryid ,\n\t\t\tquery \n\t\tFROM \n\t\t\t--pg_stat_history\n\t\t\tpg_stat_db_queries\n\t\tWHERE  \n\t\t\tqueryid is not null \n\t\tGROUP BY queryid ,\tquery \n\t\tLOOP\n\t\t\tpg_stat_query = pg_stat_history_rec.query ; \n\t\t\tpg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');\n\t\t\tpg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');\n\t\t\tpg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');\n\t\t\tpg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');\t\n\t\t\t\n\t\t\tpg_stat_query_text = pg_stat_query; \n\t  \n\t\t\tIF (query_text LIKE pg_stat_query_text) THEN\n\t\t\t\tfound_flag = TRUE ;\n\t\t\tELSE\n\t\t\t\tfound_flag = FALSE ;\n\t\t\tEND IF;\t  \n\t\t\t\n\t\t\tIF found_flag \n\t\t\tTHEN\n\t\t\t\tUPDATE activity_hist.history_pg_stat_activity\n\t\t\t\tSET queryid = pg_stat_history_rec.queryid\n\t\t\t\tWHERE regexp_replace(regexp_replace(regexp_replace(regexp_replace(query,'n+',' ','g'),'t+',' ','g'),' +',' ','g'),';','','g') \n\t\t\t\t      LIKE query_text||'%' ;\n\t\t\n\t\t\t\tactivity_string = \t'history_pg_stat_activity has updated by queryid = '||pg_stat_history_rec.queryid;\n\t\t\t\tRAISE NOTICE '%',activity_string;\t\n\t\t\t\t\n\t\t\t\tPERFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , activity_string); \n\t\t\t\t\n\t\t\t\tEXIT ;\n\t\t\t\t\n\t\t\tEND IF ;\t  \n\t\tEND LOOP ;\n\t\t\n\t\tIF NOT found_flag \n\t\tTHEN\n\t\t\tactivity_string = 'WARNING : Not FOUND queryid for the query : '||query_text ;\n\t\t\t\n\t\t\tRAISE NOTICE '%',activity_string;\t\n\t\t\t\t\n\t\t\tPERFORM pg_log( 999 , 'update_history_pg_stat_activity_by_queryid' , activity_string); \n\t\tEND IF ;\n\t\t\n\tRAISE NOTICE 'UPDATE log_query if query has not logged in log-file';\n\t\t\n  END LOOP;\n  \n  RETURN result ;\nEND\n$$ LANGUAGE plpgsql;<\/code><\/pre>\n<p>\n\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>\n\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:<\/p>\n<ul>\n<li>\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043f\u043b\u0430\u043d\u043e\u0432 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f.<\/li>\n<li>\u0418\u0441\u0442\u043e\u0440\u0438\u044f CPU-time \u0437\u0430\u043f\u0440\u043e\u0441\u0430.<\/li>\n<li>\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043e\u0436\u0438\u0434\u0430\u043d\u0438\u0439 \u0437\u0430\u043f\u0440\u043e\u0441\u0430.<\/li>\n<\/ul>\n<p>\n\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>\n\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. <\/p>\n<p>\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.<br \/>\n<br \/>\u0418\u0441\u0442\u043e\u0447\u043d\u0438\u043a: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/467277\/\">habr.com<\/a><\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\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 \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. \u0417\u0430\u0434\u0430\u0447\u0430 \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. \u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435. \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 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-38131","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 4.9.10 - aioseo.com -->\n\t<meta name=\"description\" content=\"\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 \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. \u0417\u0430\u0434\u0430\u0447\u0430 \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. \u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435. \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\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/pg_stat_statements-pg_stat_activity-loq_query-pg_ash\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 4.9.10\" \/>\n\t\t<meta property=\"og:locale\" content=\"it_IT\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47pg_stat_statements + pg_stat_activity + loq_query = pg_ash? | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\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 \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. \u0417\u0430\u0434\u0430\u0447\u0430 \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. \u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435. \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\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/pg_stat_statements-pg_stat_activity-loq_query-pg_ash\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:21:49+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:21:49+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47pg_stat_statements + pg_stat_activity + log_query = pg_ash? | ProHoster","description":"Come breve aggiunta all'articolo 'Tentativo di creare un equivalente di ASH per PostgreSQL'. Compito \u00c8 necessario collegare la cronologia delle visualizzazioni di pg_stat_statements e pg_stat_activity. Di conseguenza, utilizzando la cronologia dei piani di esecuzione dalla tabella di servizio log_query, \u00e8 possibile ottenere molte informazioni utili per affrontare incidenti di performance e ottimizzazione delle query. Avviso. A causa della continua fase di test e sviluppo, l'articolo non","canonical_url":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/pg_stat_statements-pg_stat_activity-loq_query-pg_ash","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"it_IT","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47pg_stat_statements + pg_stat_activity + loq_query = pg_ash? | ProHoster","og:description":"\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 \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. \u0417\u0430\u0434\u0430\u0447\u0430 \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. \u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435. \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","og:url":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/pg_stat_statements-pg_stat_activity-loq_query-pg_ash","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:21:49+00:00","article:modified_time":"2019-10-31T19:21:49+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"38131","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-23 20:35:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:14:23","updated":"2026-01-23 20:35:19"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/38131","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=38131"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/38131\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=38131"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=38131"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=38131"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}