Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 4F2439F9BF5 for ; Mon, 15 Oct 2007 00:08:20 -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 92474-03 for ; Mon, 15 Oct 2007 00:07:34 -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 592A39F9A41 for ; Mon, 15 Oct 2007 00:07:34 -0300 (ADT) Received: from compute2.internal (compute2.internal [10.202.2.42]) by out1.messagingengine.com (Postfix) with ESMTP id 0E1C1321A0; Sun, 14 Oct 2007 23:07:28 -0400 (EDT) Received: from heartbeat1.messagingengine.com ([10.202.2.160]) by compute2.internal (MEProxy); Sun, 14 Oct 2007 23:07:33 -0400 X-Sasl-enc: S/oOC8i0qrGVebym9GviH5ulPo0Mb3S7h8barGa+9NLf 1192417646 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 8D2DA3A9B; Sun, 14 Oct 2007 23:07:26 -0400 (EDT) Message-ID: <4712D97E.7010004@fastmail.net> Date: Sun, 14 Oct 2007 20:07:42 -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> In-Reply-To: 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/112 X-Sequence-Number: 27026 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. So I'm moving quite squarely away from postgres being able to gobble up as much RAM is it desires. Darren