Re: Queries are failing on standby server

How do those config options interact with replication slots?

On Thu, Jul 25, 2024 at 9:54 AM 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.,'
>>
>>