Re: Queries are failing on standby server

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