From: Darren Reed <darrenr+postgres@fastmail.net>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is my database now too big?
Date: Sun, 14 Oct 2007 20:07:42 -0700
Message-ID: <4712D97E.7010004@fastmail.net> (raw)
In-Reply-To: <dcc563d10710071803ia92a046neef1296999173d13@mail.gmail.com>
References: <1191730382.18880.1214536197@webmail.messagingengine.com>
<47087723.7040403@commandprompt.com>
<1191744007.14918.1214548241@webmail.messagingengine.com>
<dcc563d10710070114m35df7741tcaa01700b21baec@mail.gmail.com>
<4708A9DC.5060306@fastmail.net>
<dcc563d10710070713q6aa1fbfdqe8f07e5356d39d41@mail.gmail.com>
<47097EA7.902@fastmail.net>
<dcc563d10710071803ia92a046neef1296999173d13@mail.gmail.com>
Scott Marlowe wrote:
> On 10/7/07, Darren Reed <darrenr+postgres@fastmail.net> wrote:
> > Scott Marlowe wrote:
> > > On 10/7/07, Darren Reed <darrenr@fastmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: darrenr+postgres@fastmail.net, scott.marlowe@gmail.com, jd@commandprompt.com
Subject: Re: Is my database now too big?
In-Reply-To: <4712D97E.7010004@fastmail.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox