postgresql,

POSTGRESQL ERROR: Canceling statement due to conflict with recovery

Oct 14, 2021 · 1 min read · Post a comment
POSTGRESQL ERROR: Canceling statement due to conflict with recovery

This article is focused on resolving Canceling statement due to conflict with recovery, which is a common issue when dealing with master-slave replication.

Prerequisites

  • PostgreSQL cluster

Solution

Step 1. Connect to a secondary (replica) node and open the /etc/postgresql/10/main/postgresql.conf file.

Step 2. Set max_standby_archive_delay and max_standby_streaming_delay to some reasonable values. For example:

max_standby_archive_delay = 30s        # max delay before canceling queries
max_standby_streaming_delay = 30s      # max delay before canceling queries

Step 3. Reload the PostgreSQL service.

Alternative solution

Now, there is alternative solution you could try if the above steps don’t help. It includes avoiding all replication conflicts by setting the hot_standby to off. But, keep in mind, this could easily cause bloat on the primary node.

hot_standby = on                        # "off" disallows queries during recoverry

Conclusion

As always, hope this was useful. Cheers!
Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on telegram.