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
       </listitem>
      </varlistentry>
 
+     <varlistentry id="recovery-min-apply-delay-reconnect" 
xreflabel="recovery_min_apply_delay_reconnect">
+      <term><varname>recovery_min_apply_delay_reconnect</varname> 
(<type>integer</type>)
+      <indexterm>
+        <primary><varname>recovery_min_apply_delay_reconnect</> recovery 
parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        If the streaming replication is inturruped while
+        <varname>recovery_min_apply_delay</varname> is set, WAL records will be
+        replayed from the archive. After all records have been processed from
+        local disk, <productname>PostgreSQL</> will attempt to resume streaming
+        and connect to the master.
+       </para>
+       <para>
+        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.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
    </sect1>
 
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;
        bool            recoveryTargetActionSet = 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, &recovery_min_apply_delay, 
GUC_UNIT_MS,
                                                   &hintmsg))
                                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 = 
recovery_min_apply_delay;
+               }
+               else if (strcmp(item->name, 
"recovery_min_apply_delay_reconnect") == 0)
+               {
+                       if (!parse_int(item->value, 
&recovery_min_apply_delay_reconnect, GUC_UNIT_MS,
+                                                  &hintmsg))
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                                                errmsg("parameter \"%s\" 
requires a temporal value",
+                                                               
"recovery_min_apply_delay_reconnect"),
+                                                hintmsg ? errhint("%s", 
_(hintmsg)) : 0));
+                       if (recovery_min_apply_delay_reconnect > 
recovery_min_apply_delay)
+                               ereport(ERROR,
+                                               
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                                                errmsg("\"%s\" must be <= 
\"%s\"",
+                                                               
"recovery_min_apply_delay_reconnect",
+                                                               
"recovery_min_apply_delay")));
+                       ereport(DEBUG2,
+                                       
(errmsg_internal("recovery_min_apply_delay_reconnect = '%s'", item->value)));
                }
                else
                        ereport(FATAL,
@@ -6080,20 +6099,25 @@ recoveryApplyDelay(XLogReaderState *record)
        if (!getRecordTimestamp(record, &xtime))
                return false;
 
-       recoveryDelayUntilTime =
-               TimestampTzPlusMilliseconds(xtime, recovery_min_apply_delay);
-
-       /*
-        * Exit without arming the latch if it's already past time to apply this
-        * record
-        */
-       TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime,
-                                               &secs, &microsecs);
-       if (secs <= 0 && microsecs <= 0)
-               return false;
 
        while (true)
        {
+               if (WalRcvStreaming())
+                       recoveryDelayUntilTime =
+                               TimestampTzPlusMilliseconds(xtime, 
recovery_min_apply_delay);
+               else
+                       recoveryDelayUntilTime =
+                               TimestampTzPlusMilliseconds(xtime, 
recovery_min_apply_delay_reconnect);
+
+               TimestampDifference(GetCurrentTimestamp(), 
recoveryDelayUntilTime,
+                                                       &secs, &microsecs);
+               /*
+                * Exit without arming the latch if it's already past time to 
apply this
+                * record
+                */
+               if (secs <= 0 && microsecs <= 0)
+                       return false;
+
                ResetLatch(&XLogCtl->recoveryWakeupLatch);
 
                /* might change the trigger file's location */
@@ -6116,6 +6140,15 @@ recoveryApplyDelay(XLogReaderState *record)
                elog(DEBUG2, "recovery apply delay %ld seconds, %d 
milliseconds",
                         secs, microsecs / 1000);
 
+               /*
+                * Loop every 10 seconds so that an alternate delay can be 
calculated if
+                * the WallReceiver is shut down
+                */
+               if (secs > 10) {
+                       secs = 10;
+                       microsecs = 0;
+               }
+
                WaitLatch(&XLogCtl->recoveryWakeupLatch,
                                  WL_LATCH_SET | WL_TIMEOUT | 
WL_POSTMASTER_DEATH,
                                  secs * 1000L + microsecs / 1000,
diff --git a/src/test/recovery/t/005_replay_delay.pl 
b/src/test/recovery/t/005_replay_delay.pl
index 8909c4548b..36b94817f0 100644
--- a/src/test/recovery/t/005_replay_delay.pl
+++ b/src/test/recovery/t/005_replay_delay.pl
@@ -20,13 +20,17 @@ my $backup_name = 'my_backup';
 $node_master->backup($backup_name);
 
 # Create streaming standby from backup
-my $node_standby = get_new_node('standby');
-my $delay        = 3;
+# Set recovery_min_apply_delay_reconnect to verify that in normal conditions it
+# does not interfere with recovery_min_apply_delay
+my $node_standby    = get_new_node('standby');
+my $delay           = 3;
+my $delay_reconnect = 1;
 $node_standby->init_from_backup($node_master, $backup_name,
        has_streaming => 1);
 $node_standby->append_conf(
        'recovery.conf', qq(
 recovery_min_apply_delay = '${delay}s'
+recovery_min_apply_delay_reconnect = '${delay_reconnect}s'
 ));
 $node_standby->start;
 
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to