Обсуждение: How to salvage a corrupted DB?

Hi,

I have an important database which I have been unable to backup/dump for "a long
while". I am looking for a method to "fix" the problem.  As a last resort, I
guess I could dump the DB table by table using "\copy", although I haven't tried
yet. And I worry that the corrupted system tables may be accessed during a
"\copy" so I may still be stuck.

The problem began when a "vacuum analyse" crashed.  I can no longer run 'vacuum'
without an immediate crash:

> htg=> vacuum;
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> We have lost the connection to the backend, so further processing is impossible.  Terminating.
>
This returns me to the shell prompt.

Okay, what tables are present?

> htg=> \d
> pqReadData() -- backend closed the channel unexpectedly.
>         This probably means the backend terminated abnormally
>         before or while processing the request.
> htg=>
>
Well, not quite such a hard crash; I'm still in psql, however, I am not
connected to any DB an longer.

After reconnecting, I can still read and write user data from this DB, but all
attempts to access the system (pg_*) tables cause this type of message.

I am currently running Postgresql ver 6.5.1 (Patched for 'vacuum' problems and
patched for UNIQUE INDEXES) on an SGI IRIX 6.5 system.  The initial "crash
during vacuuming" occured prior to when I applied the 'vacuum' patch.

If I open up a different DB than this one, I have no problem with accessing the
system tables: '\d' works, pg_dump works, etc.

Suggestions on how to recover the data would be most welcome.

Thanks,
Mark

--
Mark Dalphin                          email: mdalphin@amgen.com
Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
One Amgen Center Drive                       +1-805-375-0680 (home)
Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)

> Hi,
>
> I have an important database which I have been unable to backup/dump for "a long
> while". I am looking for a method to "fix" the problem.  As a last resort, I
> guess I could dump the DB table by table using "\copy", although I haven't tried
> yet. And I worry that the corrupted system tables may be accessed during a
> "\copy" so I may still be stuck.

<snip>

> Okay, what tables are present?
>
> > htg=> \d
> > pqReadData() -- backend closed the channel unexpectedly.
> >         This probably means the backend terminated abnormally
> >         before or while processing the request.
> > htg=>
> >
> Well, not quite such a hard crash; I'm still in psql, however, I am not
> connected to any DB an longer.

At the worst, you could, as you say, dump out the tables using copy.
To get the table list, look in the data directory, and you should see
all you need to know what tables are there

                        ~Michael