ááá¯á·ááá¯áẠtetrisology ááᯠá¡áááºážááẠá¡áá¯á¶ážáá»áá«á
á¡áá
áºá¡áá¬á¡á¬ážáá¯á¶ážááẠá¡áá±á¬ááºážááᯠáá±á·áá»á±á¬á·áá±áá±áááºá
á
á¬ááá¯ááºááá¯áá»á¬áž
ááŒá¿áá¬ááá¯áá¯á¶áá±á¬áºááŒááºáž
áááºááŸá PostgreSQL ááŸááºáááºážááá¯ááºááᯠAWS cloud á០áá±ááᶠLinux host ááá¯á· á¡áá«á¡á¬ážáá»á±á¬áºá
áœá¬ áá±á«ááºážáá¯ááºáá¯ááºááẠááá¯á¡ááºáá«áááºá á¡áá»áááºááŸáá·áºáááŒá±ážáá®ááá¯ááºáá±á¬áºáááºáž á¡áááºážáááºááŸá±á¬áá·áºááŸá±ážááŸá¯ááŒáá·áº ááŒá±á¬ááááºá
ááŸááºáááºážááá¯ááºá¡ááºááááºáá±á«ááºážáá¯ááºáá¬áááẠ5 áááá
áºááŒá
áºáááºá
AWS ááœáẠááŸááºáááºážááá¯ááºááᯠáá¬áá®ááá¯ááºáž ááŸáá·áºáá±áááºá
á¡áá¯á¶ážááŒá¯áá±á¬áááááá¬áá»á¬áž
ááŸááºáááºážááá¯ááºááᯠhost ááá¯á· á¡ááºáá¯ááºáá¯ááºáááºá AWS API "áá¯áá±á«áºááá¯áá±á¬ bash script ááá¯á¡áá¯á¶ážááŒá¯áááº
options ááá¯:
- --db-instance-identifier- AWS ááŸá instance á¡áááºá
- --log-file-name- áááºááŸááá¯ááºáá¯ááºáá¬ážáá±á¬ ááŸááºáááºážááá¯ááºá¡áááº
- --max-item- command á output ááœáẠááŒááºáá±ážááá·áº á á¯á á¯áá±á«ááºáž áá á¹á ááºážá¡áá±á¡ááœááºááá±á«ááºážáá¯ááºáá¯ááºáá¬ážáá±á¬ááá¯ááºá á¡áá¯á¶ážá¡ááœááºá¡á á¬ážá
- --starting-token- á áááºááŒááºáž ááá¯ááẠááá¯áááº
áá¯ááºáá²á·á ááá¯ážááá¯ážááŸááºážááŸááºáž - á¡áá¯ááºáá»áááºá¡ááœááºáž áááºáááºážááŸáá·áº á¡áá»áá¯ážáá»áá¯ážá¡ááœáẠá
áááºáááºá
á¬ážá
áᬠá¡áá¯ááºáá
áºáá¯á
ááŒá¿áá¬ááᯠáá¯ááºááá¯ážáá¯ááºá
ááºá¡á ááŒá±ááŸááºážááŒá®ážáá¬ážááá¯á· áá°ááááºá ááá¯á·áá±á¬áº Google ááẠá¡ááŒááºááŒá±ááŸááºážáááºážáá»á¬ážááᯠá¡ááŒá¶ááŒá¯áá¬ážááŒááºáž áááŸáááá·áºá¡ááŒáẠááá¯ááá¯áááºááŸáá¯ááºážá
áœá¬ ááŸá¬ááœá±ááá¯áá±á¬ááá¹ááááºáž áááŸááá«á áááºááá¯á·áááºááá¯á
á±áá¬áá° áá±á·áá»áá·áºáááºážáá±á¬ááºážáá
áºáá¯ááŒá
áºáááºá
áá¯ááºáááºážáá¬áááºááᯠááá¬ážáááºááŒá áºá á±ááŒááºážá
áá±á¬ááºáá¯á¶áž ááŸááºáááºážááá¯ááºááẠááŒá±á¬ááºážáá²ááá¯ááºáá±á¬ á¡ááŸááºáá»ááºážáá»á¬áž á¡á
á¯á¶ááŒá
áºáááºá ááááºáá
áºá¡áá ááŸááºáááºážááá¯ááºááᯠá€áá²á·ááá¯á· ááá¯ááºá
á¬ážááŒá¯ááá¯ááºáááº-
áá
áºáá¯áá¯ááᯠáááááá±ááŒá®áá¬ážá "tetris" ááá¯áᬠáá¬áá²á áá®ááŸá¬ áá¬áá²á
áá±á¬ááºááá¯ááºááᯠááááºáá
áºááŒáá·áº áááºááá·áºá¡áá«ááœáẠááŒá
áºáá±á«áºáá¬ááá¯ááºáá±á¬ ááœá±ážáá»ááºá
áá¬áá»á¬ážááᯠááá¯ááºá
á¬ážááŒá¯áá«á (ááá¯ážááŸááºážá
á±áááºá¡ááœááºá á€ááá
á¹á
ááœááºá ááá¯ááºážáá»á¬ážááᯠáá°áá®áá±á¬á¡ááŸááºááŸááá«á
á±)á áá¯á¶ááŸáẠtetris ááááºážááááºážáá»á¬áž
1) ááá¯ááºáá
áºáá¯áá¯á¶ážááᯠáá±á«ááºážáá¯ááºáá¯ááºááŒá®áž á¡ááŒá®ážáááºáá«áááºá á¡áá¯á¶ážá¡ááœááºá¡á
á¬ážááẠáá±á¬ááºáá¯á¶ážááá¯ááºá¡ááœááºá¡á
á¬ážááẠááá¯ááŒá®ážáááº-
2) ááá¯ááºááœáẠáááºáááºááŸááááºá á¡áá¯á¶ážá¡ááœááºá¡á
á¬ážááẠáá±á¬ááºáá¯á¶ážááá¯ááºá¡ááœááºá¡á
á¬ážááẠáá±ážáááºáááº-
3) ááá¯ááºááẠááááºááá¯ááºá áááºááœáŸááºááŸá¯ááŒá
áºááŒá®áž á¡áááºá¡á
ááºááŸááááºá á¡áá¯á¶ážá¡ááœááºá¡á
á¬ážááẠáá»ááºáá±á¬ááºáá¯á¶ážááá¯ááºá á¡ááœááºá¡á
á¬ážááẠáááºážáááº-
4) ááá¯ááºááẠááááºááá¯ááºá á¡áááºááŒá
áºááŒá®áž áá±á¬ááºáá¯á¶ážááŒá
áºáááºá á¡áá¯á¶ážá¡ááœááºá¡á
á¬ážááẠáá»ááºáá±á¬ááºáá¯á¶ážááá¯ááºáá¡ááœááºá¡á
á¬ážááẠááá¯ááŒá®ážáááº-
áá¬áááºááŸá¬ áá±á¬áá·áºááŸááºá
áá¯áá¶áá
áºáá¯ááᯠá
á¯á
ááºážááẠááá¯á·ááá¯áẠá¡ááá·áºá¡áá
áºáá
áºáá¯ááœáẠTetris áá
á¬ážáááºááŒá
áºáááºá
ááŒá¿áá¬ááᯠááŒá±ááŸááºážáá¬ááœáẠááŒá áºáá±á«áºáá¬áá±á¬ ááŒá¿áá¬áá»á¬áž
1) á¡ááá¯ááºáž á ááá¯ááºážáá« ááŒáá¯ážáá áºáá»á±á¬ááºážááᯠáá±á¬áºáá«á
áá±áá¯áá»á¡á¬ážááŒáá·áº á¡áá°ážááŒá¿áá¬áá»á¬áž áááŸááá²á·áá«á áááŠáž áááá¯ááááºážáááºážáááºáááºážá០á
á¶áá¬áááºáá
áºáá¯á
á¡áá±á¬ááºážáá¯á¶ážáááºáá±á¬ááºááŸá¯á¡ááœááºá¡á á¬áž
áá«áá±ááá·áº áá«á áááºážáááºáž ááá¯á
áááºáááºá
á¬ážááá¯á·áá±á¬ááºážáááºá
áá¶ááá±á¬ááºážá
áœá¬ááŒáá·áºá start chunk label ááŒá®ážáá±á¬áẠoffset ááá¯áá¯á¶ážááẠáááºážáááºážáááŸááá«-
áááºááááŒá®ážáááºááŸáá·áºá¡áá»áŸ ááœá±ážáá»ááºá áᬠ--starting-token ááᯠpaginating á áááºááá·áºáá±áá¬ááᯠáááºááŸááºááẠá¡áá¯á¶ážááŒá¯áááºá á€ááœá±ážáá»ááºááŸá¯ááẠáá±á¬ááºááá¯áááºá á¬ááŒá±á¬ááºážáááŸá±á·ááœáẠá¡á±á¬á·ááºáááºáááºááá¯ážáá áºáá¯ááᯠááá·áºáááºááŒáá¯ážá á¬ážáá«áá ááœá±ážáá»ááºááœáá·áºááᯠá¡á±á¬á·ááºáááºá¡ááŒá Ạááá·áºááœááºážá ááºážá á¬ážáááºááá¯ááºáá«á
áá«ááŒá±á¬áá·áº á¡ááá¯ááºážááœá±ááᯠá¡ááá¯ááºážááá¯ááºáááºááá«áááºá
á¡ááá¯ááºážááŒá®ážáá»á¬ážááŒáá·áº áááºáá«áá áááºááŸá¯ááŸá¯ á¡áá±á¡ááœáẠá¡áááºážáááºáᬠááŸááááá·áºáááºá ááá¯á·áá±á¬áº ááá¬áááŸá¬ á¡áá»á¬ážáá¯á¶áž ááŒá
áºáááá·áºáááºá
á¡áááºá áááºááẠá¡ááá¯ááºážáááºáá»á¬ážááŒáá·áº áááºáá«áá ááá·áºáá»ááºáááºá¡áá±ááŸáá·áºá áááºááŸá¯ááŸá¯á¡áá±á¡ááœááºááẠá¡áá»á¬ážáá¯á¶ážááŒá
áºáááá·áºáááºá ááá¯á·áá±á¬áº ááá¬áááŸá¬ á¡áááºážáááºáá¬ááŸááááá·áºáááºá
ááá¯á·ááŒá±á¬áá·áºá áá¬ááºááŒá±á¬ááááºááá¯á·ááŸá¯ááᯠáá»áŸá±á¬á·áá»áááºááŸáá·áº ááŒá±ááŸááºážáá»ááºá á¡áá¯á¶ážá
á¯á¶ ááŸáááŸá¯á¡ááœááºá áá¶ááá±á¬ááºážá
áœá¬áá²á áá»áá¯ááºážáá±á¬ááºááŸáá·áº áááºáááºáááºáá°áá±á¬ ááŒá±ááŸááºážáááºážáá
áºáá»áá¯ážááᯠáá»áœááºá¯ááºááŸá¬ááœá±áá²á·ááá«áááºá
á¥ááá¬á¡áá±ááŒáá·áºá á¡ááœááºááá¯ážááŸááºážáá±á¬áá¬ážááŸááºáž 2 áá¯ááœáẠááŸááºáááºážááá¯ááºáá áºáá¯ááᯠáá±á«ááºážáá¯ááºáá¯ááºááŒááºážáá¯ááºáááºážá ááºááᯠáá¯á¶ážáááºááŒáá·áºááŒáá«á áá¯á·á ááŒá áºáááºááŸá áºáá¯á áá¯á¶ážááœáẠáááºááŸá¯ááŸá¯á¡áá±á¡ááœááºááẠá¡ááá¯ááºážá¡ááœááºá¡á á¬ážáá±á«áºáá°áááºáááºá
1) áá±ážáááºáá±á¬á¡ááá¯ááºážáá»á¬ážááœááºááá·áºáá«á
2) ááŒá®ážáá¬ážáá±á¬á¡á
áááºá¡ááá¯ááºážáá»á¬ážááá¯áááºáá«
áá¯á¶ááŸááºá¡ááá¯ááºážá á¡áá±á¬ááºážáá¯á¶ážááŒá±ááŸááºážáá»ááºááẠá¡áááºááœááºááŒá
áºáááºá.
á¡ááá¯ááºážá¡ááœááºá¡á
á¬ážááẠá¡áááºážáááºáá»áŸáá¬ááŒá
áºáá±á¬áºáááºáž á
á¬áááºááŒááºážáá¯ááºáááºážá
ááºááœáẠáááºááŸá¯ááŸá¯á¡áá±á¡ááœááºááᯠáá»áŸá±á¬á·áá»áááºá¡ááœáẠá¡ááœááºá¡á
á¬ážááᯠááá¯ážááŒáŸáá·áºááá¯ááºáááºá
ááŸááºáá¬ážááá·áºáááºá áááºááŸá¯ááá·áºá¡ááá¯ááºážá á¡áá±á¬ááºážáá¯á¶ážá¡ááœááºá¡á á¬ážááᯠááœá±ážáá»ááºááŒááºážáááŒá¿áá¬ááᯠáá¯á¶ážááááŒá±ááŸááºážááá±ážááŒá±á¬ááºážááŸáá·áº ááá¯ááá¯áá±ážáááºáá±á¬áá±á·áá¬ááŸá¯ááŸáá·áº ááœá²ááŒááºážá áááºááŒá¬ááẠááá¯á¡ááºáá«áááºá áááºážáááºážááŒá¬áááºá
á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á á¡ááœá±ááœá±áá±á¬áºááŒáá»ááº
áááºáá±á¬ááºááŸá¯á á¬ážááœá²áá»á¬áž á¡áá¯á¶ážááŒá¯áááºá
CREATE TABLE endpoint
(
id SERIAL ,
host text
);
TABLE database
(
id SERIAL ,
âŠ
last_aws_log_time text ,
last_aws_nexttoken text ,
aws_max_item_size integer
);
last_aws_log_time â вÑÐµÐŒÐµÐœÐœÐ°Ñ ÐŒÐµÑка пПÑлеЎМегП загÑÑжеММПгП лПг-Ñайла в ÑПÑЌаÑе YYYY-MM-DD-HH24.
last_aws_nexttoken â ÑекÑÑÐŸÐ²Ð°Ñ ÐŒÐµÑка пПÑлеЎМей загÑÑжеММПй пПÑÑОО.
aws_max_item_size- ÑЌпОÑОÑеÑкОЌ пÑÑеЌ, пПЎПбÑаММÑй МаÑалÑÐœÑй ÑÐ°Ð·ÐŒÐµÑ Ð¿ÐŸÑÑОО.
áá¬ááºááœáŸááºážá á á¬áá¬ážá¡ááŒáá·áºá¡á á¯á¶
download_aws_piece.sh
#!/bin/bash
#########################################################
# download_aws_piece.sh
# downloan piece of log from AWS
# version HABR
let min_item_size=1024
let max_item_size=1048576
let growth_factor=3
let growth_counter=1
let growth_counter_max=3
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:''STARTED'
AWS_LOG_TIME=$1
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:AWS_LOG_TIME='$AWS_LOG_TIME
database_id=$2
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:database_id='$database_id
RESULT_FILE=$3
endpoint=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE_DATABASE -A -t -c "select e.host from endpoint e join database d on e.id = d.endpoint_id where d.id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:endpoint='$endpoint
db_instance=`echo $endpoint | awk -F"." '{print toupper($1)}'`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:db_instance='$db_instance
LOG_FILE=$RESULT_FILE'.tmp_log'
TMP_FILE=$LOG_FILE'.tmp'
TMP_MIDDLE=$LOG_FILE'.tmp_mid'
TMP_MIDDLE2=$LOG_FILE'.tmp_mid2'
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:current_aws_log_time='$current_aws_log_time
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:is_new_log='$is_new_log
let last_aws_max_item_size=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select aws_max_item_size from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: last_aws_max_item_size='$last_aws_max_item_size
let count=1
if [[ $is_new_log == '1' ]];
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF NEW AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 2
fi
else
next_token=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "select last_aws_nexttoken from database where id = $database_id "`
if [[ $next_token == '' ]];
then
next_token='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: CONTINUE DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 3
fi
line_count=`cat $LOG_FILE | wc -l`
let lines=$line_count-1
tail -$lines $LOG_FILE > $TMP_MIDDLE
mv -f $TMP_MIDDLE $LOG_FILE
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
if [[ $next_token == '' ]];
then
cp $TMP_FILE $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
else
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
fi
first_str=`tail -1 $TMP_FILE`
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
head -$lines $TMP_FILE > $RESULT_FILE
###############################################
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
#
#################################################################
exit 0
á¡áá»áá¯á·áá±á¬ ááŸááºážáááºážáá»ááºáá»á¬ážááŸáá·áº áá¬ááºááœáŸááºážá¡ááá¯ááºážá¡á áá»á¬áž-
Script ááá·áºááœááºážááŸá¯ ááá·áºáááºáá»ááºáá»á¬áž
- YYYY-MM-DD-HH24 áá±á¬áºáááº- AWS_LOG_TIME= ááŸááºáááºážááá¯ááºá¡áááºá á¡áá»áááºáá¶ááááº$1
- áá±áá¬áá±á·á ẠID- database_id=$2
- á á¯áá±á¬ááºážáá¬ážáá±á¬ ááŸááºáááºážááá¯ááºá¡áááº- RESULT_FILE=$3
áá±á¬ááºáá¯á¶áž á¡ááºáá¯ááºáá¯ááºáá¬ážáá±á¬ ááŸááºáááºážááá¯ááºá á¡áá»áááºáá¶ááááºááᯠááá°áá«-
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
áá±á¬ááºáá¯á¶ážáááºáá¬ážáá±á¬ ááŸááºáááºážááá¯ááºá á¡áá»áááºáá¶ááááºááẠááá·áºááœááºážááŸá¯ ááá·áºáááºáá»ááºááŸáá·áº áááá¯ááºáá®áá«áá ááŸááºáááºážááá¯ááºá¡áá áºááᯠáááºáá±ážáááº-
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo '***download_aws_piece.sh -FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
áááºáá¬ážáá±á¬ááá¯ááºá០áá±á¬ááºááá¯áááºá¡ááœáŸááºážááááºááá¯ážááᯠáá»áœááºá¯ááºááá¯á·áááŸááááº-
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
áá±á«ááºážáá¯ááºáá¡áá¯á¶ážáááááááºááá¹ááá¬ááẠnexttoken áááá¬áááºááá¯ážááŒá áºáááºá
á¡ááá¯ááºážáá
áºáá¯ááœááºá áááºážáá
áºáá»áŸá±á¬ááºááœááºá ááá¯ááºáá¡ááá¯ááºážáá»á¬ážááᯠáá±ááœááºáá¬á ááá¯ááºážáá»á¬ážáá±á«ááºážá
ááºáᬠá¡ááá¯ááºážáá¡ááœááºá¡á
á¬ážááᯠááá¯ážá
á±áááº-
Main Loop
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
áá±á¬ááºáá áºáá¯ááá¬áá²?
ááá¯á·ááŒá±á¬áá·áºá áááá¡áááºá¡áááºá¡áá¯áẠ- "cloud ááŸááŸááºáááºážááá¯ááºááá¯áá±á«ááºážáá¯ááºáá¯ááºáá«" ááá¯ááŒá±ááŸááºážáááºá áá±á«ááºážáá¯ááºáá¯ááºáá¬ážáá²á· ááŸááºáááºážáá²á· áá¬áá¯ááºáááá²á
áŠážá
áœá¬ áááºááẠááŸááºáááºážááá¯ááºááᯠááœá²ááŒááºážá
áááºááŒá¬ááŒá®áž áááºážá០á¡ááŸááºáááẠáá±á¬ááºážááá¯ááŸá¯áá»á¬ážááᯠáá¯ááºáá°ááẠááá¯á¡ááºáááºá
á¡áá¯ááºá ááááºááááºáá«áá°ážá á¡ááá¯ážááŸááºážáá¯á¶áž bash-script ááẠáá±á¬ááºážááœááºáá«áááºá
upload_log_query.sh
#!/bin/bash
#########################################################
# upload_log_query.sh
# Upload table table from dowloaded aws file
# version HABR
###########################################################
echo 'TIMESTAMP:'$(date +%c)' Upload log_query table '
source_file=$1
echo 'source_file='$source_file
database_id=$2
echo 'database_id='$database_id
beginer=' '
first_line='1'
let "line_count=0"
sql_line=' '
sql_flag=' '
space=' '
cat $source_file | while read line
do
line="$space$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}' `
if [[ $current_beginer == $beginer ]]; then
if [[ $sql_flag == '1' ]]; then
sql_flag='0'
log_date=`echo $sql_line | awk -F" " '{ print $1}' `
log_time=`echo $sql_line | awk -F" " '{ print $2}' `
duration=`echo $sql_line | awk -F" " '{ print $5}' `
#replace ' to ''
sql_modline=`echo "$sql_line" | sed 's/'''/''''''/g'`
sql_line=' '
################
#PROCESSING OF THE SQL-SELECT IS HERE
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d DATABASE -v ON_ERROR_STOP=1 -A -t -c "select log_query('$ip_port',$database_id , '$log_date' , '$log_time' , '$duration' , '$sql_modline' )"
then
echo 'FATAL_ERROR - log_query '
exit 1
fi
################
fi #if [[ $sql_flag == '1' ]]; then
let "line_count=line_count+1"
check=`echo $line | awk -F" " '{ print $8}' `
check_sql=${check^^}
#echo 'check_sql='$check_sql
if [[ $check_sql == 'SELECT' ]]; then
sql_flag='1'
sql_line="$sql_line$line"
ip_port=`echo $sql_line | awk -F":" '{ print $4}' `
fi
else
if [[ $sql_flag == '1' ]]; then
sql_line="$sql_line$line"
fi
fi #if [[ $current_beginer == $beginer ]]; then
done
ááá¯áááºááẠlog file ááŸáá¯ááºáá¯ááºáá¬ážáá±á¬áá±ážááœááºážááŸáá·áºá¡áá¯ááºáá¯ááºááá¯ááºáá«ááŒá®á
ááŒá®ážáá±á¬á· á¡áá¯á¶ážáááºááá¯ááºááŒá±áá»á¬ážá áœá¬ááŸááá«áááºá
ááœá²ááŒááºážá áááºááŒá¬áá¬ážáá±á¬ áá±ážááœááºážáá»á¬ážááᯠáá áºáá±áá¬áá¬ááœáẠááááºážáááºážáá¬ážááá«áááºá áááºážá¡ááœááºá áááºáá±á¬ááºááŸá¯ááá¬ážááᯠá¡áá¯á¶ážááŒá¯áááºá log_query
CREATE TABLE log_query
(
id SERIAL ,
queryid bigint ,
query_md5hash text not null ,
database_id integer not null ,
timepoint timestamp without time zone not null,
duration double precision not null ,
query text not null ,
explained_plan text[],
plan_md5hash text ,
explained_plan_wo_costs text[],
plan_hash_value text ,
baseline_id integer ,
ip text ,
port text
);
ALTER TABLE log_query ADD PRIMARY KEY (id);
ALTER TABLE log_query ADD CONSTRAINT queryid_timepoint_unique_key UNIQUE (queryid, timepoint );
ALTER TABLE log_query ADD CONSTRAINT query_md5hash_timepoint_unique_key UNIQUE (query_md5hash, timepoint );
CREATE INDEX log_query_timepoint_idx ON log_query (timepoint);
CREATE INDEX log_query_queryid_idx ON log_query (queryid);
ALTER TABLE log_query ADD CONSTRAINT database_id_fk FOREIGN KEY (database_id) REFERENCES database (id) ON DELETE CASCADE ;
ááœá²ááŒááºážá
áááºááŒá¬áá¬ážáá±á¬ áá±á¬ááºážááá¯áá»ááºááᯠáá¯ááºáá±á¬ááºáá±áá«áááºá plpgsql áá¯ááºáá±á¬ááºáá»áẠ"log_query"á
log_query.sql
--log_query.sql
--verison HABR
CREATE OR REPLACE FUNCTION log_query( 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 ;
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 ;
BEGIN
result = TRUE ;
RAISE NOTICE '***log_query';
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 );
SELECT e.host , d.name , d.owner_pwd
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||' dbname='||database_rec.name||' user=DATABASE 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
INSERT INTO log_query
(
query_md5hash ,
database_id ,
timepoint ,
duration ,
query ,
explained_plan ,
plan_md5hash ,
explained_plan_wo_costs ,
plan_hash_value ,
ip ,
port
)
VALUES
(
log_md5hash ,
log_database_id ,
log_timepoint ,
log_duration ,
log_query ,
log_explain_plan ,
md5(log_explain_plan::text) ,
log_plan_wo_costs ,
md5(log_plan_wo_costs::text),
client_ip ,
client_port
);
activity_string = 'New query has logged '||
' database_id = '|| log_database_id ||
' query_md5hash='||log_md5hash||
' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');
RAISE NOTICE '%',activity_string;
PERFORM pg_log( log_database_id , 'log_query' , activity_string);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE '*** unique_violation *** query already has logged';
END;
SELECT queryid
INTO log_queryid
FROM log_query
WHERE query_md5hash = log_md5hash AND
timepoint = log_timepoint;
IF log_queryid IS NOT NULL
THEN
RAISE NOTICE 'log_query with query_md5hash = % and timepoint = % has already has a QUERYID = %',log_md5hash,log_timepoint , log_queryid ;
RETURN result;
END IF;
------------------------------------------------
RAISE NOTICE 'Update queryid';
SELECT *
INTO log_query_rec
FROM log_query
WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');
FOR pg_stat_history_rec IN
SELECT
queryid ,
query
FROM
pg_stat_db_queries
WHERE
database_id = log_database_id AND
queryid is not null
LOOP
pg_stat_query = pg_stat_history_rec.query ;
pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
log_query_text = trim(trailing ' ' from log_query_rec.query);
pg_stat_query_text = pg_stat_query;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
IF (log_query_text LIKE pg_stat_query_text) THEN
found_flag = TRUE ;
ELSE
found_flag = FALSE ;
END IF;
IF found_flag THEN
UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
activity_string = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
RETURN result ;
END
$$ LANGUAGE plpgsql;
áá¯ááºáá±á¬ááºááá·áºá¡áá«á áááºáá±á¬ááºááŸá¯ááá¬ážááᯠá¡áá¯á¶ážááŒá¯áááºá pg_stat_db_queriesááá¬ážá០áááºááŸááá±ážááŒááºážáá»ááºáá»á¬ážá áá»áŸááºáá
áºááŒááºáá¯á¶áá«ááŸááá±á¬ A pg_stat_history (ááá¬ážá¡áá¯á¶ážááŒá¯ááŸá¯ááᯠá€áá±áá¬ááœáẠáá±á¬áºááŒáá¬ážáá«áááºá
TABLE pg_stat_db_queries
(
database_id integer,
queryid bigint ,
query text ,
max_time double precision
);
TABLE pg_stat_history
(
âŠ
database_id integer ,
âŠ
queryid bigint ,
âŠ
max_time double precision ,
âŠ
);
áá¯ááºáá±á¬ááºáá»ááºááẠááá·áºá¡á¬áž ááŸááºáááºážááá¯ááºáá áºáá¯á០áá±á¬ááºážááá¯áá»ááºáá»á¬ážááᯠáá¯ááºáá±á¬ááºáááºá¡ááœáẠá¡áá¯á¶ážáááºáá±á¬á¡ááºá¹áá«áááºáá»á¬ážá áœá¬ááᯠá¡áá±á¬ááºá¡áááºáá±á¬áºááẠááœáá·áºááŒá¯áááºá ááá¯ááá¯áááºááŸá¬-
á¡ááœáá·áºá¡áááºážáá¶áá«áẠ1 - áá±ážááŒááºážááŸá¯áá¯ááºáá±á¬ááºááŸá¯ááŸááºáááºáž
á
áœááºážáá±á¬ááºáááºááŒá
áºáááºáá
áºáá¯á
áááºááẠá¡ááœááºá¡áá¯á¶ážáááºáááºá ááááŠážá
áœá¬ áááá¯ááºážááᯠááá¡á±á¬ááºáá¯ááºáá« - ááŸá±á¬áá·áºááŸá±ážááŸá¯á áááºá¡áá»áááºá á
áá¬áá²á
ááá¯á·áá±á¬áẠááá¹ááááºáá»á¬ážá¡ááá¯ááºáž ááŒááºáá¡ááŒá±á¬ááºážááá¬ážáá»á¬ážááᯠááŸá¬ááœá±áá«á áá±áá¬áá±á·á
Ạload ááẠáááááá¬áᬠááá¯ážáá¬ááŒá®áž áááá»áá±á¬ áá±á¬ááºážááá¯áá»ááºááẠáááºážááŸáá·áº áá¬ááŸáááá¯ááºáá±á
log_query ááá¬ážááá¯á· áááºáá±á¬ááºááŸá¯á¡áá
áºááá·áºáá«á
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 );
SELECT e.host , d.name , d.owner_pwd
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 = to_number(duration,'99999999999999999999D9999999999');
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);
RAISE NOTICE 'log_query=%',log_query ;
log_md5hash = md5( log_query::text );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE 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
INSERT INTO log_query
(
query_md5hash ,
database_id ,
timepoint ,
duration ,
query ,
explained_plan ,
plan_md5hash ,
explained_plan_wo_costs ,
plan_hash_value ,
ip ,
port
)
VALUES
(
log_md5hash ,
log_database_id ,
log_timepoint ,
log_duration ,
log_query ,
log_explain_plan ,
md5(log_explain_plan::text) ,
log_plan_wo_costs ,
md5(log_plan_wo_costs::text),
client_ip ,
client_port
);
á¡ááºá¹áá«ááẠáá¶áá«áẠ2 - Query Execution Plans ááᯠááááºážáááºážáá«á
á€á¡áá»áááºááœáẠááá·áºááœááº-ááŸááºážáááºážáá»ááº-ááŸááºáá»ááºáá»á¬áž ááœááºáá±á«áºáá¬ááá¯ááºáááº-"áá«áá±ááá·áº autoexplain ááŸáááŒá®ážáá¬ážáá«áâ . áá¯ááºáá«áááºá áá«áá±ááá·áº execution plan ááᯠáá°áá®áá²á· log file ááŸá¬ ááááºážáááºážááŒá®áž áá±á¬ááºáááºááœá²ááŒááºážá áááºááŒá¬ááá¯á·á¡ááœáẠááááºážáááºážááá¯á·á¡ááœááºá log file ááᯠááœá²ááŒááºážá áááºááŒá¬ááá¯á· ááá¯á¡ááºááẠáá¬á¡áá»ááºáá²?
ááá¯á·áá±á¬áºá áá«ááá¯á¡ááºáááº-
ááááŠážá
áœá¬- á
á±á¬áá·áºááŒáá·áºáá±ážáá±áá¬áá±á·á
áºááááºáá±á¬ááºááŸá¯ááá¬ážááœáẠá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡á
á®á¡á
ááºááᯠááááºážáááºážáá«á
áá¯áááá¡áá»ááº- á
á¯á¶á
ááºážáá±ážááŒááºážááŸá¯ á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡á
á®á¡á
áẠááŒá±á¬ááºážáá²ááœá¬ážááŒá±á¬ááºáž áá»ááºáá»ááºážááááŒááºááá¯ááºá
á±áááºá¡ááœáẠá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡á
á®á¡á
ááºáá»á¬ážááᯠáá
áºáá¯ááŸáá·áºáá
áºáᯠááŸáá¯ááºážááŸááºááá¯ááºá
á±áááºá
áááºáááºááŸááºááŸáẠáá¯ááºáá±á¬ááºááŸá¯áá±á¬ááºáá»á¬ážáá«ááŸááá±á¬ áá±á¬ááºážááá¯ááŸá¯áá
áºáᯠáááá¯ááºáá«áááºá EXPLAIN ááᯠá¡áá¯á¶ážááŒá¯á áááºážá á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯ á¡á
á®á¡á
ááºááᯠááá°ááŒááºážááŸáá·áº ááááºážáááºážááŒááºážááẠá¡ááŒá±áá¶á¡áá¯ááºááŒá
áºáááºá
ááá¯á·á¡ááŒááºá EXPLAIN (COSTS FALSE) á¡áá¯á¶ážá¡ááŸá¯ááºážááᯠá¡áá¯á¶ážááŒá¯á á¡á
á®á¡á
ááºá hash áááºááá¯ážááᯠááá°ááẠá¡áá¯á¶ážááŒá¯ááá·áº á¡á
á®á¡á
ááºá áá°áá±á¬ááºááᯠáááºáááá¯ááºáááºá áááºážááẠá¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯á¡á
á®á¡á
ááºá áá±á¬ááºáááºááœá²áááá¯ááºážááŒá±á¬ááºážááᯠááœá²ááŒááºážá
áááºááŒá¬áá¬ááœáẠá¡áá±á¬ááºá¡áá°ááŒá
áºá
á±ááá·áº á¡á
á®á¡á
ááºá hash áááºááá¯ážááᯠááá°ááá¯ááºáááºá
á¡áá±á¬ááºá¡áááºáá±á¬áºááŸá¯ á¡á
á®á¡á
áẠááá°áá¬ááᯠááá°áá«á
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE 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');
á¡ááœáá·áºá¡áááºáž #3 - á á±á¬áá·áºááŒáá·áºáá±á·áá¬áááºá¡ááœáẠQuery Log ááá¯á¡áá¯á¶ážááŒá¯ááŒááºážá
á
áœááºážáá±á¬ááºáááºáááºááá
áºáá»á¬ážááᯠáá±á¬ááºážááá¯áá»ááºá
á¬áá¬ážá¡ááœáẠááá¯ááºáá±á¬áºáááºáž áááºážá ID á¡ááœááºá á
áœááºážáá±á¬ááºáááºáááºááá
áºáá»á¬ážááᯠááŒááºáááºáááºááŸááºáá¬ážááá·áº áá±á¬ááºážááá¯áá»ááºáá»á¬ážááŸáá·áº ááŸááºáááºážááá¯ááºá០áá±á¬ááºážááá¯áá»ááºáá»á¬ážááᯠáá±á«ááºážá
ááºááẠááá¯á¡ááºáááºá
áá±á¬ááºážááŒá®á á¡áááºážáá¯á¶áž á
áœááºážáá±á¬ááºááẠááŒá
áºáááºáá
áºáᯠáá±á«áºáá±á«ááºááẠá¡áá»áááºá¡ááá¡áá»ááŸááááºá
ááá¯á·ááŒá±á¬áá·áº áá±á¬ááºážááá¯áá»áẠID áá
áºáá¯á¡ááœáẠá
áœááºážáá±á¬ááºááẠááŒá
áºáááºáá
áºáᯠááŒá
áºáá±á«áºááá·áºá¡áá«á áááá»áá±á¬ ááá·áºáááºáá±á¬ááºáááºááá¯ážáá»á¬ážááŸáá·áº áá±á¬ááºážááá¯áá»ááºá áááá»áá±á¬ áá¯ááºáá±á¬ááºáá»áááºááŸáá·áº ááŒá¬áá»áááºááá¯á·áá«ááŸáááá·áº áá±á¬ááºážááá¯áá»ááºááᯠááá¯ážáá¬ážááááá·áºáááºá ááŒááºááœááºážááá¯áᬠá¡áá¯á¶ážááŒá¯á áá±ážáá¬ážáá±á¬ á¡áá»ááºá¡áááºááᯠááá°áá«á pg_stat_statements - áá¬ážááŒá
áºáá¬ážáááºá
query á queryid ááá¯ááŸá¬ááŒá®áž log_query ááá¬ážááŸá entry ááᯠupdate áá¯ááºáá«á
SELECT *
INTO log_query_rec
FROM log_query
WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');
FOR pg_stat_history_rec IN
SELECT
queryid ,
query
FROM
pg_stat_db_queries
WHERE
database_id = log_database_id AND
queryid is not null
LOOP
pg_stat_query = pg_stat_history_rec.query ;
pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
log_query_text = trim(trailing ' ' from log_query_rec.query);
pg_stat_query_text = pg_stat_query;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
IF (log_query_text LIKE pg_stat_query_text) THEN
found_flag = TRUE ;
ELSE
found_flag = FALSE ;
END IF;
IF found_flag THEN
UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
activity_string = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
afterword
ááá¯á·ááŒá±á¬áá·áº áá±á¬áºááŒáá¬ážáá±á¬ áááºážáááºážááẠáááºážá á¡áá¯á¶ážáá»ááá®áá±ážááŸááºážá¡ááœáẠááœá±á·ááŸáááááºá
áá»áœááºá¯ááºáááá¯ááºáá±ážááá¯ááºáá¬á¡ááŒááºá¡ááá±á¬áºáááºážáá±á¬ááºážá áá±á«ááºážáá¯ááºáá¯ááºáá¬ážáá±á¬á¡ááá¯ááºážáá¡ááœááºá¡á á¬ážááá¯ááœá±ážáá»ááºááŒááºážááŸáá·áºááŒá±á¬ááºážáá²ááŒááºážá¡ááœáẠalgorithm ááœááºá¡áá¯ááºáá¯ááºááẠááá¯á¡ááºáá±áá±ážáááºá áá±áá¯áá»á¡á¬ážááŒáá·áº ááŒá¿áá¬ááᯠáááŒá±ááŸááºážááá¯ááºáá±ážáá°ážá á áááºáááºá á¬ážááá¯á·áá±á¬ááºážáá«áááá·áºáááºá
áá«âáá±ááá·áºâ áá«áᬠáá¯á¶ážáááŒá¬ážáá¬ážáá²á· áá¬ááºâáááºážáá«áá²...
source: www.habr.com