Re: "Resurrected" data files - problem?

On Nov 8, 2007 9:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Peter Childs" <peterachilds@gmail.com> writes:
> > On 08/11/2007, Albe Laurenz <laurenz.albe@wien.gv.at> 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.
>
> You could make this work if you shut down Postgres whenever you
> are taking a backup, but as a means for backing up a live database
> it indeed won't work at all.

I think if you had real snapshotting file systems you could use the
snapshots to create your backups.  But this seems like a lot of work
to avoid implementing PITR to me.