This article is focused on resolving
Canceling statement due to conflict with recovery, which is a common issue when dealing with master-slave replication.
- PostgreSQL cluster
Step 1. Connect to a secondary (replica) node and open the
Step 2. Set
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.
Now, there is alternative solution you could try if the above steps don’t help. It includes avoiding all replication conflicts by setting the
off. But, keep in mind, this could easily cause bloat on the primary node.
hot_standby = on # "off" disallows queries during recoverry
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.