vacuum(free)_vacuum free-CSDN博客

本文链接：https://blog.csdn.net/zhaowenzhong/article/details/103985861

本文介绍了一个名为 Vimpg-Vacuum-Freeze.sh 的 Bash 脚本，该脚本用于自动定期对 PostgreSQL 数据库执行 Vacuum Freeze 操作，以维护事务 ID 和提高数据库性能。脚本通过参数接收数据库主机名和数据库名称，遍历指定的数据库，记录操作日志，并在特定时间执行 Vacuum Freeze 命令。

摘要由CSDN通过智能技术生成

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