
> It seems to me that you can get the desired results without any
> locking.  Assume that you start archiving the WAL just after a
> checkpoint record.  Also, start copying data files to your backup
> medium.  Some not inconsiderable time later, you are done copying
> data files.  You continue copying off and archiving WAL entries.
> You cannot say that the copied data files correspond to any particular
> point in the WAL, or that they form a consistent set of data at all
> --- but if you were to reload them and replay the WAL into them
> starting from the checkpoint, then you *would* have a consistent set
> of files once you reached the point in the WAL corresponding to the
> end-time of the data file backup.  You could stop there, or continue
> WAL replay to any later point in time.

Yes. But I think you might have to avoid "vacuum full" during data file backup.
And you need the whole WAL including the "page images". Since you have 
those page images you also need not care about backing up a concurrently
written (thus prbbly partial) page (as Tom already pointed out). Key issue 
is restoring or creating a pg_control file, that tells startup recover the 
correct checkpoint and WAL position for startup rollforward.

Actually it would be worth simply testing the following:

0. configure WAL for enough room so it does not wrap around
1. start a lengthy pgbench run or something similar
2. force a checkpoint
3. save pg_control
4. tar PGDATA
5. tar the WAL's

now restore above files in order 4,3,5 start pg and debug :-)
might need to fix last page in WAL.

Andreas

Fri, 22 Feb 2002 11:27:51 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: point in time recovery and moving datafiles online 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> Yes. But I think you might have to avoid "vacuum full" during data file backup.

Why?  If vacuum is unsafe in this scenario, wouldn't it also be unsafe
in event of a system crash?

I do believe that vacuum should (but presently does not) emit a WAL
record showing its truncation of the file, so that the equivalent
truncation can be repeated during replay.  However, this is needed
in any case --- point-in-time recovery simply means replaying WAL on
a slightly longer timescale than is usual for crash-recovery.

> And you need the whole WAL including the "page images".

Check, you can't compress out the page images.

			regards, tom lane

Fri, 22 Feb 2002 09:32:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: point in time recovery and moving datafiles online 