Re: Replication: slave is in permanent startup 'recovery'

On 14/04/2011 2:15 AM, Henry C. wrote:
> Greets,
>
> Pg 9.0.3
>
> This must be due to my own misconfiguration, so apologies if I'm not seeing
> the obvious - I've noticed that my slave seems to be stuck in a permanent
> startup/recovery state.

That's what warm- and hot-standby slaves are. They're continuously
replaying WAL files from the master, essentially the same thing as
during recovery from a bad shutdown. The advantage is that it's
*extremely* well tested code.

> If I try and execute a long-lived SQL query on the slave, it eventually fails
> with "canceling statement due to conflict with recovery".

That's a limitation of streaming replication. It's a lot like the issue
Oracle has with running out of undo or redo log space. Essentially, my
understanding is that the hot standby server cannot replay WAL archives
to keep up with the master's changes at the same time as running
queries. To avoid getting too far behind the master because of a huge or
stuck query, it'll cancel very long-running queries.

Again from my limited understanding, the reason it can't replay WAL is
because the WAL records include overwrites of pages VACUUMed and re-used
on the master. HS is block-level replication; it cannot keep a page
in-place on the slave when the master has erased or overwritten it.

It's theoretically possible for the slave to copy blocks that're about
to be written out-of-line into a slave-side-only store of blocks that've
been erased on the master but are still needed by transactions on the
slave. The discussion I've read suggests that that'd be ... complicated
... to make work well especially with log replay happening concurrently.

> Replication is
> definitely working (DML actions are propagated to the slave), but something is
> amiss.

Nope, it's working as designed I'm afraid.

There are params you can tune to control how far slaves are allowed to
get behind the master before cancelling queries. I don't remember what
they are, but the manual will cover them. Do consider though that the
more behind the slave is, the more log files the master has to have
space to keep... and if the master runs out of space, things get ugly.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/