Darren Reed wrote:
Scott Marlowe wrote:
On 10/7/07, Darren Reed <darrenr+postgres@xxxxxxxxxxxx> wrote:
> Scott Marlowe wrote:
> > On 10/7/07, Darren Reed <darrenr@xxxxxxxxxxxx> 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.
I suppose the obvious question here is how do I restore the
missing tables? The data is all there, is there some sort of
transaction log that can be unwound to restore visibility of
the missing tables? Where do I start for trying to do some
forensics? Or am I SOL?
Darren
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
