Israel Brewster <israel@ravnalaska.net> writes:
> I have a backup strategy that in part consists of doing pg_dump s on my various databases. In order to hopefully
reduce/preventoperational 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
whentrying 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
suchcould 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@comcast.net
p: 312.241.7800