Recovery conflict due to buffer pins

From: Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Recovery conflict due to buffer pins
Date: 2022-06-13 18:05:35
Message-ID: CAFpL5Vxm4B2885NqXjUmZL7zpCGgL2dy=AGtOe9F31J0X+oz5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2022-06-13 18:30:44 Re: Recovery conflict due to buffer pins
Previous Message Scott Ribe 2022-06-13 14:33:04 Re: Pgbouncer pool_mode and application behavior