Re: streaming replication breaks horribly if master crashes

> The first problem I noticed is that the slave never seems to realize
> that the master has gone away.  Every time I crashed the master, I had
> to kill the wal receiver process on the slave to get it to reconnect;
> otherwise it just sat there waiting, either forever or at least for
> longer than I was willing to wait.

Yes, I've noticed this.  That was the reason for forcing walreceiver to
shut down on a restart per prior discussion and patches.  This needs to
be on the open items list ... possibly it'll be fixed by Simon's
keepalive patch?  Or is it just a tcp_keeplalive issue?

> More seriously, I was able to demonstrate that the problem linked in
> the thread above is real: if the master crashes after streaming WAL
> that it hasn't yet fsync'd, then on recovery the slave's xlog position
> is ahead of the master.  So far I've only been able to reproduce this
> with fsync=off, but I believe it's possible anyway, 

... and some users will turn fsync off.  This is, in fact, one of the
primary uses for streaming replication: Durability via replicas.

> and this just
> makes it more likely.  After the most recent crash, the master thought
> pg_current_xlog_location() was 1/86CD4000; the slave thought
> pg_last_xlog_receive_location() was 1/8733C000.  After reconnecting to
> the master, the slave then thought that
> pg_last_xlog_receive_location() was 1/87000000.  

So, *in this case*, detecting out-of-sequence xlogs (and PANICing) would
have actually prevented the slave from being corrupted.

My question, though, is detecting out-of-sequence xlogs *enough*?  Are
there any crash conditions on the master which would cause the master to
reuse the same locations for different records, for example?  I don't
think so, but I'd like to be certain.

--                                  -- Josh Berkus                                    PostgreSQL Experts Inc.
                        http://www.pgexperts.com