Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 38D5163225E for ; Thu, 12 Feb 2009 17:11:13 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34406-01 for ; Thu, 12 Feb 2009 17:11:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mk-filter-3-a-1.mail.uk.tiscali.com (mk-filter-3-a-1.mail.uk.tiscali.com [212.74.100.54]) by mail.postgresql.org (Postfix) with ESMTP id E0855633066 for ; Thu, 12 Feb 2009 17:11:05 -0400 (AST) X-Trace: 145033584/mk-filter-3.mail.uk.tiscali.com/B2C/$b2c-THROTTLED-DYNAMIC/b2c-CUSTOMER-DYNAMIC-IP/80.225.116.207/None/john.lister-ps@kickstone.com X-SBRS: None X-RemoteIP: 80.225.116.207 X-IP-MAIL-FROM: john.lister-ps@kickstone.com X-MUA: Thunderbird 2.0.0.19 (Windows/20081209) X-IP-BHB: Once X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AnYBAG8flElQ4XTP/2dsb2JhbAAIgxbRFIQYBg X-IronPort-AV: E=Sophos;i="4.38,198,1233532800"; d="scan'208";a="145033584" Received: from ppp-3-207.bris-a-2.access.uk.tiscali.com (HELO [192.168.6.11]) ([80.225.116.207]) by smtp.tiscali.co.uk with ESMTP; 12 Feb 2009 21:11:02 +0000 Message-ID: <4994906B.6090408@kickstone.com> Date: Thu, 12 Feb 2009 21:11:07 +0000 From: John Lister User-Agent: Thunderbird 2.0.0.19 (Windows/20081209) MIME-Version: 1.0 To: Tom Lane CC: pgsql-admin@postgresql.org Subject: Re: database corruption help References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com> <17205.1234200693@sss.pgh.pa.us> <4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us> <49908D72.7040904@kickstone.com> <25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com> In-Reply-To: <4991338B.704@kickstone.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200902/83 X-Sequence-Number: 31304 I seem to have more dead rows now.. doing a vacuum full on pg_class gives me INFO: vacuuming "pg_catalog.pg_class"INFO: "pg_class": found 37 removable, 1845 nonremovable row versions in 18905 pages DETAIL: 27 dead row versions cannot be removed yet. Nonremovable row versions range from 160 to 229 bytes long. There were 933834 unused item pointers. Total free space (including removable row versions) is 150368692 bytes. 18839 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.01s/0.05u sec elapsed 0.17 sec.INFO: index "pg_class_oid_index" now contains 1813 row versions in 7 pages DETAIL: 56 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index "pg_class_relname_nsp_index" now contains 1818 row versions in 24 pages DETAIL: 63 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.WARNING: index "pg_class_relname_nsp_index" contains 1818 row versions, but table contains 1813 row versions HINT: Rebuild the index with REINDEX.INFO: analyzing "pg_catalog.pg_class"INFO: "pg_class": scanned 3000 of 18905 pages, containing 227 live rows and 6 dead rows; 227 rows in sample, 1430 estimated total rows Total query runtime: 4469 ms. I've restarted the server which should have got rid of any outstanding/long running transactions - which it though was the major cause of lingering dead rows. Any ideas how to fix this as i'd like to reduce this from 150+Mb which can't be good for performance.... Thanks