--- On Thu, 3/19/09, Kevin Keane <subscription AT kkeane DOT com> wrote:

> From: Kevin Keane <subscription AT kkeane DOT com>
> Subject: Re: [Bacula-users] Better way to garbage collect postgresql database
> To: 
> Cc: "baculausers" <bacula-users AT lists.sourceforge DOT net>
> Date: Thursday, March 19, 2009, 8:30 PM
> 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.
> 

Kevin,

  This is exactly what I want to do. I come from DB2 world and we would reorg 
the table. I run milder version of vacuum on other days, but I run extensive 
vacuum after full backup. 



> 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.
> 

If I can reduce the time it takes to do full vacuum then I would like to do it 
before my full backup. This database is for bacula only so unless I am doing 
backups it could be down for few hours.

If dump/load takes less  than four hours then I can do it before full backups 
start.

> -- 
> 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



Hemant Shah
E-mail: hjrrs AT yahoo DOT com



      

------------------------------------------------------------------------------
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