Обсуждение: ***Conflict with recovery error***
Hi Team,
Hope you guys are doing good.
We are facing below issue with read replica we did work arounds by setting hot_standby_feedback, max_standby_streaming_delay and max_standby_archive_delay, which indeed caused adverse effects on primary DB and storage. As our DB is nearly 6 TB which runs as AWS Postgres RDS.
Even the below error occurs on tables where vacuum is disabled and no DML operations are permitted. Will there be any chances to see row versions being changed even if vacuum is disabled.
Please advise.
2023-01-13 07:20:12 UTC:10.64.103.75(61096):ubpreplica@ubprdb01:[17707]:ERROR: canceling statement due to conflict with recovery
2023-01-13 07:20:12 UTC:10.64.103.75(61096):ubpreplica@ubprdb01:[17707]:DETAIL: User query might have needed to see row versions that must be removed.
Thanks & Regards,
Abhishek P
On Fri, 2023-01-20 at 08:56 +0000, Abhishek Prakash wrote: > We are facing below issue with read replica we did work arounds by setting > hot_standby_feedback, max_standby_streaming_delay and max_standby_archive_delay, > which indeed caused adverse effects on primary DB and storage. As our DB is > nearly 6 TB which runs as AWS Postgres RDS. > > Even the below error occurs on tables where vacuum is disabled and no DML > operations are permitted. Will there be any chances to see row versions > being changed even if vacuum is disabled. > Please advise. > > 2023-01-13 07:20:12 UTC:10.64.103.75(61096):ubpreplica@ubprdb01:[17707]:ERROR: canceling statement due to conflict withrecovery > 2023-01-13 07:20:12 UTC:10.64.103.75(61096):ubpreplica@ubprdb01:[17707]:DETAIL: User query might have needed to see rowversions that must be removed. It could be HOT chain pruning or an anti-wraparound autovacuum (which runs even if autovacuum is disabled). Disabling autovacuum is not a smart idea to begin with. Your best bet is to set "max_standby_streaming_delay = -1". More reading: https://www.cybertec-postgresql.com/en/streaming-replication-conflicts-in-postgresql/ Yours, Laurenz Albe
Hi Laurenz, Thanks for your reply. We had set max_standby_streaming_delay = -1, but faced storage issues nearly 3.5 TB of storage was consumed. Regards, Abhishek P -----Original Message----- From: Laurenz Albe <laurenz.albe@cybertec.at> Sent: Friday, January 20, 2023 3:26 PM To: Abhishek Prakash <abhishek.prakash08@infosys.com>; pgsql-general@lists.postgresql.org; pgsql-hackers@lists.postgresql.org;usergroups@postgresql.org Subject: Re: ***Conflict with recovery error*** [**EXTERNAL EMAIL**] On Fri, 2023-01-20 at 08:56 +0000, Abhishek Prakash wrote: > We are facing below issue with read replica we did work arounds by > setting hot_standby_feedback, max_standby_streaming_delay and > max_standby_archive_delay, which indeed caused adverse effects on > primary DB and storage. As our DB is nearly 6 TB which runs as AWS Postgres RDS. > > Even the below error occurs on tables where vacuum is disabled and no > DML operations are permitted. Will there be any chances to see row > versions being changed even if vacuum is disabled. > Please advise. > > 2023-01-13 07:20:12 > UTC:10.64.103.75(61096):ubpreplica@ubprdb01:[17707]:ERROR: canceling > statement due to conflict with recovery > 2023-01-13 07:20:12 UTC:10.64.103.75(61096):ubpreplica@ubprdb01:[17707]:DETAIL: User query might have needed to see rowversions that must be removed. It could be HOT chain pruning or an anti-wraparound autovacuum (which runs even if autovacuum is disabled). Disabling autovacuum is not a smart idea to begin with. Your best bet is to set "max_standby_streaming_delay = -1". More reading: https://apc01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.cybertec-postgresql.com%2Fen%2Fstreaming-replication-conflicts-in-postgresql%2F&data=05%7C01%7Cabhishek.prakash08%40infosys.com%7Ce50f15f9ec4a497669a208dafacc8a3c%7C63ce7d592f3e42cda8ccbe764cff5eb6%7C0%7C0%7C638098053794261389%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2FlYwVKhkjP23vza5yhuJfw6mcOYynDVbNIhnKRBwUu4%3D&reserved=0 Yours, Laurenz Albe
On Fri, 2023-01-20 at 09:59 +0000, Abhishek Prakash wrote: > We had set max_standby_streaming_delay = -1, but faced storage issues > nearly 3.5 TB of storage was consumed. Then either don't run queries that take that long or run fewer data modifications on the primary. Or invest in a few more TB disk storage. Yours, Laurenz Albe