#!/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