Обсуждение: Problems with missing OIDs
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
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
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
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