vacuum(analyze)

最新推荐文章于 2024-03-15 18:36:04 发布

原创最新推荐文章于 2024-03-15 18:36:04 发布 · 897 阅读

0 ·

CC 4.0 BY-SA版权

Postgresql 专栏收录该内容

20 篇文章

订阅专栏

#!/bin/bash

#设置自动定期对表更新统计信息和删除’死‘行
#zhaowz 2019-04-02

date=`date +"%Y%m%d%H%M"`
v_hour=0
v_param=$#

if [ $v_param -ge 3 ];then
echo "Invalid argument!! E.g sh pg-vacuum-analyze.sh vlnx191001.xxx.cn OR sh pg-vacuum-analyze.sh vlnx191001.xxx.cn \" db1,db2,db3,...\""
exit 1
fi

pghosts=$1

if [ -n "$pghosts" ];then

hostname=${pghosts%%.*}
filename=$hostname'-vacuum-analyze-'$date

for pghostname in $pghosts
do
if [ $v_param -eq 1 ];then
dblist=$(psql -h $pghostname -U zhaowz -d postgres -c "select datname from pg_database where datname not in ('template0','template1')" -A -t)
elif [ $v_param -eq 2 ];then
dblist=$(psql -h $pghostname -U zhaowz -d postgres -c "select datname from pg_database where datname=any('{$2}')" -A -t)
fi

if [ -z "$dblist" ];then
echo "Invalid dbname OR Invalid hostname !!!" >> /home/zhaowz/logs/$filename.log
exit 1
fi

for db in $dblist
do
v_hour=$(date "+%k")
if [[ ${v_hour} -ge 1 && ${v_hour} -lt 10 ]]; then

echo "$db : `date +"%Y%m%d%H%M"` Begin ">>/home/zhaowz/logs/$filename.log
tables=$(psql -h $pghostname -U zhaowz -d $db -c "select tablename from pg_tables where schemaname='public'" -A -t)

for table in $tables
do
echo "$table ：`date +"%Y%m%d%H%M"` Begin vacuum-analyze ">>/home/zhaowz/logs/$filename.log
#psql -h $pghostname -U zhaowz -d $db -c "vacuum $table"
psql -h $pghostname -U zhaowz -d $db -c "vacuum analyze $table"
echo "$table ：`date +"%Y%m%d%H%M"` vacuum-analyze has finished ">>/home/zhaowz/logs/$filename.log
done
fi
done

done
fi