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