How to find the OID of the rows in a table in postgres?

Question

I have a problem encountered lately in our Postgres database, when I query: select * from myTable, it results to, 'could not open relation with OID 892600370'. And it's front end application can't run properly anymore. Base on my research, I determined the column that has an error but I want exactly to locate the rows OID of the column so that I can modify it. Please help.

Thank you in advance.

Could it be that a file is missing from somewhere like: `/var/lib/pgsql/data`? — Dwayne Towell, Sep 06 '14 at 02:53
Considering that the file is missing. Can you help me on some ways how can I restore it? that directory doesn't exist either. — jorge, Sep 06 '14 at 03:03
I can't dump it because the same error occurs, I can't dump that table, it's getting aborted. — jorge, Sep 06 '14 at 05:24

score 1 · Accepted Answer · answered Sep 06 '14 at 08:31

You've got a corrupted database. Might be a bug, but more likely bad hardware. If you have a recent backup, just use that. I'm guessing you don't though.

Make sure you locate any backups of either the database or its file tree and keep them safe.
Stop the PostgreSQL server and take a file backup of the entire database tree (base, global, pg_xlog - everything at that level). It is now safe to start fiddling...
Now, start the database server again and dump tables one at a time. If a table won't dump, try dropping any indexes and foreign-key constraints and give it another go.
For a table that won't dump, it might be just certain rows. Drop any indexes and dump a range of rows using COPY ... SELECT. That should let you narrow down any corrupted rows and get the rest.
Now you have a mostly-recovered database, restore it on another machine and take whatever steps are needed to establish what is damaged/lost and what needs to be done.
Run a full set of tests on the old machine and see if anything needs replacement. Consider whether your monitoring needs improvement.

Then - make sure you keep proper backups next time, that way you won't have to do all this, you'll just use them instead.

score 0 · Answer 2 · answered Sep 06 '14 at 13:22

could not open relation with OID 892600370

A relation is a table or index. A relation's OID is the OID of the row in pg_class where this relation is defined.

Try select relname from pg_class where oid=892600370;

Often it's immediately obvious from relname what this relation is, otherwise you want to look at the other fields in pg_class: relnamespace, relkind,...

How to find the OID of the rows in a table in postgres?

2 Answers2