From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | mlaks <mlaks(at)bellatlantic(dot)net> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Database growth problem |
Date: | 2003-07-18 14:06:29 |
Message-ID: | Pine.LNX.4.33.0307180803010.1889-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sergio Pili | 2003-07-18 14:37:04 | Upgrade Tips 7.3 |
Previous Message | Tom Lane | 2003-07-18 13:36:47 | Re: Using temp tables |