Israel Brewster <israel@xxxxxxxxxxxxxx> writes: > 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? "Best" way depends on your needs... You can pause your standby and/or configure settings like the one you mentioned to tolerate the dump conflicting with replication by allowing the standby to lag rather than issuing an cancel. select pg_xlog_replay_pause(); -- dump here select pg_xlog_replay_resume(); The above will of course guarantee that your slave lags vs fiddling with the max delay settings and being then subject to possibly moving target in terms of dump duration and upstream system behavior. > ----------------------------------------------- > Israel Brewster > Systems Analyst II > Ravn Alaska > 5245 Airport Industrial Rd > Fairbanks, AK 99709 > (907) 450-7293 > ----------------------------------------------- > > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@xxxxxxxxxxx p: 312.241.7800 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
