From: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Queries are failing on standby server |
Date: | 2024-07-25 14:00:58 |
Message-ID: | CANzqJaCvrKJwV0xODqtSouiYAW0Z9iL0-c_C=9gKuko3=_3Xow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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.,'
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Wasim Devale | 2024-07-25 16:54:06 | Re: Queries are failing on standby server |
Previous Message | Keith Fiske | 2024-07-25 13:54:13 | Re: Queries are failing on standby server |