Re: Incremental backups, and backup history

Regarding backup history:

I have an application designed for novices. Apparently it's easy to hit the
"Delete" button, and then say yes to the "Are you sure you want to delete
this?" question even when they don't want to. Therefore I simply mark a
record as deleted. For example,
UPDATE table SET deleted='t' WHERE something=true;

Then my application logic pretends it doesn't really exist until two days
later the user decides they want it back.

It works very well for me.

--
Matthew Nuzum
www.bearfruit.org
cobalt(at)bearfruit(dot)org

> -----Original Message-----
> From: Bruno Wolff III [mailto:bruno(at)wolff(dot)to]
> Sent: Thursday, June 19, 2003 6:59 AM
> To: Antonios Christofides
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: 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.