vacuum(analyze) 川长思鸟来 2023-06-30 05:25 4阅读 0赞 \#!/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
还没有评论，来说两句吧...