Re: inconsistent state after crash recovery

Hi,
I'm very new here on this mailing list, but I've been using PostgreSQL
for a while, and it scares me a little, that it's a real pain to try to
recover data from corrupted table.

Situations like file being lost following server crash (after fsck) or
page corruption happens quite often.

Having corruption at the row level for example, system could mark the
page as corrupted in the system catalog.
Giving that page knows last change to this page, can we use archived
WAL-s to recover the page?
We can have a table inside pg_catalog like pg_corrupted_pages with
information of page corruption detected by backend.

Similar to tables, in a case of lost file, once system notice that, we
should have a column in pg_class called relneedrecovery to record that.
Will it be possible to recover this file from last hot backup and apply
redo to it based on WAL records?

Similar functionality is provider by Oracle (media and block recovery).
I assume we will need some extra DDL commands to handle file/block
recovery.
Also, It would be nice to have a command to quickly cross check files
between pg_class and filesystem - just simple open/close system call for
each relation - it is always faster that to run vaccum to check that.
Tomasz

On 2 August 2013 13:19, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Aug 2, 2013 at 8:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Fri, Jul 26, 2013 at 8:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> would you expect crash recovery to notice the disappearance of a file
> >>> that was touched nowhere in the replayed actions?
> >
> >> Eh, maybe not. But should we try harder to detect the unexpected
> >> disappearance of one that is?
> >
> > We do, don't we? The replay stuff should complain unless it sees a drop
> > or truncate covering any unaccounted-for pages.
>
> Hmm. Yeah. But the OP seems to think it doesn't work.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On 2 August 2013 13:19, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Aug 2, 2013 at 8:17 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> >> On Fri, Jul 26, 2013 at 8:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >>> would you expect crash recovery to notice the disappearance of a file
> >>> that was touched nowhere in the replayed actions?
> >
> >> Eh, maybe not. But should we try harder to detect the unexpected
> >> disappearance of one that is?
> >
> > We do, don't we? The replay stuff should complain unless it sees a drop
> > or truncate covering any unaccounted-for pages.
>
> Hmm. Yeah. But the OP seems to think it doesn't work.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>