Re: Synchronous Standalone Master Redoux

Hi all,

Here are some (slightly too long) thoughts about this.

Shaun Thomas skrev 2012-07-12 22:40:
> On 07/12/2012 12:02 PM, Bruce Momjian wrote:
>
>> Well, the problem also exists if add it as an internal database
>> feature --- how long do we wait to consider the standby dead, how do
>> we inform administrators, etc.
>
> True. Though if there is no secondary connected, either because it's not
> there yet, or because it disconnected, that's an easy check. It's the
> network lag/stall detection that's tricky.

It is indeed tricky to detect this. If you don't get an (immediate) 
reply from the secondary (and you never do!), then all you can do is 
wait and *eventually* (after how long? 250ms? 10s?) assume that there is 
no connection between them. The conclusion may very well be wrong 
sometimes. A second problem is that we still don't know if this is 
caused by some kind of network problems or if it's caused by the 
secondary not running. It's perfectly possible that both servers are 
working, but just can't communicate at the moment.

The thing is that what we do next (at least if our data is important and 
why otherwise use synchronous replication of any kind...) depends on 
what *did* happen. Assume that we have two database servers. At any time 
we need at most one primary database to be running. Without that 
requirement our data can get messed up completely... If HA is important 
to us, we may choose to do a failover to the secondary (and live without 
replication for the moment) if the primary fails. With synchronous 
repliction, we can do this without losing any data. If the secondary 
also dies, then we do lose data (and we'll know it!), but it might be an 
acceptable risk. If the secondary isn't permanently damaged, then we 
might even be able to get the data back after some down time. Ok, so 
that's one way to reconfigure the database servers on a failure. If the 
secondary fails instead, then we can do similarly and remove it from the 
"cluster" (or in other words, disable synchronous replication to the 
secondary). Again, we don't lose any data by doing this. We're taking a 
certain risk, however. We can't safely do a failover to the secondary 
anymore... So if the primary fails now, then the only way not to lose 
data is to hope that we can get it back from the failed machine (the 
failure may be temporary).

There's also the third possibility, of course, that the two servers are 
both up and running, but they can't communicate over the network at the 
moment (this is, by the way, a difference from RAID, I guess). What do 
we do then? Well, we still need at most one primary database server. 
We'll have to (somehow, which doesn't matter as much) decide which 
database to keep and consider the other one "down". Then we can just do 
as above (with all the same implications!). Is it always a good idea to 
keep the primary? No! What if you (as a stupid example) pull the network 
cable from the primary (or maybe turn off a switch so that it's isolated 
from most of the network)? In that case you probably want the secondary 
to take over instead. At least if you value service availability. At 
this point we can still do a safe failover too.

My point here is that if HA is important to you, then you may very well 
want to disable synchronous replication on a failure to avoid down time, 
but this has to be integrated with your overall failover / cluster 
management solution. Just having the primary automatically disable 
synchronous replication doesn't seem overly useful to me... If you're 
using synchronous replication to begin with, you probably want to *know* 
if you may have lost data or not. Otherwise, you will have to assume 
that you did and then you could frankly have been running async 
replication all along. If you do integrate it with your failover 
solution, then you can keep track of when it's safe to do a failover and 
when it's not, however, and decide how to handle each case.

How you decide what to do with the servers on failures isn't that 
important here, really. You can probably run e.g. Pacemaker on 3+ 
machines and have it check for quorums to accomplish this. That's a good 
approach at least. You can still have only 2 database servers (for cost 
reasons), if you want. PostgreSQL could have all this built-in, but I 
don't think it sounds overly useful to only be able to disable 
synchronous replication on the primary after a timeout. Then you can 
never safely do a failover to the secondary, because you can't be sure 
synchronous replication was active on the failed primary...

Regards,
Hampus