大网数据广告活动
服务器运维问答

pgagent job for routine vacuum with bash script

· 52CE 我爱测

I was expected to create routine vacuuming to be able to schedule the vacuum at less busy hours. I have been searching for a means to routinely vacuum the tables that have not been vacuumed for x days. Postgresql allows you to va…

问题原文

原帖链接 · Bart Dirks · CC BY-SA 3.0

I was expected to create routine vacuuming to be able to schedule the vacuum at less busy hours.

I have been searching for a means to routinely vacuum the tables that have not been vacuumed for x days. Postgresql allows you to vacuum full. Which starts and does not give you control over the time it takes.

Therefore I would like to have a means to search for tables that have not been vauumed for x time. Then vacuum them if the current time is in a window of time.

已采纳解答

原帖链接 · Bart Dirks · CC BY-SA 3.0

This is the code:

#!/bin/bash

beginTime="0000"
endTime="0700"

time=$(date +"%H%M")
timeToRun=true
while $timeToRun;do
  if [ $time -lt $beginTime ]
  then
    echo "Begin Time not yet reached"
    timeToRun=false
  else
        if [ $time -lt $endTime ]
        then
          echo "you may start"
          echo "Vacuum table"
          resultToBeVacuumed=$(sudo -u postgres psql -c "select '!'|| schemaname ||'.' || relname||'!' from pg_stat_user_tables where (last_autovacuum IS NULL or (now() - last_autovacuum  >  interval '5' day) ) and (last_vacuum IS NULL or (now() -last_vacuum > interval '5' day)) order by last_vacuum asc limit 1;")

          echo "$resultToBeVacuumed"
          toBeVacuumed=$(echo $resultToBeVacuumed| cut -d '!' -f 2)
          echo "$toBeVacuumed"
          echo "Vacuum started"
          if [[ $toBeVacuumed != *"----"* ]]; then
            echo "$toBeVacuumed"
            sudo -u postgres psql -c "vacuum analyse verbose $toBeVacuumed"
          else
            timeToRun=false
          fi
        else
          echo "End Time passed"
          timeToRun=false
        fi
  fi
done

The reason why I ended up at a bash script is that the postgresql sql script did not make it possible to use a Execute 'vacuum analyse $table'. This results in an error VACUUM cannot be executed from a function or multi-command string. I have tried a lot of things. The basic rule of not allowing to use a begin...end, which is the cause of the error, I was not able to remove. Every function requires a scope and thus will result in an error. Therefore the only way to solve the problem was to execute the vacuum with an isolated command. Thus bash.

I hope this will save people a lot of hours searching for a solution.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-02-07)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。