Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group