Обсуждение: Postmaster cannot start

""Chun Yit(Chronos)"" <ivanbeh@chronos.com.my> wrote
> saw from the log file, it's possible that server crash during
> vacuum process...
>
> Question :
> 1) what happen to my database server? what the error meaning?
>
It looks like index "pg_class_relname_nsp_index" (which is an index on
pg_class) is corrupted. But not sure why it reports the following error
message (which looks like a post-commit cleanup caused error):

    DEBUG:  AbortCurrentTransaction
    PANIC:  cannot abort transaction 14135438, it was already committed

> 2) it is my daily maintenance routine causing that?
No.

> 3) it is ok to run vacuum full every day ?
Yes. There is no apparent bad side effect of vacuum full except that it
would block other accesses on the target table. But since you do it per
day(guess you do it on a nightly schedule), so this should not be a
problem.

> 4) how can i solve this problem?
>
The base table pg_class should be ok(pg_class_oid_ind indicates both
have the same cardinality). Try to reindex pg_class as the superuser.

Regards,
Qingqing

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> But not sure why it reports the following error
> message (which looks like a post-commit cleanup caused error):

>     DEBUG:  AbortCurrentTransaction
>     PANIC:  cannot abort transaction 14135438, it was already committed

I think this is an artifact of the fact that VACUUM FULL commits its own
transaction before it starts the final index cleanup pass.  We ought to
think of a better way to handle that sometime.  I don't recall having
seen a PANIC like this reported before, but on reflection it seems like
this would be guaranteed to happen for any ERROR condition occurring
during that last pass.  An error there would be pretty improbable, but
surely not impossible.

As for the OP's problem, it seems pretty suspicious that we got through
one cycle of vacuuming pg_class_relname_nsp_index and then the second
one failed with what seems to be a bad block link.  If that bad link was
there before, why didn't it fail the first time through?  I'm wondering
about flaky hardware ...

            regards, tom lane

>> 4) how can i solve this problem?
>>
> The base table pg_class should be ok(pg_class_oid_ind indicates both have
> the same cardinality). Try to reindex pg_class as the superuser.
>
>>
>but not i not be able to reindex the table because i cannot start the
>postmaster.
>postmaster give me error every time i try to start it
>this is the error msg
DEBUG:  found "/usr/local/pgsql/bin/postgres" using argv[0]
LOG:  could not create IPv6 socket: Address family not supported by protocol
DEBUG:  max_safe_fds = 985, usable_fds = 1019, already_open = 5
DEBUG:  found "/usr/local/pgsql/bin/postmaster" using argv[0]
LOG:  could not resolve "localhost": Temporary failure in name resolution
LOG:  database system was interrupted while in recovery at 2006-05-15
04:14:39 MYT
HINT:  This probably means that some data is corrupted and you will have to
use the last backup for recovery.
LOG:  checkpoint record is at A/46315F10
LOG:  redo record is at A/46315F10; undo record is at 0/0; shutdown FALSE
LOG:  next transaction ID: 14135366; next OID: 51809028
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  redo starts at A/46315F50
PANIC:  btree_delete_page_redo: uninitialized right sibling
LOG:  startup process (PID 5043) was terminated by signal 6
LOG:  aborting startup due to startup process failure

>Regards
>Beh

> "Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
>> But not sure why it reports the following error
>> message (which looks like a post-commit cleanup caused error):
>
>>     DEBUG:  AbortCurrentTransaction
>>     PANIC:  cannot abort transaction 14135438, it was already committed
>
> I think this is an artifact of the fact that VACUUM FULL commits its own
> transaction before it starts the final index cleanup pass.  We ought to
> think of a better way to handle that sometime.  I don't recall having
> seen a PANIC like this reported before, but on reflection it seems like
> this would be guaranteed to happen for any ERROR condition occurring
> during that last pass.  An error there would be pretty improbable, but
> surely not impossible.
>
>
>>>I have check my past postgresql log file, i did not see this error come
>>>out (i did vacuum full every night).
>
>
> As for the OP's problem, it seems pretty suspicious that we got through
> one cycle of vacuuming pg_class_relname_nsp_index and then the second
> one failed with what seems to be a bad block link.  If that bad link was
> there before, why didn't it fail the first time through?  I'm wondering
> about flaky hardware ...
>
>>>Any tool to check am i having a flaky hardware?
>>>
>>>Regards
>>>Beh

"Chun Yit\(Chronos\)" <ivanbeh@chronos.com.my> writes:
> PANIC:  btree_delete_page_redo: uninitialized right sibling
> LOG:  startup process (PID 5043) was terminated by signal 6
> LOG:  aborting startup due to startup process failure

That's pretty ugly :-(.  I think your only hope to get out of it is to
use pg_resetxlog.  After that, you will undoubtedly need to reindex
pg_class in the problem database.  After that, if you're very lucky,
you'll be able to pg_dump.  If you can get a clean dump, then initdb
and reload --- it would not be wise to keep using the database.

I'd suggest first taking a filesystem backup of the $PGDATA directory as
it stands, so you can get back to where you are if this fails to work.

Assuming you can get back up and running, you should look into (a) why
you are running an old, known-buggy PG release, and (b) whether you have
flaky hardware, as per comments already made upthread.

            regards, tom lane

""Chun Yit(Chronos)"" <ivanbeh@chronos.com.my> wrote
> postmaster give me error every time i try to start it
> LOG:  redo starts at A/46315F50
> PANIC:  btree_delete_page_redo: uninitialized right sibling
>

So the last resort I can think of is to use pg_resetxlog to pass the startup
failure -- but no gaurantee that your database is still consistent after
that :-(. So before you do that, make a copy of your database for backup.

Maybe a deeper look into the problem is not needed, since 7.4.5 is too old,
and try to use some newer version.

Regards,
Qingqing

> What i done is i use the last backup file and restore it on new $PGData
> Directory, now it
> ok. just curious how this thing can happen? maybe i asking a stupid
> question since i using
> quite old Postgresql Version but hoping this problem won't come back
> again.
> Thanks for your help to clear some doubt on my mind.

> Regards
> Beh


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Chun Yit(Chronos)" <ivanbeh@chronos.com.my>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, May 16, 2006 1:03 PM
Subject: Re: [GENERAL] Postmaster cannot start


> "Chun Yit\(Chronos\)" <ivanbeh@chronos.com.my> writes:
>> PANIC:  btree_delete_page_redo: uninitialized right sibling
>> LOG:  startup process (PID 5043) was terminated by signal 6
>> LOG:  aborting startup due to startup process failure
>
> That's pretty ugly :-(.  I think your only hope to get out of it is to
> use pg_resetxlog.  After that, you will undoubtedly need to reindex
> pg_class in the problem database.  After that, if you're very lucky,
> you'll be able to pg_dump.  If you can get a clean dump, then initdb
> and reload --- it would not be wise to keep using the database.
>
> I'd suggest first taking a filesystem backup of the $PGDATA directory as
> it stands, so you can get back to where you are if this fails to work.
>
> Assuming you can get back up and running, you should look into (a) why
> you are running an old, known-buggy PG release, and (b) whether you have
> flaky hardware, as per comments already made upthread.
>
> regards, tom lane
>