CODEWITHSUNDEEP
postgresqlrecovery
Ricardo Peres
Ricardo Peres

Reputation: 14555

How to Recover PostgreSQL 8.0 Database

On my PostgreSQL 8.0 database, I started receiving a "ERROR: could not open relation 1663/17269/16691: No such file or directory" message, and now my data is inaccessible. Any ideas on how to recover at least some of the data? Professional support is an option.

Regards.

RP

Upvotes: 1

Views: 189

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22952

If you want your data back in a hurry and it's worth something to you, then the professional support option should be simple enough.

Some things to check, now that you've got a full backup of all your database (that's base, pg_clog, pg_xlog and all the other folders at that level).

  1. Does that file actually exist? It might be a permissions problem rather than the file actualy going missing.

  2. Check your anti-virus/security packages - have they mistakenly quarantined the file? If you can exclude PostgreSQL's database directories from scans/active scans that's worthwhile too.

  3. Make a note of everything you can remember about when this happened and what happened just before. This will help with troubleshooting for you or a consultant.

  4. Check the logs likewise - this error will be logged, find the first occurrence and see if there's anything odd before.

  5. Double-check you really do have all your existing files backed up, and restart PostgreSQL.

  6. Try connecting as user postgres to database postgres or database template1. If that works then the file is one of your database files rather than the global list of users or some such.

  7. Try creating an empty file with the right name (and permissions - check the other files). If you are really lucky it's just an index. Otherwise it could be a data table you can live without. Then you can dump other tables individually.

  8. OK - if you're here then you can connect to your DB. Those numbers in the file-path are PostgreSQL's OIDs identifying system objects. You can try a couple of useful queries here. These two queries should give you the IDs of the databases and then the object with the missing file. This is useful information for your professional too.

    SELECT oid, datname, dattablespace FROM pg_database; SELECT * FROM pg_class WHERE relfilenode = 16691;

Remember make sure you have the filesystem backup before tinkering.

Upvotes: 2

Related Questions