Re: Attempt to re-archive existing WAL logsafterrestoringfrom backup

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.

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.

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