Re: "Resurrected" data files - problem?

On Fri, 2007-11-09 at 09:24 +0100, Albe Laurenz 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
> >>>>> (other than the obvious one that there may be unnecessary files
> >>>>> about that consume disk space)?
> >>>>
> >>>> This will not work at all.
> >>>
> >>> To be more specific: the resurrected files aren't the problem;
> >>> offhand I see no reason they'd create any issue beyond wasted
> >>> disk space. The problem is version skew between files that were
> >>> backed up at slightly different times, leading to inconsistency.
> >>
> >> I should have mentioned that before the (incremental) backup
> >> there would be a pg_start_backup() and a pg_stop_backup()
> >> afterwards, and we would use PITR.
> >>
> >> So there could only be three kinds of files:
> >> - Files that did not change since the full backup, restored
> >> from there. They should therefore look exactly as if the
> >> online backup were performed in the normal way.
> >> - Files that have changed or are new, restored from the
> >> incremental backup. These will also be ok, because
> >> they were backed up between pg_start_backup() and
> >> pg_stop_backup().
> >> - Files that have been deleted between full and incremental
> >> backup and have been resurrected.
> >>
> >> This third group is the only one which might be problematic,
> >> as far as I can see, because PostgreSQL will no expect them to
> >> be there.
> >>
> >> The version skew between files backed up at slightly different
> >> times should be taken care of by PITR, shouldn't it?
> >
> > 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 am afraid that there is still a misunderstanding. The
> procedure would be as follows:
>
> The backup:
>
time1
> - pg_start_backup()
> - full backup of the PostgreSQL files
> - pg_stop_backup()
time2
>
> Next day:
>
time3
> - pg_start_backup()
> - backup of the files that have changed since the last backup
> - pg_stop_backup()
time4

> The recovery:
>
> - restore files from the full backup
> - restore files from the incremental backup
> - create recovery.conf and start the server

Yes, I understood that.

> With a normal online backup, the backup also does not take
> place at a single time.

;-)

> Why is that no problem there, but is
> a problem here?

I'm not sure where "there" and "here" are, but it seems you have
misunderstood.

There *is* a problem with normal online backup, as many people have now
explained, though I have shown you a way to do this. Please reread.

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