PITR Backup state validity checking - Mailing list pgsql-hackers

Problem Summary (from previous posts)
The archive recovery must stop AFTER the end of the backup which the
recovery used as its "starting point". If not, incorrect database states
are likely.

In general, this is a small window for error and procedures should exist
to return to the prior backup. Nonetheless, this check should be made
i.e. stop time/point > backup end time

Solution Design:
Before a backup is taken, write a file to data directory that identifies
which backup this is. When the backup is taken this file will be copied
with the backup, and later restored when the backup is restored.
When backup completes write a file to xlog directory that contains the
start backup identifier and the end time. When recovery occurs the
backup identifier can be used to find the end backup file and read this
to find the end backup time.

Additional aspects:
- Can't assume that archive allows direct access, so anything written to
log must be read in sequential order it was written. 
- Backup may be taken when postmaster is down, so solution must not rely
on postmaster being up.
- It *is* posssible to do incremental backups, as long as the backup
checks each file's change data against files already archived (or on
write-once media). The previously backed-up files are thus able to be
considered as being part of *this* backup as well as the one in which
the backup took place. (So we still write start now and end shortly
afterwards).
- We want to offer the user an interface now, so that when later changes
occur, we will not be requiring them to change again.

Implementation Options:
------------------------
User Interface
Two user interfaces have been suggested:
- Write a server function which can be called from anywhere...
- Write an external program

External program will still work when postmaster is down, so is the
option suggested in further detail here...

- Call Sequence
It has been suggested that there should be an "API call" issued before
and after the backup. That requires the user to issue 3 calls in
sequence to get a correct backup.
- For full backups taken all at once, a single call is desirable,
ensuring that no API call was missed.

Implementation Design
----------------------
Implement an external program, called pg_backup. (I guess there's some
historical baggage there, but may be time to leave that behind now....)

pg_backup will do:
1. If postmaster is up, issue a manual CHECKPOINT
2. Write a file called backup_start_<backupid>.info
where <backupid> is the time when backup starts
contains: systemid, time(now)
3. Remove all previous backup_start*.info files
4. Issue the users backup_command via system(3)
5. Write a second file called backup_end_<backupid> to pg_xlog AND write
a backup_end_<backupid>.ready to archive_status. 
backup_end_<backupid> contains: systemid, time of backup end
backup_end_<backupid>.ready is empty

Other changes:
- Alter archiver to always archive backup_end* files first, so they are
written to archive in time sequence order.
- Alter recovery so that it requests backup_end_<backupid> first. We
then read the time in this file and compare with our end time, if there
is one. If there is and we fail the > test, we stop. If no target time
exists, we rollforward though can still fail the test at our selected
stopping point (an Xid).
- If recovery ends at an Xid, but when this is reached we are still less
than backup end time, then we alter our target to being the backup end
time (inclusive) and continue to roll forward. WARNING issued.
- If recovery ends before it has read the backup_end* file then we issue
a WARNING error saying "recovery using incomplete backup", HINT:"you
will need to start recovery from the next earliest backup". (Later
change this to an ERROR and add an option to override and ignore it, for
when you're really up to your neck in it)

pg_backup -opts [BACKUP COMMAND]
opts:
-D    data directory (defaults to PGDATA)

usage examples:
pg_backup tar zcvhf /dev/rmt0 $PGDATA
uses PGDATA to identify data directory, then creates a tape archive on
the default tape device

pg_backup write_to_BAR_system p1 p2 p3

Not hugely happy with the above. I'm sure someone will come up with a
few streamlining comments, eh?

I'd certainly prefer a solution that involved writing WAL records to
indicate start and end, which seems cleaner and more integrated.
However, we need to be able to cater for cold/offline backups.

Best Regards, Simon Riggs