PITR Recovery

...on the assumption we now have archived xlogs, how do we do recovery?

Default is to put all xlogs back into pg_xlog and then let recovery do
its work...though clearly we all want finer specification than that.

Based upon all our discussions to date...I propose to:
1. put more verbose instrumentation around recovery, so we can see how
recovery progresses and calculate an estimated recovery time
2. put in a recovery control command
3. put in a validation step that will check to see whether there are any
missing transaction log files in the sequence of xlogs available

The recovery control command would:
- read the file DataDir/recovery.conf (placed there by DBA)
- parse out an SQL-like command string
ROLLWARD object target finalaction;

e.g. 
ROLLFORWARD DATABASE TO END OF LOGS;
(is the current, and would be default, behaviour if file absent)

ROLLFORWARD DATABASE TO TIMESTAMP '2004-06-11-23:58:02.123' EXCLUSIVE;

ROLLFORWARD DATABASE TO END OF LOGS THEN PAUSE

SYNTAX
object = DATABASE (default) | TABLESPACE
target = END OF LOGS (default)| TO TIMESTAMP 'yyyy-mm-dd-hh:mm:ss.sss' edge
edge = INCLUSIVE (default) | EXCLUSIVE
finalaction = THEN START (default)| THEN PAUSE

-object refers to the part of the database (or whole) that is to be
recovered
-target specifies whether to stop, and what test we will use
-edge refers to whether we use <= or < on the test for target
-finalaction refers to what to do when target is reached - the purpose
of this is to allow recovery of a database to occur when we don't have
enough space for all of the xlogs at once, so we need to do recovery in
batches.

When recovery is complete, recovery.conf would be renamed to
recovery.done, so it would not be reactivated if we restart.

In time for beta freeze, I think it is possible to do a limited subset
of the above:
- implement DATABASE only (whole instance, not specific database)
- implement END OF LOGS and TO TIMESTAMP
- implement THEN START only
- implement using simple C, rather than bison

Reading the command is probably the hardest part of this, so agreeing
what we're working towards is crucial. We're out of time to redesign
this once its coded.

If the hooks are there, we can always code up more should it be required
for a particular recovery...

The syntax is very like DB2, but is designed to be reminiscent of other
systems that give you control over rollforward recovery (e.g. Oracle
etc), allowing those with experience to migrate easily to PostgreSQL.

Implementation wise, I would expect all of this code to go in xlog.c,
with the recovery target code living in ReadRecord(). This would delve
inside each record to check record type, then if it is a COMMIT record
to look further at the timestamp then either implement this COMMIT or
not according to INCLUSIVE/EXCLUSIVE.
Only the txn boundary records have time stamps... 

As Tom points out, we can't accept normal SQL at this point, nor can we
easily achieve this with command line switches or postgresql.conf
entries. My solution is to just use another .conf file (call it what you
like...)

Comments?

Best Regards, Simon Riggs