Re: "Resurrected" data files - problem?

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