Re: pg_dump Conflict with recovery

On Tue, Jan 24, 2017 at 5:45 AM, Israel Brewster <israel(at)ravnalaska(dot)net>
wrote:

> 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?
>
> 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(dot)consulting(at)comcast(dot)net
> p: 312.241.7800
>
>
>