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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
