--- On Fri, 3/20/09, Jesper Krogh <jesper AT krogh DOT cc> wrote:

> From: Jesper Krogh <jesper AT krogh DOT cc>
> Subject: Re: [Bacula-users] Better way to garbage collect postgresql database
> To: hjrrs AT yahoo DOT com
> Cc: "baculausers" <bacula-users AT lists.sourceforge DOT net>
> Date: Friday, March 20, 2009, 12:30 AM
> Hemant Shah wrote:
> > 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
> 
> There is absolutely no reason to "vacumm full"
> unless your data-size actually are shrinking over time.
> (longer periods). A normal vacuum will make it available for
> the next run.. you most likely are running autovacuum
> anyway.

  Yes, I do run autovacuum, but I just start using postgresql and I am not sure 
how efficient it is in space usage. I was afraid that the data would be 
fragmented if it re-uses the free space.

> 
> > reindexdb
> 
> Might make sense, but weekly?..  There is AFAIK a small
> amount of index-bloat collecting up over time in PG. But in
> general just "yearly" or "monthly"
> should really be sufficient.

 Weekly may be overkill, but it is part of script that run through cron.


> 
> > 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?
> 
> No not really VACUUM FULL i telling PG to reorder the data
> on-disk and free up space for the os, that is a "hard
> task". But it is also not needed, since you are going
> to used it within the next week/month anyway.. so ordinary
> VACUUM is sufficient.
> 
> > 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?
> 
> Basically yes.



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