Re: Queries are failing on standby server

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
>>
>