X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3BEE532A041 for ; Tue, 28 Sep 2004 15:45:38 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63577-08 for ; Tue, 28 Sep 2004 14:45:28 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5651632A02D for ; Tue, 28 Sep 2004 15:45:31 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8SEjVos014630; Tue, 28 Sep 2004 10:45:31 -0400 (EDT) To: "Thomas Madsen" Cc: pgsql-admin@postgresql.org Subject: Re: Vacuum full - disk space eaten by WAL logfiles In-reply-to: <64F70386AE9AB64D8F36DD6FC355555E8FE2C0@hal9000.softcom.local> References: <64F70386AE9AB64D8F36DD6FC355555E8FE2C0@hal9000.softcom.local> Comments: In-reply-to "Thomas Madsen" message dated "Tue, 28 Sep 2004 15:12:25 +0200" Date: Tue, 28 Sep 2004 10:45:31 -0400 Message-ID: <14629.1096382731@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/267 X-Sequence-Number: 14970 "Thomas Madsen" writes: > I raised it to 3,000,000 and tried a "VACUUM FULL" on the table. Out > popped an error when it reached the toast table index: "Number of tuples > (xxx) not same at number in heap (XXX), recreate index". The numbers > (XXX) differed by just 2. It then started recreating the index, but > stopped short when another error popped up: "insertion of duplicate > keys"-failure. VACUUM FULL will not try to "recreate the index", so you misinterpreted what you were seeing. > I have subsequently attempted to reindex the tables/indexes, but it > didn't help a bit. I still got the: "Number of tuples (xxx) not same at > number in heap (XXX), recreate index"- error. I have some vague memory that it's difficult to get the system to accept a REINDEX on a toast table or toast index in older versions. REINDEXing the base table will *not* fix it, you need to specify the toast object by name. Does it let you do that? > The last lines keep coming until no more diskspace is left. I searched > this forum and got some hints to this problem. > I raised the "checkpoint" default values in postgres.conf to these > values, which should have decreased the need for diskspace: It's checkpoints that are responsible for recycling WAL files. It sounds to me like checkpoints just aren't happening at all, which seems odd. Does a checkpoint subprocess appear while this is going on? It might well be that your best bet is to dump and reload that particular table. You don't necessarily have to use pg_dump, you could do something like CREATE TABLE dup AS SELECT * from badtable; TRUNCATE badtable; INSERT INTO badtable SELECT * FROM dup; DROP TABLE dup; (7.2 had TRUNCATE didn't it? I forget.) In any case I would *strongly* recommend an update to 7.2.5, which you can do in-place without a dump/reload. We do not make bugfix releases on a whim --- there are some pretty serious bugs fixed between 7.2.1 and 7.2.5. regards, tom lane