Обсуждение: Corrupted DB - Help

Running postgres 8.1.2 on gentoo linux 2.6.14

Server crashed (not sure if due to pg operation).  Upon rebooting server and trying to start postgres, get the following log entries:

<4983:2006-07-27 18:04:24 PDT>LOG:  database system was interrupted at 2006-07-27 17:58:51 PDT
<4983:2006-07-27 18:04:24 PDT>LOG:  checkpoint record is at 2A4/2A23F328
<4983:2006-07-27 18:04:24 PDT>LOG:  redo record is at 2A4/2A23F328; undo record is at 0/0; shutdown FALSE
<4983:2006-07-27 18:04:24 PDT>LOG:  next transaction ID: 70649528; next OID: 345957
<4983:2006-07-27 18:04:24 PDT>LOG:  next MultiXactId: 1742; next MultiXactOffset: 3498
<4983:2006-07-27 18:04:24 PDT>LOG:  database system was not properly shut down; automatic recovery in progress
<4983:2006-07-27 18:04:24 PDT>LOG:  redo starts at 2A4/2A23F36C
<4983:2006-07-27 18:04:24 PDT>WARNING:  could not truncate relation 1663/128873/129263 to 398 blocks: Success
<4984:2006-07-27 18:04:24 PDT>LOG:  connection received: host=[local]
<4984:2006-07-27 18:04:24 PDT>FATAL:  the database system is starting up
<4981:2006-07-27 18:04:24 PDT>DEBUG:  forked new backend, pid=4984 socket=7
<4981:2006-07-27 18:04:24 PDT>DEBUG:  server process (PID 4984) exited with exit code 0
<4985:2006-07-27 18:04:25 PDT>LOG:  connection received: host=[local]
<4985:2006-07-27 18:04:25 PDT>FATAL:  the database system is starting up
<4981:2006-07-27 18:04:25 PDT>DEBUG:  forked new backend, pid=4985 socket=7
<4981:2006-07-27 18:04:25 PDT>DEBUG:  server process (PID 4985) exited with exit code 0
<4983:2006-07-27 18:04:26 PDT>PANIC:  could not open relation 1663/128873/129201: No such file or directory
<4981:2006-07-27 18:04:26 PDT>LOG:  startup process (PID 4983) was terminated by signal 6
<4981:2006-07-27 18:04:26 PDT>LOG:  aborting startup due to startup process failure
<4982:2006-07-27 18:04:26 PDT>LOG:  logger shutting down


The last commmand that I did right before the crash was a vacuum full on a very small table.  After it completed, I exited psql, tried a non-pg command and it hung up.  Here are the last log entries before the crash:

<1054:2006-07-27 18:00:18 PDT>LOG:  statement: vacuum full verbose port_etr_state_offline;
<1054:2006-07-27 18:00:18 PDT>INFO:  vacuuming "public.port_etr_state_offline"
<1054:2006-07-27 18:00:18 PDT>INFO:  "port_etr_state_offline": found 0 removable, 213 nonremovable row versions in 16 pages
<1054:2006-07-27 18:00:18 PDT>DETAIL:  0 dead row versions cannot be removed yet.
        Nonremovable row versions range from 45 to 45 bytes long.
        There were 2445 unused item pointers.
        Total free space (including removable row versions) is 109896 bytes.
        12 pages are or will become empty, including 0 at the end of the table.
        16 pages containing 109896 free bytes are potential move destinations.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
<1054:2006-07-27 18:00:18 PDT>INFO:  "port_etr_state_offline": moved 213 row versions, truncated 16 to 2 pages
<1054:2006-07-27 18:00:18 PDT>DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
<1054:2006-07-27 18:00:18 PDT>LOG:  duration: 16.078 ms

Any way to salvage this db?

Thanks,

Mark

"Mark Liberman" <mliberman@mixedsignals.com> writes:
> <4983:2006-07-27 18:04:26 PDT>PANIC:  could not open relation =
> 1663/128873/129201: No such file or directory
> <4981:2006-07-27 18:04:26 PDT>LOG:  startup process (PID 4983) was =
> terminated by signal 6

That's really strange ... it's not supposed to be possible to get that
error during xlog replay, because XLogOpenRelation is always willing to
create a file that doesn't exist.  Can you get a stack trace from the
crash dump this presumably left behind?

If you're just in a panic to get the DB up again, I'd try creating the
mentioned file ("touch $PGDATA/base/128873/129201") and see if it'll
start.  But if you have some time to investigate, it'd be good to try
to determine exactly what went wrong here.

            regards, tom lane