Re: Reporting from Standby

What is the replication conflict you are facing - Can you share output
from pg_stat_database_conflicts ?

Regards,
Mahesh Shetty
DbaUniversity.com

On Thu, Dec 1, 2022, 11:51 PM Don Seiler <don(at)seiler(dot)us> wrote:

> Good afternoon.
>
> PG 12.12, PGDG ubuntu 18.04 LTS build.
>
> We have a primary with a couple of physical/streaming replicas. One
> intended for quick lookups and another intended for long-running reports
> (aka "the reporting replica"). There's also a DR replica in another region
> but we don't touch that for obvious reasons. All streaming replicas
> currently make use of replication slots on the primary as well. We have WAL
> archiving to cloud storage and restore/recovery handled via pgBackrest.
>
> I'm running into the age-old dilemma on the reporting replica where
> queries are being killed with the "canceling statement due to conflict with
> recovery" error. We have hot_standby_feedback=on and both
> max_standby_streaming_delay and max_standby_archive_delay are set to 5
> minutes (300s). I'm fine increasing those delay parameters to 15 minutes or
> more. Like I said this replica is dedicated to long-running reports like
> month-end reports that you'd see today on the first of the month.
>
> I searched around and I see some suggestions like disabling
> hot_standby_feedback on replicas like this to minimize impact on the
> primary, in addition to increasing the delays, or even disabling the delays
> by setting them to -1. With replication slots in use, I want to make sure
> that WAL retention doesn't fill up the WAL volume, would it make sense to
> not use a slot for this replica (and/or not use streaming replication)?
>
> I'm interested to know what people suggest in this space.
>
> --
> Don Seiler
> www.seiler.us
>