PostgreSQL Vacuum and analyze script
vacuum_and_analyze.sh script vacuums and analyzes all the database of an instance. Script pick the instance on the basis of details provided in cronjobs_conf_5432.sh. If you have multiple instances, and you want to run vacuum_and_analyze.sh for those instances too, by tweaking the instance details in cronjobs_conf_5432.sh.
bash-4.1$ cat /post/open_source/scripts/vacuum_and_analyze.sh #!/bin/bash usage() { echo -e "\n\tUsage: `basename $0` -c /home/postgres/edbscripts/cronjobs_conf_5432.sh\n" echo -e "\tNote : Absolute path required for cronjobs configuration file\n" exit 1 } while getopts ":c:" opt; do case "${opt}" in c) CONF=${OPTARG} ;; *) echo "Unknown option: -$OPTARG" >&2; usage ;break;; esac done if [ -z "${CONF}" ]; then usage fi if [ ! -f "${CONF}" ]; then echo -e "\t\nNot a valid conf file...$CONF" exit 1 fi ### Main script process source ${CONF} process() { echo -e "\n:::::::::::: VACUUM ANALYZE Details :::::::::::::::\n" echo "VACUUM ANALYZE LOG.: $CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log" START=$(date +%s) $PGBIN/vacuumdb -a -z -v -h $PGHOST -U $PGUSER -p $PGPORT >>$CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log 2>>$CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log result=${?} if [ ${result} -ne 0 ] then echo "Please connect to the server ASAP"| mailx -s "::ALERT::`hostname` - VACUUM Failed" $CUSTOMER_EMAIL echo "`date`|$CUSTOMER_NAME|`basename $0`|false|vacuum failed" >>$CRONLOG_LOCATION/cronjobs.csv exit 1 else echo "vacuum analyze completed successfully">>$CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log END=$(date +%s) DIFF=$(($END-$START)) VDB=`awk '/vacuuming database/ {print $4}' $CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log|wc -l` ISSUES=`grep -i "error:" $CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log|wc -l` echo "VACUUMED INFO.....: vacuumed $PGPORT port of $VDB databases." echo "TIME..............: started at $(date -d @"$START" +%d-%m-%y' '%T) with total time $((diff/60))M $((diff%60))s" echo "VACUUM ISSUES.....: In logs $(if [ $ISSUES -ge 1 ]; then echo "$ISSUES found. Please check logs"; else echo "nothing found"; fi)" echo "`date`|$CUSTOMER_NAME|`basename $0`|true|vacuum completed" >>$CRONLOG_LOCATION/cronjobs.csv fi } process >>$CRONLOG_LOCATION/crons_summary.log exit 0
Comments
Post a Comment