Re: "Could not open relation XXX: No such file or directory"

On Fri, 2009-08-21 at 11:30 +1000, Yaroslav Tykhiy wrote:
> Hi there,
>
> On 19/08/2009, at 8:38 PM, Craig Ringer wrote:
> > You should also `chkdsk' your file system(s) and use a SMART
> > diagnostic tool to test your hard disk (assuming it's a single ATA
> > disk).
>
> By the way, `chkdsk' in Windows or `fsck' in Unix can, in a way, be a
> _source_ of file loss if the file metadata got damaged badly, e.g., by
> a system crash, and the file node has to be cleared. So I've always
> been curious if there is a way to retrieve surviving records from a
> PostgreSQL database damaged by file loss.

Good point and good question.

One thing that'd _REALLY_ help recover PostgreSQL databases would be if
files defining the tables had a header containing:

- A magic number or string
- The PostgreSQL version
- The file path/name relative to the pg data root

eg:

PGSQL84\x00base/11511/2699

That'd be a big bonus if they turned up in lost+found, and would also
assist in recovery of a database from a file system with completely
destroyed or unusable metadata (eg: dead superblocks). Then again, with
the DB files not having end markers and with the potential for file
fragmentation you're probably not going to recover a DB from a
completely mangled FS anyway. Help identifying DB files from lost+found
would be very nice, though.

Of course, we all keep good backups so nobody'll ever _need_ this sort
of thing, right? Right? *sigh*

--
Craig Ringer