How to reindex when unable to open relation?

From: Darren Reed <darrenr+postgres(at)fastmail(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: How to reindex when unable to open relation?
Date: 2008-02-26 06:13:51
Message-ID: 47C3AE1F.2010804@fastmail.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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;

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Darren Reed 2008-02-26 06:19:15 Retrieving data from the data files.
Previous Message Suresh Gupta VG 2008-02-26 06:08:19 Latest postgres stable version