Re: Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL

On Fri, 2010-02-12 at 14:38 +0900, Fujii Masao wrote:
> On Thu, Feb 11, 2010 at 11:22 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > Simon Riggs wrote:
> >> Might it not be simpler to add a parameter onto pg_standby?
> >> We send %s to tell pg_standby the standby_mode of the server which is
> >> calling it so it can decide how to act in each case.
> >
> > That would work too, but it doesn't seem any simpler to me. On the contrary.
>
> Agreed.
>
> There could be three kinds of SR configurations. Let's think of them separately.
>
> (1) SR without restore_command
> (2) SR with 'cp'
> (3) SR with pg_standby

Thanks for the explanation.

> (1) is the straightforward configuration. In this case the standby replays only
> the WAL files in pg_xlog directory, and starts SR when it has found the invalid
> record or been able to find no more WAL file. Then if SR is terminated for some
> reasons, the standby would periodically try to connect to the primary and start
> SR again. If you choose this, you don't need to care about the problem discussed
> on the thread.
>
> In the (2) case the standby replays the WAL files in not only pg_xlog but also
> the archive, and starts SR when it has found the invalid record or been able to
> find no more WAL file. If the archive is shared between the primary and the
> standby, the standby might restore the partial WAL file being archived (copied)
> by the primary. This could happen because 'cp' is not an atomic operation.
>
> Currently when the standby finds the WAL file whose file size is less than 16MB,
> it emits the FATAL error. This is the problem that I presented upthread. That is
> undesirable behavior, so I proposed to just treat that case the same as if no
> more WAL file is found. If so, the standby can start SR instead of emitting the
> FATAL error. (2) is useful configuration as described in Heikki's
> commig message.
> http://archives.postgresql.org/pgsql-committers/2010-01/msg00395.php

> (3) was unexpected configuration (at least for me). This would work fine as a
> *file-based* log shipping but not SR. Since pg_standby doesn't return when no
> more WAL file is found in the archive (i.e., it waits until the next complete
> WAL file is available), SR will never start. OTOH, since pg_standby treats the
> partial file as "nonexistence", the problem discussed on this thread doesn't
> happen.

When we refer to "pg_standby" we mean any script. pg_standby is just a
reference implementation of a useful script. The discussion shouldn't
really focus on pg_standby, nor should we think of it as a different
approach. My original question was whether we are seeking to remove
pg_standby and, if so, have we implemented all of the technical features
that pg_standby provides? Worryingly the answer seems to be Yes and No.
I don't care if we get rid of pg_standby as long as we retain all the
things it does. *Losing* features is not acceptable.

> Questions:
> (A) Is my proposal for (2) reasonable? For me, Yes.
> (B) Should we allow (3) to work as "streaming replication"? In fact, we should
> create the new mode that makes pg_standby return as soon as it doesn't find
> a complete WAL file in the archive? I agree with Heikki, i.e., don't think
> it's worth doing. Though pg_standby already has the capability to remove the
> old WAL files, we would still need the cron job that removes them
> periodically
> because pg_standby is not executed during SR is running normally.

Yes, I realised that myself overnight and was going to raise this point
with you today.

In 8.4 it is pg_standby that was responsible for clearing down the
archive, which is why I suggested using pg_standby for that again. I
agree that will not work. The important thing is not pg_standby but that
we have a valid mechanism for clearing down the archive.

If (2) is a fully supported replication method then we cannot rely on
the existence of an external cron job to clear down the archive. Most
importantly, that cron job would not know the point up to which to clear
down the archive, the information given to pg_standby by %r.

So I suggest that you have a new action that gets called after every
checkpoint to clear down the archive. It will remove all files from the
archive prior to %r. We can implement that as a sequence of unlink()s
from within the server, or we can just call a script to do it. I prefer
the latter approach. However we do it, we need something initiated by
the server to maintain the archive and stop it from overflowing.

--
Simon Riggs www.2ndQuadrant.com