Recovery conflict due to buffer pins

[Nikhil Shetty <nikhil.dba04@xxxxxxxxx>]

Hi,

PostgreSQL version - 11.7

We are seeing using few queries to fetch data from standby for monitoring but we often see that the query is cancelled due to 'conflict with recovery'.

Example:

ERROR: canceling statement due to conflict with recovery

2022-06-12 04:30:01 UTC [3594]: [5-1] user=<user>,db=postgres,app=<app>,client=127.0.0.1DETAIL:  User was holding shared buffer pin for too long.

2022-06-12 04:30:01 UTC [3594]: [6-1] user=<user>,db=postgres,app=<app>,client=127.0.0.1STATEMENT:  SET statement_timeout = 30000; SELECT 'dbsize_bytes', COALESCE(sum(pg_database_size(datname)),0) FROM pg_stat_database where datname NOT IN ('postgres','template0','template1');

2022-06-12 04:30:01 UTC [3324]: [3-1] user=<user>,db=postgres,app=<app>,client=127.0.0.1ERROR:  canceling statement due to conflict with recovery

04:30:01 UTC [3324]: [4-1] user=<user>,db=postgres,app=<app>,client=127.0.0.1DETAIL:  User was holding shared buffer pin for too long.

04:30:01 UTC [3324]: [5-1] user=<user>,db=postgres,app=<app>,client=127.0.0.1STATEMENT:  SELECT 'tuple_returned', sum(tup_returned)  FROM pg_stat_database;

I have verified that there is a vacuum operation happening at the same time on primary.

We have set below parameters in standby

hot_standby_feedback = on

max_standby_streaming_delay = 0

How can we avoid this error on standby? 

Regards,

Nikhil