(PostgreSQL) ERROR: could not open relation

Question

I get this strange error

Caused by: org.postgresql.util.PSQLException: ERROR: could not open
relation base/17369/1684: No such file or directory

How do I recover from it ? Any ideas ?

Thanks

Answer 1

You lost file related to table 1684 and database 17369. Check presence file with name 1684 in directory base/17369.

You can determine which table it is:

1) get database name: select * from pg_database where oid=17369

2) get table name (connect to affected database): select * from pg_class where oid=1684

Probably there is not much more you can do. Only restore from backup.

Answer 2

There could be a number of causes for this. Your best bet, if it is reasonable, is to restore from backup after running your hardware through diagnostics.

If the restore from backup is not desirable, then you need to stop Postmaster, make a copy of your database, and get professional help asap. Data may or may not be recoverable and you need to know this going in. Additionally I would suggest beyond what the link Craig points you to, in a case like this (where files may be deleted), work on recovering from a copy, preferably a block-level copy of the whole filesystem, on another system.

FWIW I have watched PostgreSQL run on heavily abused hardware and seen many index corruption issues when the hardware was pushed beyond limits (database server above door level in a hot, unventilated closet for example), but this doesn't look like one (index corruption is relatively easy to fix but usually the error messages are a bit different).

Now, additionally, it may be possible to restore data with your current database and a backup. Obviously we won't know this either until one tries it.