[pgpool-general: 282] Feature Request … Master/Slave Mode Addition

Matt Wise matt at nextdoor.com
Fri Mar 23 02:50:03 JST 2012


I've been trying to figure out how to do this properly for about 2 weeks now, and as far as I can tell its simply impossible with the current model. In our environment we currently have 1 PGPool server and 4 backend DB servers. For the moment, we want to control which of the 4 backend DB servers is master entirely manually. That means that we'll handle doing failover if necessary. With this model, there are two problems..

1) If we set master_slave_mode to OFF, then PGPool is not smart enough to figure out that DB1 is our master and DB2/3/4 are slaves of it. If we set master_slave_mode to ON, any failure of any node triggers the various commands. That is, even a simple Postgres restart of DB1 will cause PGPool to try to do a failover to DB2.

2) If we set DISALLOW_TO_FAILOVER as the backend flags for all of our servers then if one out of the 4 servers goes offline, all connections to the DBs break and hang. The entire system shuts down. If we bring that node back up PGPOol automatically recognizes this and starts sending traffic again. However, if we set ALLOW_TO_FAILOVER and a node goes down, traffic keeps working for the other nodes. THe problem is, if that node comes back up (lets say it was just a postgresql restart), PGPool will never add it back into the pool automatically.

I essentially want a mode in PGPool where PGPool will monitor all of the DB servers and dynamically figure out which one is the master. It will do all of the normal healthchecks, and even catch it automatically when a new server is promoted to the master. Streaming replication healthchecks would still run as well, and update appropriately if the backend master changes. Any time a single server goes down (thats not the master), it should stop sending transactions there but continue to accept them and distribute them among the other servers. When that backend server comes back online, it should be re-added to the pool immediately.

This model also allows us to run two PGPool servers in parallel.. we can run one as a primary (that maybe has the failover commands all configured) and a second one as a backup with no failover commands configured... just there to accept connections if the first one goes offline!

—Matt



More information about the pgpool-general mailing list