Re: [GENERAL] "Resurrected" data files - problem?

From: Albe Laurenz <laurenz.albe(at)wien.gv.at>
Date: Thu Nov 08 2007 - 11:11:45 EST

Tom Lane 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?

Yours,
Laurenz Albe

---------------------------(end of broadcast)---------------------------

TIP 5: don't forget to increase your free space map settings Received on Thu Nov 8 12:07:48 2007