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.

Good point. You are going to need a tar image of the data files to
restore via WAL and skip all WAL records from before the tar image. WAL
does some of the tricky stuff now as part of crash recovery but it gets
more complited for a point-in-time recovery because the binary images
was taken over time, not at a single point in time like crash recovery.

>
> 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?

pg_dump is very hard because WAL contains only tids. No way to match
that to pg_dump-loaded rows.

> > 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?

I think CHECKPOINT is as good as we are going to get in that area, but
of course there are outstanding transactions that are not going to be
picked up because they weren't committed before the checkpoint
completed.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026