Re: Is my database now too big?

Scott Marlowe wrote:
> On 10/7/07, Darren Reed <darrenr+postgres(at)fastmail(dot)net> wrote:
> > Scott Marlowe wrote:
> > > On 10/7/07, Darren Reed <darrenr(at)fastmail(dot)net> wrote:
> > > > Scott Marlowe wrote:
>
> > A few days ago I did:
> > pg_dumpall > foo
> > What I was doing yesterday was:
> > rm -rf /data/db/*
> > initdb -D /data/db
> > start
> > psql < foo
> > run for some period
> > stop
> > reboot
> > start
> > ...tables have gone but disk space is still in use.
> > I dont know if it was during the period of running that the
> > database got corrupted (interrupted insert/update/query?)
> > or what happened.
>
> Are you sure postgresql was starting up in the /data/db directory
> after reboot and not somewhere else like /var/lib/pgsql/data???
>
> IF you're definitely hitting the right directory, then Is the database
> shutting down cleanly on reboot? It might be that it's getting killed
> during a write and you've got some kind of problem with fsync on your
> machine so the db is getting corrupted
>
> > > Can you be more specific on what exact query causes the problem to show up?
> > >
> >
> > It turned out that _any_ query on that table caused the problem to show up.
> >
> > I couldn't even do "DROP TABLE ifl;" without postgres growing until it
> > ran out of memory.
>
> definitely sounds like some kind of issue other just the size of the
> table, like some kind of corruption.
>
> ...
> And I don't see anything else in your postgresql.conf that looks
> suspicious. I'm leaning towards possible pilot error in shutting down
> or starting up the db.
>

Ok, I've had another reoccurance of this problem.

The sequence of events was something like this:
CREATE TABLESPACE foo LOCATION "/data/index/ext";
<wait>
<machine hangs>
<reboot>
Of course postgresql didn't shut down cleanly because it was
naughtly earlier and ate all my RAM, causing the box to hang.
Now I'm back to the prior problem: entire tables are missing
when postgresql starts back up again. Obviously there is some
sort of corruption (caused by postgresql) and it isn't able to
recover properly.

So I'm moving quite squarely away from postgres being able
to gobble up as much RAM is it desires.

Darren