Re: Database growth problem

On Thu, 17 Jul 2003, mlaks wrote:

> Hi,
>
>  I have about 15 different PC's running RedHat 7.3 Linux, each running the
> same database backed application (I use  Postgresql 7.2.1 for the database)
> on each machine.

Upgrade to 7.2.4 as soon as possible.  There are bugs in 7.2.3 and before
that can cause data loss.

> I also keep one machine slightly different than the others.
>
> On this machine, as a backup to the other machines, instead of just storing
> files without deleting, I use a scheme that stores new files as they come in
> but also deletes the oldest files from the RAID and also deletes the
> corresponding entries describing those entries from the database. I use this
> machine as an element of redundancy to recent files on the other machines.
>
> On this system, I keep the RAID about 75% full of files, and thus I imagine
> that the Postgresql database should be about .75 of a GB in size. However, it
> isnt! I find that it keeps growing as time goes on. Its now about 3.9GB in
> size!

This sounds like index growth.  I'm guessing you're indexing on a field
that only increments, and the btree is growing in one direction only.
There's a fix in for 7.4 for this, but for now, all you likely need to do
is reindex the index that keeps growing.

you can use oid2name (in /contrib/oid2name, easy install) and 'du -s' to
figure out which files are the biggest and what they belong to.

cd $PGDATA/base
oid2name
cd oidfromlastcommand
du -s *|sort -n  #<- this will put the biggest at the bottom of the list
oid2name -d dbname |grep oid-from-previous-du-s