Обсуждение: Recovery conflict due to buffer pins

Поиск
Список
Период
Сортировка

Recovery conflict due to buffer pins

От
Nikhil Shetty
Дата:
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


Re: Recovery conflict due to buffer pins

От
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

Re: Recovery conflict due to buffer pins

От
Nikhil Shetty
Дата:
Hi Tom,

We have set max_standby_streaming_delay to 0 because we have a synchronous standby with synchronous_commit as 'remote_apply'. We want to make sure that queries are cancelled so that it doesn't affect the primary. 

Is there any other way to resolve this apart from setting max_standby_streaming_delay to a value greater than zero?

Thanks,
Nikhil


On Tue, Jun 14, 2022 at 12:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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