Re: some queries on standby preventing replication updates

On 28/10/14 19:37, Joe Van Dyk wrote:
> On Mon, Oct 27, 2014 at 6:22 AM, Emanuel Calvo
> <emanuel(dot)calvo(at)2ndquadrant(dot)com <mailto:emanuel(dot)calvo(at)2ndquadrant(dot)com>>
> wrote:
>
>
> El 23/10/14 a las 17:40, Joe Van Dyk escibió:
> > Hi,
> >
> > I have a master and a slave database.
> >
> > I've got hot_standby_feedback turned on,
> > max_standby_streaming_delay=-1. I've configured the master and slave
> > to keep a few days of WALs around.
> >
> > I've noticed that when some large queries are run on the standby
> > machine (ones that take more than a minute or so), replication updates
> > are paused. Is there a way to fix this?
> >
> You may need to set a value on max_standby_streaming_delay, which
> controls the time
> before cancelling the standby queries when a conflict occurs on a
> wal-records-about-to-be-applied.
>
> Source:
> http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html
>
>
> I'm using -1 for that option, would using something different be better?

It depends on what you want to achieve. If you want to sacrifice your
long-running query to keep replication going, set the value to >0. If
you (like me) are using the slave to run analytical queries that can
take many hours or even days, I'd rather live with the current
behaviour. When the long-running query is over the wal receiver
automatically reconnects to the master. The only thing you should make
sure is to keep enough wal segments. With 9.4 even that gets easier.
There you can assign a replication slot to the replica and the master
then knows which segments are still needed when the slave reconnects.

Torsten