Re: [HACKERS] [PATCH] Add recovery_min_apply_delay_reconnect recovery option
On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote:
> On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman <ericsh...@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
commit 36b5a022241c1ade9dcf5ffc46f926e46f4ee696
Author: Eric Radman <ericsh...@eradman.com>
Date: Tue Oct 17 19:10:22 2017 -0400
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.
It would be better if a second option was not added, but second delay
parameter provides a workaround for some use cases without complecting
xlog.c.
diff --git a/doc/src/sgml/recovery-config.sgml
b/doc/src/sgml/recovery-config.sgml
index 4e1aa74c1f..8e395edae0 100644
--- a/doc/src/sgml/recovery-config.sgml
+++ b/doc/src/sgml/recovery-config.sgml
@@ -502,6 +502,30 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'
# Windows
+
+ recovery_min_apply_delay_reconnect
(integer)
+
+recovery_min_apply_delay_reconnect recovery
parameter
+
+
+
+
+If the streaming replication is inturruped while
+recovery_min_apply_delay is set, WAL records will be
+replayed from the archive. After all records have been processed from
+local disk, PostgreSQL will attempt to resume streaming
+and connect to the master.
+
+
+This parameter is used to compromise the fixed apply delay in order to
+restablish streaming. In this way a standby server can be run in fair
+conditions with a long delay (hours or days) without while specifying
+the maximum delay that can be expected before the WAL archive is
brought
+back up to date with the master after a network failure.
+
+
+
+
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index dd028a12a4..6f4c7bf3e8 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -267,6 +267,7 @@ static TimestampTz recoveryTargetTime;
static char *recoveryTargetName;
static XLogRecPtr recoveryTargetLSN;
static int recovery_min_apply_delay = 0;
+static int recovery_min_apply_delay_reconnect = 0;
static TimestampTz recoveryDelayUntilTime;
/* options taken from recovery.conf for XLOG streaming */
@@ -5227,6 +5228,7 @@ readRecoveryCommandFile(void)
*head = NULL,
*tail = NULL;
boolrecoveryTargetActionSet = false;
+ const char *hintmsg;
fd = AllocateFile(RECOVERY_COMMAND_FILE, "r");
@@ -5452,8 +5454,6 @@ readRecoveryCommandFile(void)
}
else if (strcmp(item->name, "recovery_min_apply_delay") == 0)
{
- const char *hintmsg;
-
if (!parse_int(item->value, _min_apply_delay,
GUC_UNIT_MS,
))
ereport(ERROR,
@@ -5463,6 +5463,25 @@ readRecoveryCommandFile(void)
hintmsg ? errhint("%s",
_(hintmsg)) : 0));
ereport(DEBUG2,
(errmsg_internal("recovery_min_apply_delay = '%s'", item->value)));
+ recovery_min_apply_delay_reconnect =
re
Re: [HACKERS] [PATCH] Add recovery_min_apply_delay_reconnect recovery option
On Tue, Oct 17, 2017 at 12:34:17PM +0900, Michael Paquier wrote: > On Tue, Oct 17, 2017 at 12:51 AM, Eric Radman <ericsh...@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
[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(-)
commit b8807b43c6a44c0d85a6a86c13b48b47f56ea45f
Author: Eric Radman <ericsh...@eradman.com>
Date: Mon Oct 16 10:07:55 2017 -0400
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.
diff --git a/doc/src/sgml/recovery-config.sgml
b/doc/src/sgml/recovery-config.sgml
index 0a5d086248..4f8823ee50 100644
--- a/doc/src/sgml/recovery-config.sgml
+++ b/doc/src/sgml/recovery-config.sgml
@@ -502,6 +502,30 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"'
# Windows
+
+ recovery_min_apply_delay_reconnect
(integer)
+
+recovery_min_apply_delay_reconnect recovery
parameter
+
+
+
+
+If the streaming replication is inturruped while
+recovery_min_apply_delay is set, WAL records will be
+replayed from the archive. After all records have been processed from
+local disk, PostgreSQL will attempt to resume streaming
+and connect to the master.
+
+
+This parameter is used to compromise the fixed apply delay in order to
+restablish streaming. In this way a standby server can be run in fair
+conditions with a long delay (hours or days) without while specifying
+the maximum delay that can be expected before the WAL archive is
brought
+back up to date with the master after a network failure.
+
+
+
+
diff --git a/src/backend/access/transam/xlog.c
b/src/backend/access/transam/xlog.c
index dd028a12a4..36a4779f70 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -267,6 +267,7 @@ static TimestampTz recoveryTargetTime;
static char *recoveryTargetName;
static XLogRecPtr recoveryTargetLSN;
static int recovery_min_apply_delay = 0;
+static int recovery_min_apply_delay_reconnect = 0;
static TimestampTz recoveryDelayUntilTime;
/* options taken from recovery.conf for XLOG streaming */
@@ -5227,6 +5228,7 @@ readRecoveryCommandFile(void)
*head = NULL,
*tail = NULL;
boolrecoveryTargetActionSet = false;
+ const char *hintmsg;
fd = AllocateFile(RECOVERY_COMMAND_FILE, "r");
@@ -5452,8 +5454,6 @@ readRecoveryCommandFile(void)
}
else if (strcmp(item->name, "recovery_min_apply_delay") == 0)
{
- const char *hintmsg;
-
if (!parse_int(item->value, _min_apply_delay,
GUC_UNIT_MS,
))
ereport(E
