Incremental Backup Script

From: "Gregor Zeitlinger" <gregor(dot)zeitlinger(at)torexretail(dot)de>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Incremental Backup Script
Date: 2005-12-25 13:02:13
Message-ID: 5DE489C997EC984FA3DD0935879DAE1255EB1B@ex09-00-z002.torexretail.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

as far as I have understood, the WAL backup that you control via "archive_command" is the PostgreSQL equivalent to what other databases 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 data up 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> are available. 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 in any circumstance?

Is this a sensible idea?

Regards,

Gregor

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Martijn van Oosterhout 2005-12-25 13:10:07 Re: Fixing row comparison semantics
Previous Message Andy Astor 2005-12-25 04:29:08 Happy Holidays