Re: Re: Backup and Recovery

On Thu, Jul 05, 2001 at 09:33:17PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > In other words, we keep the page images and row records in one file so
> > we can do one fsync, but once we have written the page, we don't want to
> > store them for later point-in-time recovery.
>
> What we'd want to do is strip the page images from the version of the
> logs that's archived for recovery purposes.

Am I correct in believing that the remaining row images would have to
be applied to a clean table-image snapshot? Maybe you can produce a
clean table-image snapshot by making a dirty image copy, and then
replaying the WAL from the time you started copying up to the time
when you finish copying.

How hard would it be to turn these row records into updates against a
pg_dump image, assuming access to a good table-image file?

> Ideally the archiving process would also discard records from aborted
> transactions, but I'm not sure how hard that'd be to do.

A second pass over the WAL file -- or the log-picker daemon's
first-pass output -- could eliminate the dead row images. Handling
WAL file boundaries might be tricky if one WAL file has dead row-images
and the next has the abort-or-commit record. Maybe the daemon has to
look ahead into the next WAL file to know what to discard from the
current file.

Would it be useful to mark points in a WAL file where there are no
transactions with outstanding writes?

Nathan Myers
ncm(at)zembu(dot)com