Re: Detecting corrupted pages earlier - Mailing list pgsql-hackers

On 2003-04-02 16:18:33 -0500, Tom Lane wrote:
> Kevin Brown <kevin@sysexperts.com> writes:
> > Hmm...I don't know that I'd want to go that far -- setting this
> > variable could be regarded as a policy decision.  Some shops may have
> > very good reason for running with ZERO_DAMAGED_PAGES enabled all the
> > time, but I don't know what those reasons might be.
> 
> I would buy this argument if I could imagine even a faintly plausible
> reason for doing that ... but I can't.
> 
>             regards, tom lane
> 

I've been following this discussion with great interest, because I actually
have a situation where running with ZERO_DAMAGED_PAGES on all the time would
be somewhat plausible.

We use a PostgreSQL database purely for caching pages for a very busy website.
A user changes some stuff which causes a page on the site to change, the HTML
for the new page gets generated using the data from another database
(containing all the actual data) and the generated HTML is inserted into this
PG database. When a page is requested that isn't cached yet, it'll be
generated and inserted too. This makes it possible to invalidate the
cache-version of a large amount of pages by simply deleting the relevant rows
and not spending the time to regenerate all that data immediately (and it
makes crashrecovery more robust).

We can afford to lose all the data in the cache DB, because it's all generated
by using other data anyway. But losing all data would be bad from a
performance/uptime perspective, as all the cached data would need to be
regenerated (which takes a few days). Also, making backups once a day and
restoring such a backup when something goes wrong is also impractical, because
in our situation old data is much worse than no data at all. I'm working on a
script to detect old data and delete it so a new page will be generated, but
that isn't finished yet.

Two weeks ago the server running this database screwed up (it crashes pretty
badly) and made some data unreadable. Although I was running with fsync on on
an ext3 partition (with data=writeback, linux 2.4.20, PG 7.2) some of the PG
datafiles got damaged anyway (I blame IDE disks). The damage seemed light
enough to keep running with this dataset (it occasionally borked with
'heap_delete: (am)invalid tid', but since our application attempts a delete
followed by an insert of newly generated data in case of a db error it would
repair itself most of the time). Two crashes later (weirdly patched kernels
hooray) the errors got progressively worse ('missing chunk number 0 for toast
value 79960605' and stuff like that) so we were forced to shut the website
down, dump all the data we could dump (not everything), initdb and restore
that dump. This cost us about 10 hours downtime. If I'd had the option I just
would've set ZERO_DAMAGED_PAGES to true and let it run for a few days to sort
itself out. Alternatively an ALTER TABLE foo ZERO DAMAGED PAGES; would've
worked as well, although that would create a small downtime too.

I know I'm doing a lot of weird things, and that I could avoid a lot of the
problems listed here were I to do things differently, but the fact remains
that I actually have a real-life situation where running (for a while at
least) with ZERO_DAMAGED_PAGES on makes some kind of sense.

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/