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 E6565329DCD for ; Tue, 14 Sep 2004 07:17:45 +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 07976-05 for ; Tue, 14 Sep 2004 06:17:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id A3024329D58 for ; Tue, 14 Sep 2004 07:17:39 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i8E6HbHl003145; Tue, 14 Sep 2004 02:17:37 -0400 (EDT) To: Wim Kerkhoff Cc: pgsql-admin@postgresql.org Subject: Re: pg_class / missing tables In-reply-to: <414657CA.5040809@nyetwork.org> References: <414657CA.5040809@nyetwork.org> Comments: In-reply-to Wim Kerkhoff message dated "Mon, 13 Sep 2004 19:30:34 -0700" Date: Tue, 14 Sep 2004 02:17:37 -0400 Message-ID: <3144.1095142657@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/119 X-Sequence-Number: 14822 Wim Kerkhoff writes: > Short story: server was rebooted without being shut down properly, upon > bootup PostgreSQL (7.4.3) starts fine, but a couple of data tables > (along with their associated indexes and sequences) are gone. Other > tables are still there. Hmm. What *exactly* happens when you try select * from pg_class where relname = 'missing_table_name'; Also, let's see the error log from when you tried to restart the server after the crash. > What's interested is that if I try: > CREATE TABLE missing_table_name (foo int); > It does complain that the table already exists... You sure it's not complaining that the type already exists? > What's happening here? I'm suspicious that you've got a damaged block of pg_class. It would make sense that that would take out several rows created at about the same time, which would explain the fact that the lost items seem closely related. If you had *no* other lossage, you might be able to recover by recreating the tables with the exact same schemas, and then copying the old data files over these tables' data files. But there are enough gotchas in this idea that "restore from backup" is probably a better answer. regards, tom lane