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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users