X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9CD779DC873 for ; Mon, 13 Feb 2006 12:42:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62977-03 for ; Mon, 13 Feb 2006 12:42:02 -0400 (AST) X-Greylist: delayed 00:27:50.002293 by SQLgrey- Received: from bigmail.jlaltd.co.uk (bigmail.jla.com [81.171.198.99]) by postgresql.org (Postfix) with ESMTP id 0345B9DC9ED for ; Mon, 13 Feb 2006 12:42:00 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by bigmail.jlaltd.co.uk (Postfix) with ESMTP id B34DE2A9997 for ; Mon, 13 Feb 2006 16:12:19 +0000 (GMT) Received: from bigmail.jlaltd.co.uk ([127.0.0.1]) by localhost (bigmail [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20984-02 for ; Mon, 13 Feb 2006 16:12:17 +0000 (GMT) Received: from [192.168.0.232] (ruger.jlaltd.co.uk [192.168.0.232]) by bigmail.jlaltd.co.uk (Postfix) with ESMTP id 4B29E2A9991 for ; Mon, 13 Feb 2006 16:12:17 +0000 (GMT) Message-ID: <43F0ACEA.4090503@jla.com> Date: Mon, 13 Feb 2006 15:59:38 +0000 From: Steve Burrows User-Agent: Debian Thunderbird 1.0.2 (X11/20050331) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Lost tables in Postgres 7.1 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at jla.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200602/161 X-Sequence-Number: 20582 OK, I know I should be running a later version, but we've been on this since it came out and rebuilding the host is a big deal :( Last time we did it was to move from 6.5 to 7.1 and we had to rebuild the whole server (not the fault of PostgreSQL, just evolution). Some of my tables have spontaneously disappeared. They are not visible in pg_class. vacuuming pg_class (as suggested by Tom Lane for someone with a similar problem) did not cause them to reappear. Doing a select on the invisible tables returns a structure (column headings) but no data. The log returned the following for the vacuum of pg_class, suggesting that 44 tuples got shot. DEBUG: --Relation pg_class-- DEBUG: Pages 31: Changed 0, reaped 17, Empty 0, New 0; Tup 1922: Vac 44, Keep/VTL 848/0, Crash 0, UnUsed 42, MinLen 115, MaxLen 160; Re-using: Free/Avail. Space 11716/0; EndEmpty/Avail. Pages 1/0. CPU 0.00s/0.00u sec. DEBUG: Index pg_class_oid_index: Pages 61965; Tuples 1922: Deleted 44. CPU 4.71s/0.34u sec. DEBUG: Index pg_class_relname_index: Pages 82270; Tuples 1922: Deleted 44. CPU 6.41s/0.39u sec. DEBUG: Rel pg_class: Pages: 31 --> 30. I have a copy of the pg_class table file from before it was vacuumed, so in theory I still have the 'deleted' tuples. Is there any way of recovering them? Any suggestions? We have backups and are rolling the tape, but the reality is that we will of course lose the 2 days of data entered since the last good backup which would be painful to reproduce. Getting a bit desperate here, 7 years on PostgreSQL and never had a problem like this before! Thanks, Steve