Re: "Resurrected" data files - problem?

On Fri, 2007-11-09 at 10:28 +0100, Albe Laurenz wrote:
> Simon Riggs wrote:
> >>>>>>> So if we perform our database backups with incremental
> >>>>>>> backups as described above, we could end up with additional
> >>>>>>> files after the restore, because PostgreSQL files can get
> >>>>>>> deleted (e.g. during DROP TABLE or TRUNCATE TABLE).
> >>>>>>>
> >>>>>>> Could such "resurrected" files (data files, files in
> >>>>>>> pg_xlog, pg_clog or elsewhere) cause a problem for
> >>>>>>> the database?
> >>>
> >>> The backup is not instantaneous, so there is no single time
> >>> at which the hot backup takes place. So deciding whether
> >>> a file has changed based upon a comparison of two file timestamps
> >>> cannot work. You would need to take timestamps for the file both
> >>> before the pg_start_backup() and after the pg_stop_backup()
> >>> of the file for both full and incremental backups.
> >>> If all four timestamps are equivalent, then you are safe.
>
> I think that understanding is finally dawning here.
>
> The problem you see is that the backup software might decide
> that the file has not been changed, skip it and go on backing
> up other files, but the file can still be modified before
> pg_stop_backup(), correct?

Correct.

I doubt whether the backup software performs these checks, but if you
have time please bug your vendor for details. Note that the check must
be performed *after* the backup, to see whether the backup itself is
valid, which is a little strange. That only makes sense when using
frozen snapshot techniques.

Any information about specific vendors interface is always welcome.
Speaking to them directly hasn't been very effective, but when there is
a paying customer they are much more responsive.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com