Re: Issues Outstanding for Point In Time Recovery (PITR)

On Mon, 2002-07-08 at 21:53, Barry Lind wrote:
> I know that in Oracle there are 'alter database begin backup' and 'alter
> database end backup' commands that allow you to script your hot backups
> through a cron job by calling the begin backup command first, then using
> disk backup method of choice and then finally call the end backup command.

This gave me an idea of a not-too-difficult-to-implement way of doing
consistent online backups (thanks to MVCC it is probably much easier
than Oracle's):

Backup:

1) record the lowest uncommitted transaction number (LUTN) , this may
have problems with wraparound, but I guess they are solvable. Disllow
VACUUM. Do a CHECKPOINT ('alter database begin backup')

3) make a file-level (.tar) backup of data directory.

4) Allow VACUUM. ('alter database end backup')

Restore:

1) restore the data directory from file-level backup

2) mark all transactions committed after LUTN as aborted, effectively
deleting all tuples inserted and resurrecting those deleted/updated
after start of backups.

3) make sure that new transaction number is large enough.

PS. It would be nice if our OID-based filenames had some type indicator
in their names - it is usually waste of time and space to backup indexes
and temp tables. The names could be of form
pg_class.relkind:pg_class.relfilenode instead of just
pg_class.relfilenode they are now.

-------------------
Hannu