Re: point in time recovery and moving datafiles online

> 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