Postgres Pro
Downloads
Home   >   mailing lists

Thread: backing up corrupt database

backing up corrupt database

From
Date:
My first post didn't work, so here goes again

I want to backup my database, which appears to be corrupt.  The projects
table is corrupt:
bash$ pg_dump devtest2
pg_dump: Attempt to lock table "projects" failed.  ERROR:  cannot find
attribute
 8 of relation projects

When I try to drop this corrupted table:
devtest2=# drop table projects;
ERROR:  cannot find attribute 8 of relation projects

How can I manually purge this table from the system tables so that I can run
a backup and then recreate the database?

Thanks

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

Re: backing up corrupt database

From
Tom Lane
Date:
<terry@ashtonwoodshomes.com> writes:
> When I try to drop this corrupted table:
> devtest2=# drop table projects;
> ERROR:  cannot find attribute 8 of relation projects

Sounds like a mess.  What PG version is this?  Do you know what happened
to cause the corruption?

> How can I manually purge this table from the system tables so that I can run
> a backup and then recreate the database?

If you do not care about this table, then "DELETE FROM pg_class WHERE
relname = 'projects'" would do it.

If you do care, I'd try a system-table reindex (read the REINDEX man
page carefully) before giving up on it.  The problem is probably just
corruption in one of the indexes on pg_attribute.

            regards, tom lane

Re: backing up corrupt database

From
Richard Huxton
Date:
On Wednesday 30 Oct 2002 3:56 pm, terry@ashtonwoodshomes.com wrote:
> My first post didn't work, so here goes again
>
> I want to backup my database, which appears to be corrupt.  The projects
> table is corrupt:
> bash$ pg_dump devtest2
> pg_dump: Attempt to lock table "projects" failed.  ERROR:  cannot find
> attribute
>  8 of relation projects
>
> When I try to drop this corrupted table:
> devtest2=# drop table projects;
> ERROR:  cannot find attribute 8 of relation projects
>
> How can I manually purge this table from the system tables so that I can
> run a backup and then recreate the database?

Well, pg_class contains the tables - relname holds the name. I've just tried
deleting a table from it and using pg_dump and it seemed to work.

Having said that, I'd be tempted to pg_dump all the other tables individually
and then give it a go - I'm always a little uneasy about tinkering with the
system tables even when the developers say it should be fine.

--
  Richard Huxton