Re: pg_dump Conflict with recovery

On Jan 23, 2017, at 10:06 AM, Jerry Sievers <gsievers19(at)comcast(dot)net> wrote:
>
> Israel Brewster <israel(at)ravnalaska(dot)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(dot)consulting(at)comcast(dot)net
> p: 312.241.7800