***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 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.
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 row versions 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".
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