B95: Canceling statement due to conflict with recovery

Example Postgres Log Output:

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

Explanation:

This error will occur when the standby server gets updates/deletes in the WAL stream that will invalidate data currently being accessed by a running query.

You will primary see this with long-running queries accessing tables with significant activity on the primary.

Recommended Action:

There are two approaches to avoid this kind of query cancellation:

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.

Depending on your use case, either 1) or 2) makes sense - you may also be okay with queries occasionally being canceled, since you can simply try to resubmit the query and it may succeed then.

Learn More:

• Postgres Documentation: Handling Query Conflicts