Running smart vacuum and analyze in pre-specified maintenance window

posted Nov 10, 2014, 4:16 PM by Sachchida Ojha   [ updated Jul 23, 2018, 12:01 PM ]
There is a  compelling requirement for all Greenplum DBA to run the maintenance tasks in a pre-specified window. Depending on the data volume, No of columns in tables and other factors, your daily maintenance job may run longer than maintenance window. One dirty way is to kill the job that is running beyond the maintenance window but this may leads your database in a inconsistent state and may raise other issues.

Here is the script that will do vacuum and analyze on tables in a prespecified time limit. The script will automatically exit after Runtime Exceeded Project maintenance Window.

[gpadmin@sachi scripts]$ ./
Script is running.Please wait.It may take few minutes...
Runtime Exceeded Project maintenance Window. Now exiting!!!!!!!!

Script completed. Script log is available at /home/gpadmin/maintenance/logs
[gpadmin@sachi scripts]$ 

Script will run next day from the table where it left on previous day.