I have a backup strategy that in part consists of doing pg_dump s on my various databases. In order to hopefully reduce/prevent operational slow-down as a result of the backup, I do the dumps from my secondary server, configured as a hot standby with streaming replication.
In general this works fine, but one of my databases has now grown to the point that often as not I get the following when trying to dump the database:
ERROR: canceling statement due to conflict with recovery
DETAIL: User was holding a relation lock for too long.
As I understand it, this is due to the pg_dump taking longer than the max_standby_streaming_delay of 180s, and as such could be easily fixed by upping that value in the config. But is that the "right" fix? Or is there a "better" way?
-----------------------------------------------
5245 Airport Industrial Rd
-----------------------------------------------