Re: [GENERAL] pg_dump Conflict with recovery

On Jan 23, 2017, at 10:06 AM, Jerry Sievers <gsievers19@comcast.net> wrote:

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/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.consulting@comcast.net
p: 312.241.7800