Hemant Shah wrote: > Folks, > > This is a database question, but I figured some of the bacula users may have > come across this problem so I am posting it here. > > > Every monday I run following commands to check and garbage collect bacula > database: > > dbcheck command > vacuumdb -q -d bacula -z -f > reindexdb > > Usually I purge one or two backup volumes and the above commands run in less > than 20 minutes. > > Before my monthly Full backup I delete large amount of data from the database > as I delete one month worth of Full and Incremental backups. When I run the > above commands after the Full backup, the vacummdb command take 12 hours to > run. Is there a faster/better way of doing it? > It has been a long time since I administered a postgres DB, but if memory serves me right you might be able to drop some indexes, then do the vacuuming, and then recreate them. Also, I believe you can vacuum individual tables rather than the database as a whole.
The lion's share of the vacuuming would happen in the files table, so that's probably the one you'd want to first look at in terms of dropping/recreating indexes, and also in terms of vacuuming separately. Also there are several levels of vacuuming. With this type of table, you would probably not want to get too aggressive. What you don't want to do is eliminate all the empty space in the database, only to later need the same empty space again. You do want to vacuum simply to consolidate empty space into larger chunks. Basically, the same idea as disk defragmentation. If memory serves me right, this "milder" vacuuming is the default. Sorry I have to speak in concepts rather than concrete here, but it just has been too long. > My database is about 9GB. > > If I backup database using pgdump and then restore it, will it do the same > thing as vacuumdb and reindexdb commands? > Pretty close, but keep in mind that you would have considerable database downtime. You can do this, too, on a per-table basis. -- Kevin Keane Owner The NetTech Find the Uncommon: Expert Solutions for a Network You Never Have to Think About Office: 866-642-7116 http://www.4nettech.com This e-mail and attachments, if any, may contain confidential and/or proprietary information. Please be advised that the unauthorized use or disclosure of the information is strictly prohibited. The information herein is intended only for use by the intended recipient(s) named above. If you have received this transmission in error, please notify the sender immediately and permanently delete the e-mail and any copies, printouts or attachments thereof. ------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
