Обсуждение: [HACKERS] [PATCH] Add recovery_min_apply_delay_reconnect recovery option

This is a patch I am using in production using the following parameters
in recovery.conf:

    recovery_min_apply_delay = '1d'
    recovery_min_apply_delay_reconnect = '10 min'

In our environment we expect that standby servers with an apply delay
provide some protection against mistakes by the DBA (myself), and that
they contain a valid copy of the data that can be used in the event that
the master dies.

Does this feature seems applicable to a wider community?


== delay-reconnect-param ==

Add recovery_min_apply_delay_reconnect recovery option

'recovery_min_apply_delay_reconnect' allows an administrator to specify
how a standby using 'recovery_min_apply_delay' responds when streaming
replication is interrupted.

Combining these two parameters provides a fixed delay under normal
operation while maintaining some assurance that the standby contains an
up-to-date copy of the WAL.

This administrative compromise is necessary because the WalReceiver is
not resumed after a network interruption until all records are read,
verified, and applied from the archive on disk.

Is it possible to verify the archive on disk independently of
application?  Adding a second delay parameter provides a workaround for
some use cases without complecting xlog.c.

 doc/src/sgml/recovery-config.sgml       | 24 ++++++++++++++++++++++++
 src/backend/access/transam/xlog.c       | 59 ++++++++++++++++++++++++++++++++++++++++++++++-------------
 src/test/recovery/t/005_replay_delay.pl |  8 ++++++--
 3 files changed, 76 insertions(+), 15 deletions(-)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman <ericshane@eradman.com> wrote:
> This administrative compromise is necessary because the WalReceiver is
> not resumed after a network interruption until all records are read,
> verified, and applied from the archive on disk.

Taking a step back here... recoveryApplyDelay() uses
XLogCtl->recoveryWakeupLatch which gets set if the WAL receiver has
received new WAL, or if the WAL receiver shuts down properly. So if
the WAL receiver gets down for whatever reason during the loop of
recoveryApplyDelay(), the startup process waits for a record to be
applied maybe for a long time, and as there is no WAL receiver we
actually don't receive any new WAL records. New WAL records would be
received only once WaitForWALToBecomeAvailable() is called, which
happens once the apply delay is done for. If the postmaster dies, then
HandleStartupProcInterrupts() would take care of taking down
immediately the startup process, which is cool.

I see what you are trying to achieve and that seems worth it. It is
indeed a waste to not have a WAL receiver online while waiting for a
delay to be applied. If there is a flacky network between the primary
and a standby, you may end up with a standby way behind its primary,
and that could penalize a primary clean shutdown as the primary waits
for the shutdown checkpoint record to be flushed on the standby.

I think that your way to deal with the problem is messy though. If you
think about it, no parameters are actually needed. What you should try
to achieve is to make recoveryApplyDelay() smarter, by making the wait
to forcibly stop if you detect a failure by getting out of the redo
routine, and then force again the record to be read again. This way,
the startup process would try to start again a new WAL receiver if it
thinks that the source it should read WAL from is a stream. That may
turn to be a patch more complicated than you think though.

Your patch also breaks actually the use case of standbys doing
recovery using only archives and no streaming. In this case
WalRcvStreaming returns false, and recovery_min_apply_delay_reconnect
would be used unconditionally, so you would break a lot of
applications silently.

> Is it possible to verify the archive on disk independently of
> application?  Adding a second delay parameter provides a workaround for
> some use cases without complecting xlog.c.

That's possible, not with core though. The archives are in a location
not controlled by the backend, but by archive_command, which may not
be local to the instance where Postgres is running. You could always
hack your own functions to do this work, here is an example of
something I came up with:
https://github.com/michaelpq/pg_plugins/tree/master/wal_utils
This prototype (use and hack at your own risk), is able to look at the
local contents of an archive. This can be used easily with a
client-side tool to copy a series of segments., or just perform sanity
checks on them.

For those reasons, -1 for the patch as proposed.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote:
> On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman <ericshane@eradman.com> wrote:
> > This administrative compromise is necessary because the WalReceiver is
> > not resumed after a network interruption until all records are read,
> > verified, and applied from the archive on disk.
> 
> Taking a step back here... recoveryApplyDelay() uses
> XLogCtl->recoveryWakeupLatch which gets set if the WAL receiver has
> received new WAL, or if the WAL receiver shuts down properly.

I thought I had observed cases where the WalReceiver was shut down
without causing XLogCtl->recoveryWakeupLatch to return. If I'm wrong
about this then there's no reason to spin every n seconds.

