Re: Incremental backups, and backup history

On Thu, Jun 19, 2003 at 11:42:28 +0300,
Antonios Christofides <A(dot)Christofides(at)itia(dot)ntua(dot)gr> wrote:
>
> My second question is a general relational database backup question, not
> specifically related to pgsql. Sometimes a user accidentally
> deletes/corrupts a file, and discovers it three days later. After they
> come panicing to me, I can give them their file as it was three days
> ago, because of the backup tape rotation. Now suppose a user deletes
> ten employees from the database, and three days later they understand
> that this was a bad thing. Now what? I can restore the entire database
> and make it as it was three days ago, but I can't restore the particular
> deleted records in the current database, as the relations make the
> database behave as a single unit.

This isn't a good situation to be in. If you log all of the transactions,
one option may be to restore the database as of the last time it was
valid, remove the bogus transactions from the transaction log and then replay
the transactions from the time of the backup to the present.
This will mean downtime for the database and it is possible that removing
the bogus tranasctions will affect things as you reapply other transactions
in ways you don't want.

Another option is to figure out what the bogus tranasctions did (which keeping
a history will help out with) and try to undo them as best as possible with
new transactions.

Either of the above are going to need manual oversight.