Re: Postgres Hot Standby. How or when does the recovery db move recovery.conf to recovery.done?

On 3/21/07, Dhaval Shah <dhaval(dot)shah(dot)m(at)gmail(dot)com> wrote:
> From one of Tom's reply to a different poster, I found that one can run
>
> pg_resetxlog. http://www.postgresql.org/docs/8.2/static/app-pgresetxlog.html,
> to make the db recover and startup.
>
> Appears not for the faint hearted!
>
> Dhaval
>
> On 3/21/07, Dhaval Shah <dhaval(dot)shah(dot)m(at)gmail(dot)com> wrote:
> > I looked at the pg_standby utility and would have liked to use it,
> > however there are some customer driven extraneous issues in using
> > that.
> >
> > What I am looking at it is this:
> >
> > 1. I can detect that the primary has gone down and return a non-zero
> > for the standby to recover.
> >
> > 2. Since I can detect that I am out of standby mode, I can shutdown
> > the postgres, move the recovery.conf file to recovery.done manually.
> > And then restart the db.
> >
> > Even if I do step 2, I still get the following in the server log:
> >
> > =====
> > Main: Triggering Recovery!!! <- my script is returning a non-zero code here ...
> >
> > PANIC: could not open file "pg_xlog/00000001000000000000001B" (log
> > file 0, segment 27): No such file or directory

If you are getting these errors there is something wrong with your log
shipping method. You are missing WAL files that are needed to bring
the server back into recovery...pg_resetxlog will not help you
re-recover the server although it may allow you to bring the server up
with some (possibly a lot) of data loss.

This is coming from the fact that for a 'hot standby', you need to
take extra precautions to preserve old WAL files. AIUI, the server
needs to go far enough back in 'WAL time' to see the last checkpoint,
which is not available. Even if you can't use it, get a copy of the
pg_standby utility and get a really good understanding of how it
works. It has a clever 'symlink' mode which neatly bypasses the
complexity of maintaining a standby system. It is one C file and is
well documented.

merlin