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

From Bruce Momjian
Subject Re: [HACKERS] Point in Time Recovery
Date
Msg-id 200407281625.i6SGPak28629@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-admin
Here is another open PITR issue that I think will have to be addressed
in 7.6.  If you do a critical transaction, but do nothing else for eight
hours, that critical transaction hasn't been archived yet.  It is still
sitting in pg_xlog until the WAL file fills.

I think we will need to document this behavior and address it in some
way in 7.6.  We can't assume that we can send multiple copies of pg_xlog
to the archive (partial and full ones) because we might be going to a
tape drive.  However, this is a non-intuitive behavior of our archiver.
We might need to tell people to archive the most recent WAL file every
minute to some other location or something.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think we should push the partially complete WAL file to the archive
> > location before shutdown. ...
> > When you are running and finally fill up the WAL file it would then
> > overwrite the one in the archive but I think that is OK.
>
> I don't think this can fly at all.  Here are some off-the-top-of-the-head
> objections:
>
> 1. We don't have the luxury of spending indefinite amounts of time to
> do a database shutdown.  Commonly we are under a twenty-second sentence
> of death from init.  I don't want to spend the 20 seconds waiting to see
> if the archiver will manage to push 16MB onto a slow tape drive.  Also,
> if the archiver does fail to push the data in time, it'll likely leave a
> broken (partial) xlog file in the archive, which would be really bad
> news if the user then relies on that.
>
> 2. What if the archiver process entirely fails to push the file?  (Maybe
> there's not enough disk space, for instance.)  In normal operation we'll
> just retry every so often.  We definitely can't do that during shutdown.
>
> 3. You're blithely assuming that the archival process can easily provide
> overwrite semantics for multiple pushes of the same xlog filename.  Stop
> thinking about "cp to some directory" and start thinking "dump to tape"
> or "burn onto CD" or something like that.  We'll be raising the ante
> considerably if we require the archive_command to deal with this.
>
> I think the last one is really the most significant issue.  We have to
> keep the archiver API as simple as possible.
>
>             regards, tom lane
>

--
  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-admin by date:

Previous
From: marco antonio pacheco
Date:
Subject: Re: Como usar MDB Tools
Next
From: Randall Perry
Date:
Subject: Re: How do I grant access to entire database at