Re: Queries are failing on standby server - Mailing list pgsql-admin

On Thu, Jul 25, 2024 at 12:54 PM Wasim Devale <wasimd60@gmail.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.fiske@crunchydata.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@gmail.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