Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 114199F9364 for ; Mon, 15 Oct 2007 01:10:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 47388-04 for ; Mon, 15 Oct 2007 01:10:19 -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 E96019F95D2 for ; Mon, 15 Oct 2007 01:10:19 -0300 (ADT) Received: from compute2.internal (compute2.internal [10.202.2.42]) by out1.messagingengine.com (Postfix) with ESMTP id 27B4330DEF; Mon, 15 Oct 2007 00:10:19 -0400 (EDT) Received: from heartbeat1.messagingengine.com ([10.202.2.160]) by compute2.internal (MEProxy); Mon, 15 Oct 2007 00:10:19 -0400 X-Sasl-enc: UqkCRY3y1W2UdXMOYnlxbq78SsAZ29mh+vovrRfzmTNd 1192421418 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 62A6C611; Mon, 15 Oct 2007 00:10:18 -0400 (EDT) Message-ID: <4712E839.7090403@fastmail.net> Date: Sun, 14 Oct 2007 21:10:33 -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/113 X-Sequence-Number: 27027 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? Later on... So having upgraded to 8.1.10, I thought I'd try out a few things... # /usr/pkg/bin/pg_dumpall -- -- PostgreSQL database cluster dump -- \connect postgres -- -- Roles -- CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN; CREATE ROLE root; ALTER ROLE root WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN; -- -- Database creation -- REVOKE ALL ON DATABASE template1 FROM PUBLIC; REVOKE ALL ON DATABASE template1 FROM postgres; GRANT ALL ON DATABASE template1 TO postgres; \connect postgres pg_dump: SQL command failed pg_dump: Error message from server: ERROR: out of memory DETAIL: Failed on request of size 20. pg_dump: The command was: LOCK TABLE public.foo IN ACCESS SHARE MODE pg_dumpall: pg_dump failed on database "postgres", exiting # SELECT * FROM foo LIMIT 1; ERROR: out of memory DETAIL: Failed on request of size 20. I don't know if this is of any significance: # \l List of databases Name | Owner | Encoding -----------+----------+----------- postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) # /usr/pkg/bin/psql -U postgres template1 psql: FATAL: out of memory DETAIL: Failed on request of size 20. What puzzles me is why the transaction log hasn't resulted in postgresql being able to restore itself to a known clean state. Darren