pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems with missing OIDs
4+ messages / 2 participants
[nested] [flat]

* Problems with missing OIDs
@ 2007-11-02 22:11  Alexander Lohse <al@humantouch.de>
  0 siblings, 1 reply; 4+ messages in thread

From: Alexander Lohse @ 2007-11-02 22:11 UTC (permalink / raw)
  To: pgsql-admin

Hi,

after a disk-crash some weeks ago and a successful recovery, I now  
find some strange issues in postgres.
I am not sure they are related to the crash, but I figure chances  
are ...

One of them is: When trying to vacuum one database I get the following  
message:
"ERROR:  could not open relation with OID 36893600"
After browsing the archives I found similar threads and possible  
recovery methods.

So, I already reindexed and vacuumed all pg_catalog, with no effect.

Now I searched and found a row in pg_index with

indexrelid => 36893600

Is it save to just drop this row? Or how do I find out which index is  
affected?

Any help is very much apprechiated as this seems to affect our backup- 
dumping as well!

Best regards,

Alex
______________________________________________________________

Alexander Lohse • Entwicklungsleitung & Projektmanagement
Tel +49 38374 752 11 • Fax +49 38374 752 23
http://www.humantouch.de

Human Touch Medienproduktion GmbH
Am See 1 • 17440 Klein Jasedow • Deutschland

Geschäftsführung:
Lara Mallien, Nele Hybsier, Alexander Lohse, Johannes Heimrath (Senior)
Handelsregister Stralsund • HRB 4192 • USt-IdNr. DE128367684





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Problems with missing OIDs
@ 2007-11-03 17:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alexander Lohse <al@humantouch.de>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 2007-11-03 17:00 UTC (permalink / raw)
  To: Alexander Lohse <al@humantouch.de>; +Cc: pgsql-admin

Alexander Lohse <al@humantouch.de> writes:
> after a disk-crash some weeks ago and a successful recovery, I now  
> find some strange issues in postgres.
> I am not sure they are related to the crash, but I figure chances  
> are ...

That really shouldn't have happened in the first place.  Are you running
an up-to-date Postgres version?  Are you sure your disk drives don't lie
about write completion?

> One of them is: When trying to vacuum one database I get the following  
> message:
> "ERROR:  could not open relation with OID 36893600"

> Now I searched and found a row in pg_index with
> indexrelid => 36893600
> Is it save to just drop this row? Or how do I find out which index is  
> affected?

Well, if the pg_class row with that OID is gone, then there's no direct
way to know.  But you should be able to look at the row referenced by
indrelid, to find out which table it was an index *of*.  Hopefully you
know your schema well enough to figure out which of its indexes is
missing.

I would recommend a dump/initdb/reload cycle to try to detect and clean
up any other corruption.  Loss of just one pg_class row doesn't seem
like a very probable failure, so I'm afraid you may have more problems.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Problems with missing OIDs
@ 2007-11-04 10:51  Alexander Lohse <al@humantouch.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Alexander Lohse @ 2007-11-04 10:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

Hi Tom,

> Alexander Lohse <al@humantouch.de> writes:
>> after a disk-crash some weeks ago and a successful recovery, I now
>> find some strange issues in postgres.
>> I am not sure they are related to the crash, but I figure chances
>> are ...
>
> That really shouldn't have happened in the first place.  Are you  
> running
> an up-to-date Postgres version?  Are you sure your disk drives don't  
> lie
> about write completion?

the server is running PostgreSQL 7.4. The disks where replaced by our  
hosting partners, so I do not know anything about their internal state.

What else could produce such a mess?

>> One of them is: When trying to vacuum one database I get the  
>> following
>> message:
>> "ERROR:  could not open relation with OID 36893600"
>
>> Now I searched and found a row in pg_index with
>> indexrelid => 36893600
>> Is it save to just drop this row? Or how do I find out which index is
>> affected?
>
> Well, if the pg_class row with that OID is gone, then there's no  
> direct
> way to know.  But you should be able to look at the row referenced by
> indrelid, to find out which table it was an index *of*.  Hopefully you
> know your schema well enough to figure out which of its indexes is
> missing.

Yes, that was easy to find.

The index for a primary key contstraint on that table is missing.

I also cannot drop and recreate that constraint!

Can I just go ahead and drop the corresponding rows from pg_contraint  
and pg_index?

> I would recommend a dump/initdb/reload cycle to try to detect and  
> clean
> up any other corruption.  Loss of just one pg_class row doesn't seem
> like a very probable failure, so I'm afraid you may have more  
> problems.

Currently I cannot create any dump because postgres just stops at the  
error. ;-(

So, our backup scripts are all broke ... :-(

Thank you,

Alex
______________________________________________________________

Alexander Lohse • Entwicklungsleitung & Projektmanagement
Tel +49 38374 752 11 • Fax +49 38374 752 23
http://www.humantouch.de

Human Touch Medienproduktion GmbH
Am See 1 • 17440 Klein Jasedow • Deutschland

Geschäftsführung:
Lara Mallien, Nele Hybsier, Alexander Lohse, Johannes Heimrath (Senior)
Handelsregister Stralsund • HRB 4192 • USt-IdNr. DE128367684





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Problems with missing OIDs
@ 2007-11-04 15:54  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alexander Lohse <al@humantouch.de>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2007-11-04 15:54 UTC (permalink / raw)
  To: Alexander Lohse <al@humantouch.de>; +Cc: pgsql-admin

Alexander Lohse <al@humantouch.de> writes:
>> That really shouldn't have happened in the first place.  Are you  
>> running
>> an up-to-date Postgres version?  Are you sure your disk drives don't  
>> lie
>> about write completion?

> the server is running PostgreSQL 7.4. The disks where replaced by our  
> hosting partners, so I do not know anything about their internal state.

7.4.what?

> The index for a primary key contstraint on that table is missing.
> I also cannot drop and recreate that constraint!
> Can I just go ahead and drop the corresponding rows from pg_contraint  
> and pg_index?

Yeah, what you'll have to do is remove individual rows from the catalogs
until you can get pg_dump to run without error.  Then reload into a
fresh database and check for signs of inconsistency, missing data or
tables, etc.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2007-11-04 15:54 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-02 22:11 Problems with missing OIDs Alexander Lohse <al@humantouch.de>
2007-11-03 17:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-11-04 10:51   ` Alexander Lohse <al@humantouch.de>
2007-11-04 15:54     ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox