Re: pg_dump Conflict with recovery

[julyanto SUTANDANG <julyanto@xxxxxxxxxxxx>]

On Tue, Jan 24, 2017 at 5:45 AM, Israel Brewster <israel@xxxxxxxxxxxxxx> wrote:

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

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?

You can try to use *pg_basebackup* rather than only pg_dump.. it is faster, and more approriate. 

actually pg_basebackup is a command tool also for making replicas.

Regards,

Julyanto SUTANDANG

Equnix Business Solutions, PT
(An Open Source and Open Mind Company)
www.equnix.co.id
Pusat Niaga ITC Roxy Mas Blok C2/42.  Jl. KH Hasyim Ashari 125, Jakarta Pusat
T: +6221 22866662 F: +62216315281 M: +628164858028

Caution: The information enclosed in this email (and any attachments) may be legally privileged and/or confidential and is intended only for the use of the addressee(s). No addressee should forward, print, copy, or otherwise reproduce this message in any manner that would allow it to be viewed by any individual not originally listed as a recipient. If the reader of this message is not the intended recipient, you are hereby notified that any unauthorized disclosure, dissemination, distribution, copying or the taking of any action in reliance on the information herein is strictly prohibited. If you have received this communication in error, please immediately notify the sender and delete this message.Unless it is made by the authorized person, any views expressed in this message are those of the individual sender and may not necessarily reflect the views of PT Equnix Business Solutions. 

"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