vim pg-vacuum-freeze.sh
#!/bin/bash
#设置自动定期对 事务ID执行 回环
#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-freeze.sh vlnx191001.xxx.cn OR sh pg-vacuum-freeze.sh vlnx191001.xxx.cn \" db1,db2,db3,...\""
exit 1
fi
pghosts=$1
if [ -n "$pghosts" ];then
hostname=${pghosts%%.*}
filename=$hostname'-vacuum-freeze-'$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-freeze ">>/home/zhaowz/logs/$filename.log
psql -h $pghostname -U zhaowz -d $db -c "Vacuum freeze $table"
echo "$table :`date +"%Y%m%d%H%M"` $table vacuum-freeze has finished ">>/home/zhaowz/logs/$filename.log
done
fi
done
done
fi