You’ll have to create a file on your appliance and run it.
The appliation needs to be up for this script to be up.
It will cause extra IO but no locking. If your using synchronize please do this off peak.
copy the script below into vi
vi vacuum_nightly.sh
chmod u+x vacuum_nightly.sh
./vacuum_nightly.sh
Let me know if there’s any issue
ymurphy@vmware.com
After running this manaually a few times then I’d suggest you run it nightly on a cron.
crontab -e
20 2 * * * /tmp/vacuum_nightly.sh
Output from the script can be seen on stats_collector.log on the same directory as the script
#!/bin/bash error=0 errorlog='stats_collector.log' pbin=`ps -ea -o cmd | grep '[p]ostgres -D' | awk '{ gsub ("postgres$","",$1); print $1}'` run_log='optimise_output.log' dbownername=` ps -eo uname:20,pid,pcpu,pmem,sz,tty,stat,time,cmd --sort -time |grep -m 1 'postgres:' |awk '{print $1}'` databasename=` ps -f -N --sort -pid | grep -m 1 'postgres:' |awk '{print $10}'` function vacuumaz { echo $dbownername database owner echo $databasename database name if [ $databasename = "secureall" ]; then $pbin/vacuumdb -U secureall -d secureall 2>>$errorlog elif [ $databasename = "vcac" ]; then $pbin/vacuumdb -U postgres -d vcac 2>>$errorlog elif [ $databasename = "VCDB" ]; then $pbin/vacuumdb -U postgres -d VCDB > /dev/null 2>>$errorlog elif $pbin/vacuumdb -U postgres -d $databasename > /dev/null 2>>$errorlog ; then printf '\n No Errors on vacuumdb \n' printf '\n' else echo 'vacuumdb ERROR. Please review $errorlog file' printf '\n' error=1 fi echo `/bin/date "+%Y-%m-%dT%H:%M:%S"` Finish vacuumdb echo `/bin/date "+%Y-%m-%dT%H:%M:%S"` Finish vacuumdb >> $errorlog } # Main Program vacuumaz
Reblogged this on VMzoneBlog.
LikeLike