From: | Wasim Devale <wasimd60(at)gmail(dot)com> |
---|---|
To: | Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Queries are failing on standby server |
Date: | 2024-07-25 16:54:06 |
Message-ID: | CAB5fag4RaEM-V6Hr9KagXOhtV_mYQONWGB5XApBMCCghwtd9cw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
? The it will be problematic if database is under high insert delete load.
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2024-07-25 16:59:56 | Re: Queries are failing on standby server |
Previous Message | Ron Johnson | 2024-07-25 14:00:58 | Re: Queries are failing on standby server |