Re: Hot Standby - ERROR: canceling statement due to conflict with recovery

On Sun, Feb 27, 2011 at 1:57 PM, Sean Laurent <sean(at)studyblue(dot)com> wrote:
> Right. I read all of that. I guess I just assumed it was possible to create
> a snapshot on the standby so that a longer running on the standby could
> complete. In particular, I was really hoping to run database dumps against
> the standby, not the master.
> Thanks.

It is possible to pull dumps from a hot standby, at least in theory
(I've never tried it). You can delay the timeout period via
max_standby_archive_delay and max_standby_streaming_delay to block
WAL application until the snapshot resolves. You can also use the
dblink approach mentioned by others. This is obviously you have to
think about very carefully before doing and some experimentation may
be in order.

Curious: has anybody set up 2+ hot standby reading from a primary,
with one set up as HA-ish config and one with a config set up for long
running queries, dumps etc? Has anyone successfully pulled off dumps
from a hot standby on a busy server?

merlin