Re: Incremental backups, and backup history

On Thu, 2003-06-19 at 03:42, Antonios Christofides wrote:
> Hi,
>
[snip]
> 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.

One possibility that negates the need for "garbage collection" is
to create a history table that mirrors the primary table, and
also has an "activity_code char(1)", with values either "U" or "D".

Then an "before update" or "before delete" trigger would automatically
insert the pre-update-or-delete record into the history table, along
with U or D in activity_code.

Thus, you can look through the history table and find the accidentally
deleted records.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA http://members.cox.net/ron.l.johnson |
| |
| "Oh, great altar of passive entertainment, bestow upon me |
| thy discordant images at such speed as to render linear |
| thought impossible" (Calvin, regarding TV) |
+-----------------------------------------------------------