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 1pbP3h-00059v-5j for pgsql-general@arkaria.postgresql.org; Sun, 12 Mar 2023 17:00:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pbP3g-0006gM-43 for pgsql-general@arkaria.postgresql.org; Sun, 12 Mar 2023 17:00:32 +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 1pbP3f-0006gD-NI for pgsql-general@lists.postgresql.org; Sun, 12 Mar 2023 17:00:31 +0000 Received: from mail-qv1-xf31.google.com ([2607:f8b0:4864:20::f31]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pbP3d-0004Kk-AB for pgsql-general@lists.postgresql.org; Sun, 12 Mar 2023 17:00:31 +0000 Received: by mail-qv1-xf31.google.com with SMTP id op8so6874117qvb.11 for ; Sun, 12 Mar 2023 10:00:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1678640427; h=content-transfer-encoding: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=PaY761z1YZ3H6Cl5I/2/w/rBfXlU5DXvbovBLAcDh0M=; b=Fe2JiGX+HP1HpZ56YS0asL8wEYGj4tzyRocjOR0M0qQm9ard0rXNsC69U7CrgUmDbV //n+Cg6WBTDdKg+wpuaZoquuiRbbLKIAUWmGEzaHCnNTl6QayaGjC+uyntEMaVziT8jm kEgtNBh4yJm3aZHi2VmEqNTwEdXU5Mmtw5GZaawWlcEfdK1fjDb9gCY78Bi5vYbCo+W3 yHcchbUe6QTZc0cKNNsQ5blbSE5lpQIB7qRaMI8j2j3ylKbHKBdh7u+Gv7DTvM3D4Tlo TcEDsXLbLDcsXyFDdEQ3PgzZ3dwK78HT53bde8eW9kuUSU8KedzwHnDxe2a1ofRXZ6d6 35LA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678640427; h=content-transfer-encoding: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=PaY761z1YZ3H6Cl5I/2/w/rBfXlU5DXvbovBLAcDh0M=; b=Efqg2mLSeJAJ5mza+ap609pWaQpm7uUDt0GJsf/BjEB5LG7B6lQ15tujOz3vTBHPiD ZO/krIag9p9n7vsZU0iNHyEGt+n0aGdzKYcMq23sBjAcfaZIjTRMgh9Iz280RFkkpb9Z CNS66J32HM18oxTzixeCmkaKN6azWEdN/HMvZLqiY8Q5+LNyHXXUgzjNMXxGcq7NEzvD oNlBne2aKIQnGcDTGmVho2hH6j8VOAIeVnwkuNcRbinUC2DQpbtAmQNrdO5Uiuwd13e5 66JRgSwHW13Z+Z9H5ul+guAVfkb5S+Fzr7iilgyx0+7XjazZvA8+pErCEiJN/sVQRvLr 0zkw== X-Gm-Message-State: AO0yUKXxfnzt2zdi5My/wSmtZhFg0GwDufzEK/ibE3mXa2/SwiTNrPAu E+O97N9Z8+selqvDTA6S+TzohiIHYqE= X-Google-Smtp-Source: AK7set83RzxhXr6rEYJdKAWIYlkgrPZR+Sm9oeFMuL2YLBhkrO7b2WQTuqqXnGpcwM1vKzKjA1ibcg== X-Received: by 2002:ad4:5de3:0:b0:572:592c:655d with SMTP id jn3-20020ad45de3000000b00572592c655dmr7966624qvb.42.1678640426755; Sun, 12 Mar 2023 10:00:26 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id 137-20020a370a8f000000b006fa16fe93bbsm3840842qkk.15.2023.03.12.10.00.26 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 12 Mar 2023 10:00:26 -0700 (PDT) Message-ID: Date: Sun, 12 Mar 2023 12:00:26 -0500 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: <065aeb58c2593e4087b42556de0a4af2a9ab2819.camel@cybertec.at> From: Ron In-Reply-To: <065aeb58c2593e4087b42556de0a4af2a9ab2819.camel@cybertec.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 3/12/23 09:01, Laurenz Albe wrote: > On Sun, 2023-03-12 at 01:53 +0530, Atul Kumar wrote: >> 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. >> >> Which parameter do I need to configure to serve this purpose ? And >> When will the remaining two parameters be used ? >> >> It would be great if anyone can explain them with a brief example. > The parameter that does what you describe you want is "recovery_min_apply_delay". > > The other parameters only deal with delaying replication in the face of a > replication conflict. > > Note that changes are immediately shipped to the standby, what is delayed with > "recovery_min_apply_delay" is only the replay of the WAL information. > > So you can recover from a logical problem like DROP TABLE by stopping the > standby, setting "recovery_target_time" to a time before the problem happened > and then restarting the standby. Then recovery will stop before the problem > is replayed. How do you determine what to set recovery_min_apply_delay to? 15 minutes... an hour... 8 hours... a week?  (OP's problem is better solved by restoring a recent backup to a separate instance, while letting replication do it's DR/HA things.) -- Born in Arizona, moved to Babylonia.