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