Re: Queries are failing on standby server

ERROR: User query might have needed to see row versions that must be
removed.canceling statement due to conflict with recovery ERROR: canceling
statement due to conflict with recovery SQL state: 40001 Detail: User query
might have needed to see row versions that must be removed.

So how to tackle the above error. PG version is 12.8 and has a replication
slot created.

On Thu, Jul 25, 2024 at 10:30 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
wrote:

> On Thu, Jul 25, 2024 at 12:54 PM Wasim Devale <wasimd60(at)gmail(dot)com> wrote:
>
>> This allows the replica to communicate with the primary about what
>> queries are running so that the primary does not clean up the old rows that
>> cause the replication conflict.
>>
>> What does the above statement mean? Primary does not clean up the old
>> rows ?
>>
>
> If you're executing long-running queries on the replica, then you *you
> don't want the old rows cleaned up* until the long-running query is
> complete.
>
>
>> The it will be problematic if database is under high insert delete load.
>>
>
> Which is why it's only recommended in rare circumstances.
>
>
>>
>> On Thu, 25 Jul, 2024, 7:24 pm Keith Fiske, <keith(dot)fiske(at)crunchydata(dot)com>
>> wrote:
>>
>>> Two options here, both settings on the replica side:
>>>
>>> - Set "max_standby_streaming_delay" to a value that will allow your
>>> statements time to complete on the standby. If they take longer than this,
>>> you may see the same error. Note that setting this pauses ALL replication
>>> if a conflicting statement is encountered until it either completes or the
>>> statement timeout is hit. So if there's long running, conflicting
>>> statements, you can see a significant delay in any new data showing up on
>>> the replicas. This does not allow the potential bloat caused by the next
>>> option.
>>>
>>>
>>> https://www.postgresql.org/docs/16/runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY
>>>
>>> - Set "hot_standby_feedback" to on. This allows the replica to
>>> communicate with the primary about what queries are running so that the
>>> primary does not clean up the old rows that cause the replication conflict.
>>> Note that this makes it so queries run on the replica would have the same
>>> effect on MVCC and VACUUM that running them on the primary would have. This
>>> means long running statements will cause VACUUM to skip those rows/tables
>>> and may cause additional bloat. This is the preferred method if you do not
>>> want replication to be delayed by conflicting statements.
>>>
>>>
>>> https://www.postgresql.org/docs/16/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
>>>
>>> On Thu, Jul 25, 2024 at 9:30 AM Wasim Devale <wasimd60(at)gmail(dot)com> wrote:
>>>
>>>> Hi All
>>>>
>>>> The queries are failing on the standby server. Please note that Primary
>>>> server loading of data is going on and under load.
>>>>
>>>> What setting do we need to configure to not conflict with queries.
>>>> Below is the error.
>>>>
>>>> Failure happened on 'Source' side.
>>>> ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc
>>>> Operation
>>>> Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR
>>>> [40001] [Microsoft][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR:
>>>> VERROR; canceling statement due to conflict with recovery(Detail User query
>>>> might have needed to see row versions that must be removed.; File
>>>> postgres.c; Line 3133; Routine ProcessInterrupts; ),Source=mspsql27.,'
>>>>
>>>> Thanks,
>>>> Wasim
>>>>
>>>>
>>>>
>>>
>>> --
>>> Keith Fiske
>>> Senior Database Engineer
>>> Crunchy Data - http://crunchydata.com
>>>
>>