Re: [ADMIN] Attempt to re-archive existing WAL logsafterrestoringfrom backup

On Thu, 2007-06-07 at 22:20 +0100, Simon Riggs wrote:
> On Sat, 2007-06-02 at 01:07 +0100, Jon Colverson wrote:
> > Simon Riggs wrote:
> > > I've spent some time re-examining the code and I can't see a route for
> > > the discussed problem to occur. All the main code paths do not exhibit
> > > this problem in 8.2, so I'm not sure where to go from here.
> > >
> > > If you can help further, please give me a shout.
> >
> > Thanks a lot for looking into this.
> >
> > I've attached a self-contained minimal shell script which sets up a
> > cluster and demonstrates the problem. I hope that will provide an
> > unambiguous description. I'm running it on Ubuntu 7.04 (i386) and the
> > script includes all the commands to build PostgreSQL from scratch, but
> > obviously you can snip that stuff out and change the paths to suit your
> > environment.
> >
> > The script ends with the server log showing the archiver attempting to
> > re-archive the last log that was restored in the recovery procedure.
>
> Jon,
>
> Your script is correct and there is a problem, as you demonstrate. Thank
> you for bringing this to the list.
>
> You have followed the manual in full. In Step 6 of the recovery
> procedure outlined here
> http://developer.postgresql.org/pgdocs/postgres/continuous-archiving.html#BACKUP-PITR-RECOVERY
> we say that if you have partially completed WAL logs these should be
> copied back into pg_xlog. This is correct and if we do this we avoid the
> error you have highlighted. In the case of a true disaster recovery, as
> you have attempted to test, no files are available and so an attempt is
> made to archive the last restored file. That attempt fails iff you have
> done as the manual suggests and used an archive_command that prevents
> duplicate file insertions.
>
> I'll repost to pgsql-hackers to discuss solutions. Fixes are simple, but
> require some discussion.

Currently if we perform an archive recovery and the end of logs occur
during or at the end of an xlog that has already been archived then we
will fail and keep failing to archive anything. Thats not much fun and
it appears to me that this problem goes back to early days of PITR and
may require backpatching.

Currently if an xlog file has a missing archive_status file then we
create a .ready status, which leads to the command being retried, which
might fail or might not. Since the last file on an archive recovery
seldom has an archive status this leads to failure situations when the
archive_command prevents duplicate files from existing in the archive.

AFAICS we have four options:

1. We can assume that if an xlog file has no archive status it must have
been archived. Hmmm, sounds like a poor assumption.

2. We can relax the recommendation to have an archive_command that
prevents duplicate logs. Although I haven't in recent times seen this as
too strong a requirement because we have timelines, I don't think we
should relax this.

3. We craft a special archive_status file for the last xlog file in an
archive, so that it never gets archived.

4. As Warren Little recently regretted, if we have a corrupt xlog file
then end of logs happens much earlier than anticipated. When this occurs
many subsequent xlogs would be named similarly to files already in the
archive, yet they would be a separate timeline. We could solve this
problem as well by simply bumping the TLI each time we complete a
recovery, whatever the reason. Presumably the archives will not stretch
back as far as 2 billion recoveries.

Option 3 is the straightforward and fixes the issue directly; option 4
solves many problems in a bold sweep of the design pen, with no obvious
downside, AFAICS. Both are simple and can be backpatched easily.

> Initially I thought you'd fallen foul of another problem which is known
> to exist, which is caused by immediately shutting down a server after
> having taken a hot backup. This isn't a problem in real usage though is
> a problem in a scripted test. I've already proposed solutions there and
> will pick up that thread again also.

On another thread on -hackers.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com