Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pb6EY-0006Je-Pr for pgsql-general@arkaria.postgresql.org; Sat, 11 Mar 2023 20:54:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pb6EX-00049j-Oz for pgsql-general@arkaria.postgresql.org; Sat, 11 Mar 2023 20:54:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pb6EX-00049Z-CD for pgsql-general@lists.postgresql.org; Sat, 11 Mar 2023 20:54:29 +0000 Received: from mail-qt1-x829.google.com ([2607:f8b0:4864:20::829]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pb6EO-0000a8-Vh for pgsql-general@lists.postgresql.org; Sat, 11 Mar 2023 20:54:27 +0000 Received: by mail-qt1-x829.google.com with SMTP id y10so9561190qtj.2 for ; Sat, 11 Mar 2023 12:54:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1678568059; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=Uyh/RJ0fJn1YwJT86KkTlqGFXMaYyUEsUdUhMzhzq48=; b=Sdf2O/XEXagNZZMZLoT1g3w7KZZbLh9f5TNyn3mlOyetLpnW3wLQwmkgnzPeSmqpOE hM/XrQ7hx/hh/wg9i28J35QHewvqgnujo66b819h9+a80LPqr6eyeb8EDmhX5SYKLJ0v oWoHZM3ctiUwpljIZcIcT0yjnJuqC4JFdbwWgpjtgI4tPrQ8vXCH/Z4j36tIqRE+BrTH Jt0Mhu7IqasG/bp23kmJOOBAW7xKgaaNne2c0VT9tZg4NNFxCbgqrgAYXKF4zTDPN5mW WeFBPTC1ndWLrL12X3Ui1eOJOduSwD9tQBsrWaCJ7QuXgDl9Bsaa2NckBdY1YVfsSFmn SrSQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678568059; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=Uyh/RJ0fJn1YwJT86KkTlqGFXMaYyUEsUdUhMzhzq48=; b=BKYw90HYdUntuKCsExLkpj/p3RJugSzwV/U1ulViHnVs9xbQo5jrtF5RSVh2Uh8mU+ Khu+4IIYnC9B9c/DJ7q+ToxBNAB/rg3+cqUl2ohJ2eYA+S25Jp4h8sWYBqsaod+j2ZiB bufEwEvmh0wh+eq+ykDiLtkiHm4dfCNaODM3XKwHKLln39HegsxpeexltZEpumzyUrp3 VCRGn92IIT9N6/n8H3X2Xuc1Y9VZGbwBHZMJlr1gxHXLM81GFlZYvAsS1BDxFmBWyzJF CyRGkSe9pWQB+saJ0iW/vt07qxCrQ+JMNlKbZNq93jFbdRRcyJjUU2EpA4wGgw8g4z1r NxYA== X-Gm-Message-State: AO0yUKX9W4Se/oMBJZu7SRIW9xXJvQCnXzgHPYpKwCVSd7Foci7Q60k8 +HRQrHK1j48e1pf5Vf++ovDZ3ir7CVQ= X-Google-Smtp-Source: AK7set/4YINjOt7esJVyBTEsMxS46VYGELgoczAW0xpjtiTa+htn1yoz6G026RgYgrn+DeRC8DQ2pQ== X-Received: by 2002:a05:622a:50f:b0:3c0:6cf:3226 with SMTP id l15-20020a05622a050f00b003c006cf3226mr44274315qtx.8.1678568058720; Sat, 11 Mar 2023 12:54:18 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id y10-20020ac83e8a000000b003bfbf3afe51sm2397027qtf.93.2023.03.11.12.54.18 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 11 Mar 2023 12:54:18 -0800 (PST) Content-Type: multipart/alternative; boundary="------------LnVdOvXOhqJ6HmWdtCYmxEY0" Message-ID: Date: Sat, 11 Mar 2023 14:54:18 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: confusion between max_standby_archive_delay, max_standby_archive_delay and max_standby_archive_delay Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------LnVdOvXOhqJ6HmWdtCYmxEY0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/11/23 14:23, Atul Kumar wrote: > Hi, > > Could someone help me in telling the difference between these > three parameters > 1. max_standby_archive_delay > 2. max_standby_streaming_delay > 3. recovery_min_apply_delay > > My basic motive is to make the standby database server to be delayed to > apply the changes on itself,  if any data has been accidentally > deleted/updated/ truncated from the primary server. What you're looking for is that WAL files be "aggregated and *optimized*" by removing statements which don't have any impact on the final state of instance. That's great for *restoring* instances with update-heavy databases, and can only implemented by backup/restore software, or roll-your-own log shipping.  Just as importantly, that aggregation-optimization software has it's own costs (time and disk space), since it takes time to scan through all the recovery logs, keeping track of what must stay, what can be tossed, and the final state. -- Born in Arizona, moved to Babylonia. --------------LnVdOvXOhqJ6HmWdtCYmxEY0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 3/11/23 14:23, Atul Kumar wrote:
Hi,

Could someone help me in telling the difference between these three parameters 
1. max_standby_archive_delay
2. max_standby_streaming_delay
3. recovery_min_apply_delay

My basic motive is to make the standby database server to be delayed to apply the changes on itself,  if any data has been accidentally deleted/updated/ truncated from the primary server.

What you're looking for is that WAL files be "aggregated and optimized" by removing statements which don't have any impact on the final state of instance.

That's great for restoring instances with update-heavy databases, and can only implemented by backup/restore software, or roll-your-own log shipping.  Just as importantly, that aggregation-optimization software has it's own costs (time and disk space), since it takes time to scan through all the recovery logs, keeping track of what must stay, what can be tossed, and the final state.

--
Born in Arizona, moved to Babylonia.
--------------LnVdOvXOhqJ6HmWdtCYmxEY0--