Script – vacuum, analyze and reindex for postgres database
1.Script for vacuum,analyze and Reindexing the database
cat /home/script/vaccum_analyze_reindex.sh #!/bin/bash PGHOME=/opt/PostgreSQL/9.3/bin LOG=/backups/logs/igr-vac-logs LOGFILE=vacuum_analyze_`date +"%Y-%m-%d-%T"`.log REINDEXLOG=reindex_`date +"%Y-%m-%d-%T"`.log export PGOPTIONS='-c vacuum_cost_delay=10 -c maintenance_work_mem=5GB' export PGPASSWORD=postgres $PGHOME/vacuumdb -d db_name -j 5 -p 5432 -z >> $LOG/$LOGFILE 2>> $LOG/$LOGFILE $PGHOME/reindexdb -d db_name -p 5432 >> $LOG/$REINDEXLOG 2>> $LOG/$REINDEXLOG exit 0
2.vacuum,analyze and Reindex script sheduling on crontab everyday night at 10.pm
Befere scheduling crontjob check the current date becouse some server will running with wrong time that is what am telling check date and time before scheduling crontjob
To get date and time in Linux issue following command
$ date
To scheduling crontjob issue crontab -e command
crontab -e 0 22 * * * sh /home/script/vaccum_analyze_reindex.sh