Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s7ZzB-006HM0-AV for pgsql-admin@arkaria.postgresql.org; Thu, 16 May 2024 12:13:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s7ZzB-007mr3-7B for pgsql-admin@arkaria.postgresql.org; Thu, 16 May 2024 12:13:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s7ZzA-007mm2-LO for pgsql-admin@lists.postgresql.org; Thu, 16 May 2024 12:13:24 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s7Zz7-000Ugi-Uq for pgsql-admin@lists.postgresql.org; Thu, 16 May 2024 12:13:23 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-a59c448b44aso318922166b.2 for ; Thu, 16 May 2024 05:13:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pythian.com; s=google; t=1715861600; x=1716466400; darn=lists.postgresql.org; h=content-language:in-reply-to:from:references:to:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ZIrh1Pe81vf78yKGvQBN7JMqPvbOZNnTpFhERrLLv8s=; b=U8Fn+xuaYoRnBo5x1LWHAhP1bf14s3KJOXKfcRHLcpc5vwkj2IzF58+deJ51h2Cpph 7FEYX7GVEK0BzSimpJ4dsRotVRJK0d9+PFg3+UNYLKIO1XLF73ZqXJ1Hf+byb3A3LzNA 7Yini9/j6gRiHdSRNY7F232sjMc4i4+fiBtO4vJo31Beyc63FYjjBnyzz+jiytReshIM k20T3mGlT8vDIbqnQhGARGYYwmrlWJHmttXnK4/2xdSy2G9QMjST85tUOpWToqhB+qHj 0SrCuiebUHiflFXGFvnV9ZP91jhL3fZypGLGLODyRk/lITlvIMTIJ6ZdKi1GvV5HN6FP Ls7w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1715861600; x=1716466400; h=content-language:in-reply-to:from:references:to:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ZIrh1Pe81vf78yKGvQBN7JMqPvbOZNnTpFhERrLLv8s=; b=r/aFjdzAv7OPxoKp993bQH6IkuC1asi+78Q0ImHMkTg0OFnFhQl8zGLwOFwha1XPgs LeHB0cpa/mBSsyTrNXAQx/x4NawCggqKNzFbz9EzurGnMb2UM6ajMfHBB0O3yq6bSW4f dj6xTDxeZsLy8e6YppB+k5sOntbeKmzFcQsv8hnVpIfK6T1Mj1ayKxxP4nQOLpKEjKjz IBDXDa1CrMGdC9MeUGfiSUsZyn1zAjjI9jPHJlNjVIB3f6eQd+yjvSlIP4ymtnfxYEfU JVg1bzSx3lje/3td+NZe9FyPTtqGgDLuTPqEHPOnnkztawli9X4lKKFux8TgOw9SwVZd YGKw== X-Gm-Message-State: AOJu0YypvUbbbPkaZzwkZ1/cLbgnu+jAazQLoM4JJC4c3ArfpTEeg6jz hiAWCLeBpmussDlzfu86pdWrpZ9uShSSpr5J77iAnrAoWdQUOwEG1hSl6FQvs3bKV6hfdB9+BQw tUiaFnCX23mcCX07sC5pxu3rq1otDTlToo7Z/47jUn1aJU0DRFspWyQsQJa8mOzTS5WS++IboTy 5kxq/AkvIXiWrX/7C42nbiMdrUoWcjbVN0sKB3SkukhPiLRgizraFyTg== X-Google-Smtp-Source: AGHT+IF31UWYIi+1BxdT9ifxf9qSiSuuMdNBnV4qeddSBfdu4wOt+wYx5WRpwi+xrC454HUrm0IXkg== X-Received: by 2002:a17:906:5fd5:b0:a59:c28a:7ec2 with SMTP id a640c23a62f3a-a5a2d5d49bemr1117475266b.41.1715861599721; Thu, 16 May 2024 05:13:19 -0700 (PDT) Received: from [10.0.0.3] (chap-10-b2-v4wan-169427-cust391.vm26.cable.virginm.net. [92.234.49.136]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a5ce2eabad1sm95044566b.202.2024.05.16.05.13.19 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 16 May 2024 05:13:19 -0700 (PDT) Message-ID: <3fe3d1ea-6bba-44bd-ac66-d8730d643fbe@pythian.com> Date: Thu, 16 May 2024 13:13:18 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Queries in replica are failing To: pgsql-admin@lists.postgresql.org References: From: Matt Pearson In-Reply-To: Content-Type: multipart/alternative; boundary="------------OkWFbikm060NxpQ4K5hgtb5w" Content-Language: en-GB 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. --------------OkWFbikm060NxpQ4K5hgtb5w Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: quoted-printable Hi, I would probably use "/hot_standby_feedback/" rather than change the=20 delay parameters, unless you want to have the read replica (Standby)=20 actually to operate some time behind the primary, for some reason (like=20 having a copy of the data an hour old to fix mistakes on the primary). https://postgresqlco.nf/doc/en/param/hot_standby_feedback/ The /hot_standby_feedback /parameter sends feedback to the primary, so=20 the transaction is less likely to be cancelled.=C2=A0 The only draw back is= =20 that is can cause some bloat on the primary database. Regards, Matt On 16/05/2024 13:01, Siraj G wrote: > Hello - > > Queries in replica instance are failing with the error=20 > "cancelling=C2=A0statement due to conflict with recovery". I was checking= =20 > two parameters (max_standby_archive_delay and=20 > max_standby_streaming_delay) =C2=A0which may allow the queries to run=20 > within the time defined in those. > > Is it recommended to set those? Is there any other suggestion to=20 > tackle this? > > Regards > Siraj --=20 -- --------------OkWFbikm060NxpQ4K5hgtb5w Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi,

I would probably use "hot_standby_feedback" rather than change the delay parameters, unless you want to have the read replica (Standby) actually to operate some time behind the primary, for some reason (like having a copy of the data an hour old to fix mistakes on the primary).

https://postgresqlc= o.nf/doc/en/param/hot_standby_feedback/

The hot_standby_= feedback parameter sends feedback to the primary, so the transaction is less likely to be cancelled.=C2=A0 The only draw back is that is can cause some bloat on the primary database.

Regards,

Matt


On 16/05/2024 13:01, Siraj G wrote:
Hello -

Queries in replica instance are failing with the error "cancelling=C2=A0statement due to conflict with recovery". I was checking two parameters (max_standby_archive_delay and max_standby_streaming_delay)=C2=A0=C2=A0which may al= low the queries to run within the time defined=C2=A0in those.

Is it recommended to set those? Is there any other suggestion to tackle this?

Regards
Siraj

  





--



--------------OkWFbikm060NxpQ4K5hgtb5w--