My Postgres backup (or sync) is failing with "ERROR: canceling statement due to conflict with recovery" - how do I fix this?

This error typically occurs when a query underlying the backup (or sync) process takes too long to run. The default amount of lag between the master and the replica is 30 seconds. If a query in the backup process takes longer than that, then the master may tell the replica to perform a query that invalidates the backup process query, and produces this error. The issue is well summarised here: https://pganalyze.com/docs/log-insights/standby/B95.

One possible solution is to edit your PostgreSQL configuration via CustomConfig by adding the following:

{% if server_state == ‘pg_slave’ %}
hot_standby = on
max_standby_archive_delay = 150s
max_standby_streaming_delay = 150s
{% endif %}

This increases the lag to 150 seconds, but it may be necessary to make this longer or shorter, depending on the demands of your application. Bear in mind that longer lags will mean that your replica will be significantly “behind” your master, which can create its own issues. In general you should strive to have as little delay as practical.