PostgreSQL ERROR: canceling statement due to conflict with recovery

Question

I'm getting the following error when running a query on a PostgreSQL db in standby mode. The query that causes the error works fine for 1 month but when you query for more than 1 month an error results.

ERROR: canceling statement due to conflict with recovery
Detail: User query might have needed to see row versions that must be removed

Any suggestions on how to resolve? Thanks

Answer 1

No need to touch hot_standby_feedback. As others have mentioned, setting it to on can bloat master. Imagine opening transaction on a slave and not closing it.

Instead, set max_standby_archive_delay and max_standby_streaming_delay to some sane value:

# /etc/postgresql/10/main/postgresql.conf on a slave
max_standby_archive_delay = 900s
max_standby_streaming_delay = 900s

This way queries on slaves with a duration less than 900 seconds won't be cancelled. If your workload requires longer queries, just set these options to a higher value.