Re: question about warm standby databases in 8.2.5

On Mon, 10 Dec 2007, Brett Neumeier wrote:

> It seems that the recovery command always copies the source WAL file (with a
> name like 00000001000000020000009C) to a file path "pg_xlog/RECOVERYXLOG",
> which is fine. However, then when we abort recovery, postgresql seems to
> expect that the most recent WAL log should be in pg_xlog with its original
> filename, e.g., the 0....9C filename from above.

I think your expectation for what the recovery command script you provide
does and what the server actually requires are a little mismatched.
RECOVERYXLOG is strictly a temporary file and as you've discovered the
server may want the original back again by its original name. This has
come up before--check out this thread, from this message to the end:

http://archives.postgresql.org/pgsql-admin/2007-08/msg00425.php

It's pointed out there that the documentation is little sparse in this
area.

Not that many people run into this because there is a reference
implementation of a recovery command that correctly implements the
required behavior:

http://developer.postgresql.org/pgdocs/postgres/pgstandby.html

pg_standby ships with the upcoming 8.3, but if you grab that code you can
run it just fine against an 8.2 system. You'd be better off in the long
run (and probably the short run too) replacing whatever script you've
developed with that one, so you can just get updates to it rather than
keeping one updated in-house. Consider the time you've spend working on
your own not wasted but educational--you can never know too much about
disaster recovery of your database.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD