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

On 21/08/2009, at 12:40 PM, Seth Gordon wrote:

> Yaroslav Tykhiy wrote:
>> 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.  Do you
>> know any?  (Of course, the only true solution is to have been
>> making backups beforehand, but...)
>
> The Ubuntu Linux site has this page on data recovery (also
> applicable to other Linux flavors):
>
> https://help.ubuntu.com/community/DataRecovery
>
> I assume that a database file, because of its structure, is harder
> to recover after it becomes corrupt than, say, an XML file.  But any
> port in a storm, right?

Excuse me, but my curiosity was about a somewhat different thing.
Let's assume we did file system level data recovery but lost just a
couple of files from $PGDATA/base that were damaged hopelessly.  Now,
if we start pgsql and try accessing the database, pgsql will fail as
soon as it hits a missing file.  So I wondered if there was a way to
tell pgsql to ignore such errors at the cost of returning possibly
inconsistent and corrupted data.  It has just occurred to me that
recreating the files zero-filled is another option to try.  As long as
the objects stored in the database are small and/or uncompressed,
screwing up a few pages shouldn't affect data from the other pages,
right?

Yar