Re: How to reindex when unable to open relation? - Mailing list pgsql-admin

Shoaib Mir wrote:
> On Tue, Feb 26, 2008 at 5:13 PM, Darren Reed
> <darrenr+postgres@fastmail.net <mailto:darrenr+postgres@fastmail.net>>
> 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