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 instance:
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.