Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CCAF22E0033 for ; Tue, 26 Feb 2008 06:23:41 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 38632-09 for ; Tue, 26 Feb 2008 06:23:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from out3.smtp.messagingengine.com (out3.smtp.messagingengine.com [66.111.4.27]) by postgresql.org (Postfix) with ESMTP id 60D542E003D for ; Tue, 26 Feb 2008 06:23:28 -0400 (AST) Received: from compute2.internal (compute2.internal [10.202.2.42]) by out1.messagingengine.com (Postfix) with ESMTP id 34D4FA0C7E; Tue, 26 Feb 2008 05:23:27 -0500 (EST) Received: from heartbeat1.messagingengine.com ([10.202.2.160]) by compute2.internal (MEProxy); Tue, 26 Feb 2008 05:23:27 -0500 X-Sasl-enc: 4qdnzdBpX36WVr+UevRjp/tf6V4uQo7or2FoCsZIRxOV 1204021406 Received: from [192.168.1.235] (76-191-150-176.dsl.dynamic.sonic.net [76.191.150.176]) by mail.messagingengine.com (Postfix) with ESMTPSA id 75120177DF; Tue, 26 Feb 2008 05:23:26 -0500 (EST) Message-ID: <47C3E88B.8070803@fastmail.net> Date: Tue, 26 Feb 2008 02:23:07 -0800 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: Shoaib Mir CC: pgsql-admin@postgresql.org Subject: Re: How to reindex when unable to open relation? References: <47C3AE1F.2010804@fastmail.net> In-Reply-To: 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: 200802/342 X-Sequence-Number: 28416 Shoaib Mir wrote: > On Tue, Feb 26, 2008 at 5:13 PM, Darren Reed > > > wrote: > > Inserts into my table started generating an error message (see below), > and there was a log message that suggested I reindex. > > postgres seems to start and stop ok, but no operations on the tables > seem to work. > > l# /usr/pkg/bin/pg_dump -s -U postgres > pg_dump: failed sanity check, parent table OID 16401 of pg_rewrite > entry > OID 16403 not found > > Is this recoverable without using a backup? > > > > Looks like to me corrupted system catalog! > > I guess when you dropped the table it didn't delete the record from > pg_rewrite was not deleted when the parent table was dropped. So you > can try doing something: ... I didn't drop the table! This happened amidst a script doing inserts! postgres=# delete from pg_rewrite where oid = 16403; DELETE 1 postgres=# \q firewall# !?stop /etc/rc.d/postgres stop waiting for server to shut down.... done server stopped firewall# /etc/rc.d/postgres start server starting firewall# /usr/pkg/bin/psql -U postgres psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Hmmm, from the logfile, signal 11, so it core dumped... Of course the default built binary is -O2... (gdb) where #0 0x082114b8 in RelationCacheInitializePhase2 () #1 0x0821fa67 in InitPostgres () #2 0x081a7d8b in PostgresMain () #3 0x08184882 in ServerLoop () #4 0x0818545c in PostmasterMain () #5 0x0814be1f in main () Darren LOG: received smart shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2008-02-25 22:42:13 PST LOG: checkpoint record is at 1/FB17328 LOG: redo record is at 1/FB17328; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/1348963; next OID: 16544 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: server process (PID 21040) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2008-02-25 22:42:16 PST LOG: checkpoint record is at 1/FB17328 LOG: redo record is at 1/FB17328; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/1348963; next OID: 16544 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 1/FB17370 LOG: redo is not required LOG: database system is ready LOG: server process (PID 26412) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted at 2008-02-25 22:42:22 PST LOG: checkpoint record is at 1/FB17370 LOG: redo record is at 1/FB17370; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/1348963; next OID: 16544 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 1/FB173B8 LOG: redo is not required LOG: database system is ready