Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 743949F9B19 for ; Mon, 15 Oct 2007 00:19:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 95118-09 for ; Mon, 15 Oct 2007 00:18:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by postgresql.org (Postfix) with ESMTP id 640B19F99BA for ; Mon, 15 Oct 2007 00:18:53 -0300 (ADT) Received: from compute2.internal (compute2.internal [10.202.2.42]) by out1.messagingengine.com (Postfix) with ESMTP id EFD59322DA; Sun, 14 Oct 2007 23:18:52 -0400 (EDT) Received: from heartbeat1.messagingengine.com ([10.202.2.160]) by compute2.internal (MEProxy); Sun, 14 Oct 2007 23:18:53 -0400 X-Sasl-enc: sYyJUxY99euPUZl7TTNuFQpt+IeescAcBSF1e633f68l 1192418332 Received: from [192.168.1.235] (64-142-85-108.dsl.dynamic.sonic.net [64.142.85.108]) by mail.messagingengine.com (Postfix) with ESMTP id BFD8B3AFE; Sun, 14 Oct 2007 23:18:51 -0400 (EDT) Message-ID: <4712DC28.3090708@fastmail.net> Date: Sun, 14 Oct 2007 20:19:04 -0700 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: Scott Marlowe CC: "Joshua D. Drake" , pgsql-admin@postgresql.org Subject: Re: Is my database now too big? References: <1191730382.18880.1214536197@webmail.messagingengine.com> <47087723.7040403@commandprompt.com> <1191744007.14918.1214548241@webmail.messagingengine.com> <4708A9DC.5060306@fastmail.net> <47097EA7.902@fastmail.net> <4712D97E.7010004@fastmail.net> In-Reply-To: <4712D97E.7010004@fastmail.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/120 X-Sequence-Number: 27034 Darren Reed wrote: > Scott Marlowe wrote: >> On 10/7/07, Darren Reed wrote: >> > Scott Marlowe wrote: >> > > On 10/7/07, Darren Reed 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"; > > > > 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