Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 9E91B2E0044 for ; Tue, 26 Feb 2008 02:21:20 -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 98298-05 for ; Tue, 26 Feb 2008 02:21:18 -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 2B9312E0041 for ; Tue, 26 Feb 2008 02:21:18 -0400 (AST) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id 55D04A07AF for ; Tue, 26 Feb 2008 01:14:11 -0500 (EST) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute1.internal (MEProxy); Tue, 26 Feb 2008 01:14:11 -0500 X-Sasl-enc: csiHMG5WLJluQat7eMGBBt/qCuT4sm1kA7JYa+T89pwF 1204006450 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 9E5D824D30 for ; Tue, 26 Feb 2008 01:14:10 -0500 (EST) Message-ID: <47C3AE1F.2010804@fastmail.net> Date: Mon, 25 Feb 2008 22:13:51 -0800 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: How to reindex when unable to open relation? 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/333 X-Sequence-Number: 28407 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? Darren ERROR: could not open relation with OID 16399 STATEMENT: INSERT INTO table (cola,colb,colc,cold,cole) VALUES (1,'fred',2,'john',true); LOG: unexpected EOF on client connection ERROR: could not open relation with OID 16399 STATEMENT: SELECT * FROM table WHERE (ignore IS NOT TRUE) AND (region = 'usa') ORDER BY date DESC LIMIT 300 OFFSET 0; ERROR: index "pg_attrdef_adrelid_adnum_index" contains unexpected zero page at block 1 HINT: Please REINDEX it. STATEMENT: LOCK TABLE public.ifl IN ACCESS SHARE MODE FATAL: database "posrgres" does not exist ERROR: could not open relation with OID 16399 STATEMENT: SELECT COUNT(*) FROM table; ERROR: syntax error at or near ";" at character 9 STATEMENT: REINDEX ; ERROR: syntax error at or near "table" at character 9 STATEMENT: REINDEX table; ERROR: syntax error at or near "help" at character 1 STATEMENT: help REINDEX; ERROR: could not open relation with OID 16399 STATEMENT: REINDEX TABLE table; ERROR: syntax error at or near ";" at character 17 STATEMENT: REINDEX DATABASE; NOTICE: table "pg_class" was reindexed NOTICE: table "sql_sizing" was reindexed NOTICE: table "sql_sizing_profiles" was reindexed NOTICE: table "sql_features" was reindexed NOTICE: table "sql_implementation_info" was reindexed NOTICE: table "sql_languages" was reindexed NOTICE: table "sql_packages" was reindexed NOTICE: table "sql_parts" was reindexed NOTICE: table "pg_statistic" was reindexed NOTICE: table "pg_type" was reindexed NOTICE: table "pg_attribute" was reindexed NOTICE: table "pg_proc" was reindexed NOTICE: table "pg_autovacuum" was reindexed NOTICE: table "pg_attrdef" was reindexed NOTICE: table "pg_constraint" was reindexed NOTICE: table "pg_inherits" was reindexed ERROR: could not open relation with OID 16389 STATEMENT: REINDEX DATABASE postgres; NOTICE: table "pg_class" was reindexed NOTICE: table "pg_statistic" was reindexed NOTICE: table "pg_type" was reindexed NOTICE: table "pg_attribute" was reindexed NOTICE: table "pg_proc" was reindexed NOTICE: table "pg_autovacuum" was reindexed NOTICE: table "pg_attrdef" was reindexed NOTICE: table "pg_constraint" was reindexed NOTICE: table "pg_inherits" was reindexed NOTICE: table "pg_index" was reindexed NOTICE: table "pg_operator" was reindexed NOTICE: table "pg_opclass" was reindexed NOTICE: table "pg_am" was reindexed NOTICE: table "pg_amop" was reindexed NOTICE: table "pg_amproc" was reindexed NOTICE: table "pg_language" was reindexed NOTICE: table "pg_largeobject" was reindexed NOTICE: table "pg_aggregate" was reindexed NOTICE: table "pg_rewrite" was reindexed NOTICE: table "pg_trigger" was reindexed NOTICE: table "pg_description" was reindexed NOTICE: table "pg_cast" was reindexed NOTICE: table "pg_namespace" was reindexed NOTICE: table "pg_convtableion" was reindexed NOTICE: table "pg_depend" was reindexed NOTICE: table "pg_class" was reindexed NOTICE: table "sql_sizing" was reindexed NOTICE: table "sql_sizing_profiles" was reindexed NOTICE: table "sql_features" was reindexed NOTICE: table "sql_implementation_info" was reindexed NOTICE: table "sql_languages" was reindexed NOTICE: table "sql_packages" was reindexed NOTICE: table "sql_parts" was reindexed NOTICE: table "pg_statistic" was reindexed NOTICE: table "pg_type" was reindexed NOTICE: table "pg_attribute" was reindexed NOTICE: table "pg_proc" was reindexed NOTICE: table "pg_autovacuum" was reindexed NOTICE: table "pg_attrdef" was reindexed NOTICE: table "pg_constraint" was reindexed NOTICE: table "pg_inherits" was reindexed ERROR: could not open relation with OID 16389 STATEMENT: REINDEX DATABASE postgres; ERROR: could not open relation with OID 16393 STATEMENT: SELECT COUNT(*) FROM picphone; 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:03:28 PST LOG: checkpoint record is at 1/FB15C10 LOG: redo record is at 1/FB15C10; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/1348953; next OID: 16544 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready ERROR: could not open relation with OID 16399 STATEMENT: SELECT COUNT(*) FROM table;