search

reactive-tech / kubegres

Kubegres is a Kubernetes operator allowing to deploy one or many clusters of PostgreSql instances and manage databases replication, failover and backup.
https://www.kubegres.io
Apache License 2.0
1.32k stars 74 forks source link

Error message from server: ERROR: canceling statement due to conflict with recovery #86

Open joeywang opened 2 years ago

joeywang commented 2 years ago

Hi @alex-arica and the team, Happy new year. Thanks a lot for your hard work here.

It's a very good feature to configure own backup.sh as your own backup script. We encounter a problem recently as shown in the title.

ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. STATEMENT: SELECT 1 B95: Canceling statement due to conflict with recovery

There are a few options to fix this:

1) Set hot_standby_feedback = on on the standby, which passed back information to the primary that certain rows are still needed in a table. This will cause bloat on the primary, but allows queries on the secondary to finish reliably.

2) Increase max_standby_archive_delay / max_standby_streaming_delay, which allows the standby server to intentionally increase replication lag to allow queries to finish.

3) back up from the primary host instead of standby host.

I can not find a way in kubegres to fix this unless I hardcoded the service to primary db with 3. I think to do 1 or 2 need the support of customize postgres.conf for standby instances.

Please let me know if you any solutions, thanks.

michbsd commented 2 years ago

Hi,

We are also seeing this type of error while attempting to backup a large table on the replica DB.

pg_dump: error: Error message from server: ERROR:  canceling statement due to conflict with recovery

Would be great to have a fix in kubegres.