PITR Command FAQ

J.R.Nield -- 2002-08-11

NOTE: 
This is interim documentation until an offical manual page has been written.


____________   ONLINE BACKUP COMMANDS _______________


COMMAND:                  ALTER SYSTEM BACKUP TO backup_directory_path

The 'ALTER SYSTEM BACKUP TO' command copies all the databases and configuration
files from the current active database system into the directory
'backup_directory_path'.

The active database files are 'fuzzy' copies, which require recovery before
use.

The log is then forced to advance to the next log segment (file), and all the
log files required for recovery are copied from the archive log of the
current system into the pg_xlog directory of the backup system.

A control file is built in the backup system that will cause the database
to enter INTERACTIVE RECOVERY MODE when it is next started. INTERACTIVE
RECOVERY MODE must be run from a standalone backend. For example, from the
command line type:
   postgres -D 'backup_directory_path' template1

Where 'backup_directory_path' is the same directory as specified in the
original 'ALTER SYSTEM BACKUP TO' command.

One can remain in INTERACTIVE RECOVERY MODE indefinitely, and continue to
apply log files from the original database. 


COMMAND:				ALTER SYSTEM BACKUP FORCE NEXT

The 'ALTER SYSTEM BACKUP FORCE NEXT' enters backup mode, forces the log
to advance to the next segment and archives the current logfile.

This logfile can then be applied to another database running in INTERACTIVE
RECOVERY MODE.


____________  INTERACTIVE RECOVERY MODE ______________

When you start a cloned database created by the 'ALTER SYSTEM BACKUP' command,
the database will refuse to start under the postmaster.  You must start a
standalone backend to use the database.

When you start a standalone backend on a cloned database, the backend will
enter INTERACTIVE RECOVERY MODE. You will be prompted to enter recovery
commands at the command line, which looks like this:
	recovery>

The following commands are available.


COMMAND:				ALTER SYSTEM RECOVER 
								[FROM source_directory] 
								[TO xlogid/xrecoff]


This command will begin applying log entries from the checkpoint entry
created at the start of the 'ALTER SYSTEM BACKUP' command that created
this database.

It will search for log files in 'source_directory' to get log entries from.
If no 'FROM source_directory' clause is specified, then the 'pg_xlog'
directory is used as the default.

When all the log entries in a file have been applied, and the next file
can't be found in 'source_directory', recovery stops and you are returned
to the 'recovery>' prompt. You can then issue more 'ALTER SYSTEM RECOVER'
commands.

If a log file is found that has room for more log entries, log application
will never progress beyond that point. This can only occur if you
attempt to recover from an online log file. Recovery from a properly archived
log file will never result in an inability to make progress.

If you inadvertently include an online log file of the original database
in your recovery, you may terminate the recovery session and wait until
that logfile has become full (or has been forced to advance). Then you may
apply the logfile and any subsequent to it.

If your original database has been destroyed, but you have the online logfile
from that database, then you will want to apply it. Since there are no more
logfiles beyond the online one, you have no need to continue after that. You
should then issue the 'ALTER SYSTEM RECOVER COMMIT' command, and bring the
backup database online in production mode.

COMMAND:				ALTER SYSTEM RECOVER COMMIT

After a series of 'ALTER SYSTEM RECOVER' commands, you must issue an
'ALTER SYSTEM RECOVER COMMIT' before you may bring the database into
production mode.

Once you have issued an 'ALTER SYSTEM RECOVER COMMIT' command, you may
not apply further recovery to the database, except as part of automatic
crash recovery.

SCENERIOS:

1) 'Following' the archive of an original database:

	One database archives its logfiles into a directory 'D', and
	a backup of that database is started in interactive recovery
	mode. Every so-often, the command ALTER SYSTEM RECOVER FROM 'D'
	is issued to the backup database. The backup will be behind the
	original by some indeterminate amount of time.

	If you wish to bound the time by which the backup can fall behind
	the original, you can issue an ALTER SYSTEM BACKUP FORCE NEXT command
	on the original database at some fixed interval.

	If the original database suffers catastrophic data loss and crashes, but
	the online log files are intact, you can recover from the online log files,
	and bring the backup system into production with ALTER SYSTEM RECOVER
	COMMIT. All transactions that committed before the crash will be applied
	to the backup.

2) 'Branching' from a bad decision:

	If you create a backup of a database, and continue to archive the 
	log files from the original database, you may choose to apply only
	a certian number of the archived logs to the backup, and then bring
	the backup online. For example, if someone drops an important table 
	in the original, you can use this method to 'back-up' to before the 
	command occurred.

	TODO: FEATURE: Let the user specify a specific LSN to stop at.
	Log SQL command text, and give the user a tool to find LSN's based
	on the SQL command text from the log.

