Thread: Recovery conflict due to buffer pins
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
Nikhil Shetty <nikhil.dba04@gmail.com> writes: > 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. > We have set below parameters in standby > hot_standby_feedback = on > max_standby_streaming_delay = 0 > How can we avoid this error on standby? Use a larger max_standby_streaming_delay. Setting it to zero means precisely that conflicting queries will be canceled immediately. regards, tom lane
Nikhil Shetty <nikhil.dba04@gmail.com> writes:
> 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.
> We have set below parameters in standby
> hot_standby_feedback = on
> max_standby_streaming_delay = 0
> How can we avoid this error on standby?
Use a larger max_standby_streaming_delay. Setting it to zero means
precisely that conflicting queries will be canceled immediately.
regards, tom lane