> the WAL receiver gets down for whatever reason during the loop of
> recoveryApplyDelay(), the startup process waits for a record to be
> applied maybe for a long time, and as there is no WAL receiver we
> actually don't receive any new WAL records.
...
> indeed a waste to not have a WAL receiver online while waiting for a
> delay to be applied.

Exactly!

> If there is a flacky network between the primary and a standby, you
> may end up with a standby way behind its primary, and that could
> penalize a primary clean shutdown as the primary waits for the
> shutdown checkpoint record to be flushed on the standby.

This is another artifact that the database administrator would not
anticipate.

> I think that your way to deal with the problem is messy though. If you
> think about it, no parameters are actually needed. What you should try
> to achieve is to make recoveryApplyDelay() smarter, by making the wait
> to forcibly stop if you detect a failure by getting out of the redo
> routine, and then force again the record to be read again. This way,
> the startup process would try to start again a new WAL receiver if it
> thinks that the source it should read WAL from is a stream. That may
> turn to be a patch more complicated than you think though.

One of my earlier attempts was to break from the redo loop and try
reading the next record. This was too simple because it only starts the
WAL receiver if there is nothing more to be read from the archive. 

Which record are you suggesting should be forcibly "read again"?  The
record identified by XLogCtl->replayEndRecPtr or
XLogCtl->lastReplayedEndRecPtr?  I'll look more carefully at such an
approach.

> Your patch also breaks actually the use case of standbys doing
> recovery using only archives and no streaming. In this case
> WalRcvStreaming returns false, and recovery_min_apply_delay_reconnect
> would be used unconditionally, so you would break a lot of
> applications silently.

Excellent point--I had not thought of how this would interact with a
standby that used only archives.

All useful feedback, thank you for the thorough review! 
--
Eric Radman  |  http://eradman.com



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 17, 2017 at 10:40 PM, Eric Radman <ericshane@eradman.com> wrote:
> On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote:
> I thought I had observed cases where the WalReceiver was shut down
> without causing XLogCtl->recoveryWakeupLatch to return. If I'm wrong
> about this then there's no reason to spin every n seconds.

I would expect a patch to not move the timeout calculation within the
loop in recoveryApplyDelay() as you did.

> Which record are you suggesting should be forcibly "read again"?  The
> record identified by XLogCtl->replayEndRecPtr or
> XLogCtl->lastReplayedEndRecPtr?  I'll look more carefully at such an
> approach.

I have not looked at how to do that in details, but as the delay is
applied for commit WAL records, you would need to make the redo loop
look again at this same record once you have switched back to a
streaming state. Something to be careful about is that you should not
apply the same delay multiple times for the same record.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote:
> On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman <ericshane@eradman.com> wrote:
> > This administrative compromise is necessary because the WalReceiver is
> > not resumed after a network interruption until all records are read,
> > verified, and applied from the archive on disk.
> 
> I see what you are trying to achieve and that seems worth it. It is
> indeed a waste to not have a WAL receiver online while waiting for a
> delay to be applied.
... 
> If you think about it, no parameters are actually needed. What you
> should try to achieve is to make recoveryApplyDelay() smarter,

This would be even better. Attached is the 2nd version of this patch
that I'm using until an alternate solution is developed.

> Your patch also breaks actually the use case of standbys doing
> recovery using only archives and no streaming

This version disarms recovery_min_apply_delay_reconnect if a primary is
not defined. Also rely on XLogCtl->recoveryWakeupLatch to return if the
WalReciver is shut down--this does work reliably.

-- 
Eric Radman  |  http://eradman.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Oct 20, 2017 at 3:46 AM, Eric Radman <ericshane@eradman.com> wrote:
> On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote:
>> On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman <ericshane@eradman.com> wrote:
>> > This administrative compromise is necessary because the WalReceiver is
>> > not resumed after a network interruption until all records are read,
>> > verified, and applied from the archive on disk.
>>
>> I see what you are trying to achieve and that seems worth it. It is
>> indeed a waste to not have a WAL receiver online while waiting for a
>> delay to be applied.
> ...
>> If you think about it, no parameters are actually needed. What you
>> should try to achieve is to make recoveryApplyDelay() smarter,
>
> This would be even better. Attached is the 2nd version of this patch
> that I'm using until an alternate solution is developed.

I definitely agree that a better handling of WAL receiver restart
would be done, however this needs and a better-thought refactoring
which is not this patch provides, so I am marking it as returned with
feedback. People looking for a solution, and not using archiving
(because your patch breaks it), could always apply what you have as a
workaround.
-- 
Michael