pgagent job for routine vacuum with bash script

Question

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.

You shuold really split this into a Question and a related answer. — user9517, Feb 07 '17 at 08:52

score 2 · Accepted Answer · answered Feb 07 '17 at 09:15

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.

pgagent job for routine vacuum with bash script

1 Answers1