Re: "Could not open relation XXX: No such file or directory" - Mailing list pgsql-general

From Craig Ringer
Subject Re: "Could not open relation XXX: No such file or directory"
Date
Msg-id 1250910525.29528.11.camel@ayaki
Whole thread Raw
In response to Re: "Could not open relation XXX: No such file or directory"  (Yaroslav Tykhiy <yar@barnet.com.au>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Tim Landscheidt
Date:
Subject: Re: bytea corruption?
Next
From: xaviergxf
Date:
Subject: Improving Full text performance