Hello,
as far as I have understood, the WAL backup that you control via "archive_command" is the PostgreSQL equivalent to what
otherdatabases let you do with an incremental backup. That is, if you don't forget to include the current WAL block.
I have found a script to determine the current WAL on the admin mailing list.
Based on this script, I intend to write two scripts that do the following (unless something like this already exists).
basebackup -> basebackup.<bbd>.bz2
incrementalbackup -> incrementalbackup.<bbd>.<ibc>.<ibd>.bz2 (incremental backup relative to the last incremental
backup)
restore (a file produced by the above commands) -> restore database (either base, or base + 1..n incremental backups)
<bbd>: base backup date (e.g. 2005-12-25-14-00)
<ibc>: incremental backup counter (1..n)
<ibd>: incremental backup date
The central idea is that base backups are guaranteed to include all information up to <bbd> and incremental backups all
dataup to <ibd>. I hope that this makes it easier for administrators.
archive_command:
copy the files to a local backup directory (LWB = local wal backup)
basebackup:
1) tar the data directory
2) add any WALs that are produced while the backup is running.
3) delete all WAL that are included in the tar
4) I still wonder how <bbd> must be chosen (that of pg_start_backup?)
incremental backup:
1) add all WAL that are currently in the LWB to the tar
2) add the current WAL to the tar
3) verify that all WALs prior to the current WAL are included (i.e. that no WAL is currently being copied to the LWB)
4) delete all WAL that are included in the tar
restore:
1) if it's a base backup, just restore that
2) if it's an incremental backup, check that the corresponding base backup and all incremental backups with lower <ibc>
areavailable. Then restore the base backup and all incremental backups up to the one specified
Also, I was wondering whether it is always safe to copy the current WAL file, i.e. may the current WAL file be invalid
inany circumstance?
Is this a sensible idea?
Regards,
Gregor