Re: Review of last summer's PITR patch

On Thu, 2004-02-12 at 02:41, Tom Lane wrote:
> I've done some preliminary work on the PITR patch that J.R. Nield
> developed last summer.  I've applied parts of it (in rather edited
> form) but there are big chunks I think are not ready to go.  You
> can see the original patch in pgsql-patches --- I sent it there
> mostly to get it into the archives.
>
> I have committed the parts that have to do with making the contents of
> WAL more robust for PITR purposes; specifically, labeling WAL segment
> files clearly, and adding WAL logging of file creation/deletion.
>
> I have not committed the massive reorganization of xlog.c that appears
> in J.R.'s patch; I think it's unnecessary and likely to introduce bugs
> (certainly it would revert some recent bug fixes).
>
> I have also not committed the ALTER SYSTEM BACKUP / ALTER SYSTEM RECOVER
> commands that appear in the patch.  It is not clear to me that this
> functionality belongs in the backend rather than separate management
> utilities, and even if it does belong there, this doesn't seem a clean
> way to do it.  On the backup side, it seems like this code is basically
> reinventing the tar(1) command.  On the restore side, I don't care for
> the "interactive command" implementation of ALTER SYSTEM RECOVER ---
> that seems like it makes it unnecessarily hard to drive the recovery
> process from another program.  I envision this process being controlled
> by some kind of higher-level management utility, so I'd prefer to see a
> program-friendly API instead of one designed for manual use.
>
> Anyway, I'm hoping to see some discussion of what to do next and what
> the PITR functionality ought to look like from a user's standpoint.

As a user of this functionality the first thing I would want to do is
look at a certain point in time, say yesterday around 3:30 pm and get a
window of transactions made at that time.  So maybe an object method or
function call which passes the following parameters:  1) Time, 2) number
of transactions to return with time being the middle of that window.

i.e. $transaction_list = $transaction_log_object->transaction_window({
time => '23:59:59.99-12', window => '150' });

Which returns 150 transactions centered around 11:59:59.  If the
transaction window exceeds the end of the transaction log move the
window back accordingly.  Once I have that list I would want to look at
the individual transactions and determine which one I want to roll the
database to.