pg慢SQL告警企业微信

配置DB_LIST.txt数据库链接信息

---

内容格式：数据库名称=数据库IP地址=用户名=密码=端口号
    例：testjyz=192.168.1.1=test=jyz123456=5432

编写脚本 slowsql.sh

---

#!/bin/bash
wx(){
   curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=*************************' \
   -H 'Content-Type: application/json' \
   -d '
   {
        "msgtype": "text",
        "text": {
            "content": "'${1}'"
        }
   }'
}


date1=`date "+%Y-%m-%d %H:%M:%S"`
for i in `cat DB_LIST.txt`
do
MYDATA=`echo ${i}|awk -F "=" '{print $1}'`
DBHOST=`echo ${i}|awk -F "=" '{print $2}'`
DBUSER=`echo ${i}|awk -F "=" '{print $3}'`
DBPASSWORD=`echo ${i}|awk -F "=" '{print $4}'`
DBPORT=`echo ${i}|awk -F "=" '{print $5}'`
##数据库查询
PGPASSWORD=${DBPASSWORD} psql -U ${DBUSER} -h ${DBHOST} -p ${DBPORT} -c "SELECT usename,client_addr,round(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - LEAST (query_start, xact_start)))) AS runtime,datname,query FROM  pg_stat_activity where state <> 'idle' and round(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - LEAST (query_start, xact_start))))>1  order by runtime desc limit 5;" > sql_${MYDATA}.txt 
sed -i '/^[  ]*$/d' sql_${MYDATA}.txt
sed -i "1,2d" sql_${MYDATA}.txt
NUM=`cat sql_${MYDATA}.txt|grep -v rows|wc -l`
USER=`cat sql_${MYDATA}.txt|grep -v row|awk -F "|" '{print $1}'|sed 's/ //g'`
HOST=`cat sql_${MYDATA}.txt|awk -F "|" '{print $2}'|sed 's/ //g'`
TIME=`cat sql_${MYDATA}.txt|awk -F "|" '{print $3}'|sed 's/ //g'`
DB=`cat sql_omp.txt|awk -F "|" '{print $4}'|sed 's/ //g'`
sed -i 's/+//g' sql_${MYDATA}.txt
SQL_ALL=`cat sql_${MYDATA}.txt|awk -F "|" '{print $5}'`
PGPASSWORD=${DBPASSWORD} psql -U ${DBUSER} -h ${DBHOST} -p ${DBPORT} -c "explain ${SQL_ALL}" > explain_sql_${MYDATA}.txt

SQL_ALL_S=`echo ${SQL_ALL}|sed 's/ /~/g'|sed 's/=//g'`
sed -i "1,2d" explain_sql_${MYDATA}.txt
INFO_ALL="[${date1}],大于100s慢查询${NUM}次其中最长为${TIME}秒,用户${USER},主机${HOST},数据库${DB},索引类型${SQL_EXPLAIN_TYPE},POSSIBLE_KEYS为${SQL_EXPLAIN_POSSIBLE_KEYS},KEY为${SQL_EXPLAIN_KEYS},sql为:${SQL_ALL_S}"
INFO=`echo ${INFO_ALL}|sed 's/ /~/g'|sed "s/\"/'/g"`
echo -e ${INFO}
echo ${NUM}
if [ ${NUM} -gt 0 ]; then
wx ${INFO}
fi
done