Re: [HACKERS] Point in Time Recovery - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: [HACKERS] Point in Time Recovery
Date
Msg-id 200407192136.i6JLacL06105@candle.pha.pa.us
Whole thread Raw
In response to Re: [HACKERS] Point in Time Recovery  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > we need to check to see which one has a WAL eof-of-segment marker (we
> > have on of those, right?).
>
> No, we don't.
>
> > I think I see a solution. We are going to create a file during backup so
> > we know the wal offsets and xids.  If we see that file, we know either
> > we have a restore of a backup or they currently running a backup.
>
> ... or the last backup attempt failed, but they forgot to remove the
> file it left.  Or we are doing crash recovery after the system lost
> power while a backup was running.  Or half a dozen other obvious scenarios.
>
> > If we tell them not to restore while a backup is running (seems pretty
> > obvious) we can then delete pg_xlog when the backup wal offset file
> > exists.  In other cases, we know the WAL files are valid to use.
>
> We're not deleting pg_xlog, period.  IMHO it's too dangerous even to
> have such a function in the code.
>
> My original suggestion was to *replace* individual xlog files with data
> extracted from archive, and only after determining that the archive
> indeed has a copy of that particular file (and we can fetch it).
> This at least has a fighting chance of not losing information.  Wiping
> pg_xlog in toto on the basis of a guess about the system status is just
> a form of russian roulette.  Sooner or later you will wipe some xlog
> files that you can't get back from archive.

OK, if you don't want to place restrictions on recovery, fine, but how
do you handle the situation where you backup but the WAL file has holes
in the tar backup but you don't have an archive file to use because it
didn't make it to the archive before the drive died?  Can we detect
holes in the WAL file recovered from backup?  We might, but I am afraid
we might not.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgsql-patches by date:

Previous
From: Tom Lane
Date:
Subject: Re: [HACKERS] Point in Time Recovery
Next
From: Bruce Momjian
Date:
Subject: Re: add missing options to pg_dumpall