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.
Best Answer
This is the code:
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 abegin...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.