I've got two servers set up with PG Pool to create a HA setup for a webapp.
PGPool and postgres run on both servers, using streaming replication from server 1 to server 2. The webapp on each machine connects to PgPool which then sends the request to the current master. It's set up to automatically failover should a database connection be interrupted, which runs a custom failover script to demote server 1 to slave and promote server 2 to master.
What happened this morning is that for 2 minutes the network went down, which means neither PGPool instance could talk to each other - so each PGPool thought the other machine was down.
Server 1 - Continued on as master, disconnecting server 2
Server 2 - Initated failover, disconnecting server 1 and making itself the master
Since the network was down the failover command couldn't get through to server 1 to make it the slave, and visa versa. So when the network came back up 2 minutes later what I had was two servers who both thought they were the master.
PgPool doesn't seem to have an automatic failback command, which could be used to force server 1 to become the master again when the network reconnects, which is the only real solution I can think of.
My question is how am I supposed to deal with this situation? Is this even the correct architecture for this setup? Surely this is a common scenario, I can't get my head around how this kind of this could be fixed.
Edit: Would it be advisable to have pgpool run under a virtual ip under linux-ha ? That could solve things, and I already have that up and running for the public IP - that way only one pgpool instance gets accessed by either machine.
First, I think that pgpool2 does have a failback command, but this wouldn't help you much in that case. The problem is that chaos will result if both machines think they are the master. What's more, here you had a simple case: network went down. What if the network is partitioned? I.e., both machines are connected, but they somehow lose connectivity to each other. In that case, both machines will become the master, and they will serve different clients, and you will have a forked database. It's a rarer case, but are you certain it's so unlikely that you are prepared to risk the resulting chaos?
An alternative would be this:
In that case, however, you have a single point of failure, pgpool, which you probably don't want. I know only two ways to address this problem. The easiest is to only promote a standby to master manually, and this is applicable with your architecture. Your applications will need to go to read-only mode until human intervention.
The second way is to have quorums. One architecture that could work is this:
The three pgpools are running on three different machines, each with its own IP address, but they also provide an additional failover IP address, taken only by the active machine, and it is the one used by clients. If the active pgpool fails, a standby pgpool takes it over. This can be accomplished with
heartbeat.In order to promote a hot standby to master, a quorum of pgpools (i.e. at least two of the three) must decide so; and they will implement the decision only after a delay of, say, 10 seconds after they decide. In addition, the active pgpool may not continue to use the existing master db for more than 10 seconds without getting confirmation from at least another pgpool (this is to guard against the case that the two standby pgpools lose their connection to the active pgpool and to the master at the same time, promote a hot standby to master, but the active pgpool continues to use the old master).
Actually the third pgpool need not participate in the failover IP, and just be there in order to help the quorum. In addition, I don't know if pgpool has enough features to do this. Maybe you need another daemon. A more general architecture is this:
In this case the load balancing done by pgpool is separated from the monitoring and promotion of standby to a master. Note that you can put pgpools, database servers, and monitoring daemons on the same machine, but the two pgpools must be on two different machines and the three monitoring daemons must be on three different machines. Note that I don't know whether a ready-made monitoring daemon with all necessary features exist.
The details can be changed, but I think that if you do automatic standby promotion to master without using a quorum, you are asking for trouble.
Disclaimer: I've not used pgpool, though I'm aware of what it does.
In clustering software you generally don't want any automated operations which could violate concurrency rules (e.g. something should only be online in one place) to take place when the cluster has knowledge of the status of less than half of the nodes. This prevents a split-brain condition, such as you've experienced. In a two-node cluster this would mean that in the event of loss of network interconnectivity between the two nodes, no automated failover should occur. A human being should make the decision to failover, having verified that it's the right action to take, depending upon the "other" node being offline, or accepting that there may be a loss of unreplicated transactions. I don't know whether this is something you can configure in pgpool.
If your servers are in the same LAN (low network latency to access each other), you can consider running pgpool in replication multi-master mode. You will have a little overhead imposed by the need to update two databases simultaneously.
In case of one system failure, pgpool can continue providing access through the active node. After system recovery, you can run online recovery in pgpool to bring the other node up.