On Jan 23, 2017, at 10:06 AM, Jerry Sievers <gsiever...@comcast.net> wrote: > > Israel Brewster <isr...@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/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.
Sounds reasonable. Allows for however long the dump process needs without allowing for runaway queries in the general case, as long as I make sure to implement things in a way that makes sure the pg_xlog_replay_resume() is always called, no matter what happens (not that I've had any failures, I just tend to be a bit paranoid about this system). Thanks! ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ----------------------------------------------- > > > >> ----------------------------------------------- >> 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.consult...@comcast.net > p: 312.241.7800
