This is how I used to do maintenance through cron job. Please advise if i need to change my method, Also I used to run re-index all the time while doing Vacuum (cron job updated twice a week).
queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.brands queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem. contactmethodtype queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contacttype queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.skillset queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.skillsettype queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.location queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contactmethod queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contact2 queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.contact queryfor Vaccum:VACUUM VERBOSE ANALYZE contactmanagementsystem.people Return code for VaccumDb: 0 queryfor Vaccum:REINDEX TABLE contactmanagementsystem.brands queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contactmethodtype queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contacttype queryfor Vaccum:REINDEX TABLE contactmanagementsystem.skillset queryfor Vaccum:REINDEX TABLE contactmanagementsystem.skillsettype queryfor Vaccum:REINDEX TABLE contactmanagementsystem.location queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contactmethod queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contact2 queryfor Vaccum:REINDEX TABLE contactmanagementsystem.contact Thanks, Bala On Sun, Nov 28, 2010 at 9:53 PM, hubert depesz lubaczewski < dep...@depesz.com> wrote: > On Sun, Nov 28, 2010 at 07:25:52AM +0000, Bala Murugan wrote: > > > > The following bug has been logged online: > > > > Bug reference: 5774 > > Logged by: Bala Murugan > > Email address: b...@a-cti.com > > PostgreSQL version: 8.3.7 > > Operating system: openSUSE 10.3 (X86-64) - Kernel \r (\l). > > Description: VACCUM & REINDEX kills production environement > > Details: > > > > Iam running postgres 8.3 version for more than 2 yrs on Amazon EC2 > Instance, > > in recent days Vaccum and reindex make the application down for more than > > 2hrs. I am not sure this because of my configuration or postgres. > > is it normal vacuum? > > or are you using "vacuum full"? > > generally - voth vacuum full and reindex do lock tables for exclusive > access. > > that's why you generally don't use them! > > vacuum full is especially frowned upon. > > as for reindex - if you *really* need it (are you sure? what makes you > think you need it), then there are ways to do reindex without actually > using "reindex" command, which are mostly transparent for users, but you > should check if you really need to run reindex at all. > > Best regards, > > depesz > > -- > Linkedin: http://www.linkedin.com/in/depesz / blog: > http://www.depesz.com/ > jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / > gg:6749007 >
