On 5 October 2011 10:05, Guillaume Lelarge <guilla...@lelarge.info> wrote: > On Wed, 2011-10-05 at 09:38 +0100, Thom Brown wrote: >> Hi, >> >> I want to be able to have a primary/standby PostgreSQL setup with >> streaming replication and using pgPool to deal with the load balancing >> and auto failover. One question I have is can I have 2 instances of >> pgPool pointing to the same database servers? > > Yes. > >> In addition to this, >> what if pgPool1 and the primary were in one data centre, pgPool2 and >> the standby were in another data centre, then connectivity went down >> between the data centres. On top of this would be a connection load >> balancer to map incoming connections to one of the pgPool instances >> (in case one of the pgPool instances dies). > > That would be a total mess :) > >> It looks like pgPool2 >> would promote the standby to a primary, then when connectivity was >> restored, there'd be a split brain issue where write queries would >> randomly hit one of the servers, making them instantly out of sync. > > Right. > >> This seems like it should be a very common setup, so does pgPool have >> provisioning for this, like having a 3rd pgPool to manage them >> somehow? >> > > No, it's not possible right now, and there's no discussion between > developers to do something like this (at least on pgpool-hackers).
So no-one has ever configured pgPool to have true HA? Isn't there any kind of black magick trickery one can do with the existing config options to achieve this? >> Also, if there was a primary and 2 standbys, and the primary goes >> down, which promotes standby1, how does standby2 then subscribe to >> standby1 automatically? And how does pgPool then know how to trigger >> failover on the next standby if the new primary fails? >> > > pgpool doesn't know which one to promote. It just calls a script when a > failover is needed. Now, you can have a "smart" script that will try to > understand which slave is the most uptodate, and promote only this one. > Keep in mind that the script is really executed when a node is detached, > meaning every node, even slaves one. So your script needs to check if > the detached node is the master one. > > In your example, for standby2 to follow standby1 (if standby1 is the new > master), pgpool executes a script (see follow_master_command option). Ah okay, I'll look into that. >> And when I set up pgPool today with a primary and standby using >> streaming replication, queries only seemed to hit the primary. Any >> idea what I could have missed? > > Setting load balancing? and having enough clients hitting your database? > >> Aren't SELECT queries supposed to be >> randomly sent to one of the servers? I ran queries several times and >> never did any hit the standby. >> > > You almost surely did this with one or two clients only, which won't > work. Use pgbench with 10 or more clients. Yes, you're right. I had 3 clients connecting, mainly calling pg_sleep so I could monitor it in pg_stat_activity on both servers. >> Just to note, the config changes were something like this: >> >> failover_command = '<command to put trigger file on 1st standby' >> backend_* = <these ones are all commented out as I'm only using pgPool >> for load balancing> >> replication_mode = false >> load_balance_mode = true >> replicate_select = false >> backend_hostname0/port0... = <this was set to the primary> >> backend_hostname1/port1... = <this was set to the first standby> >> > > So you set the load balancing mode. I guess you didn't have enough > clients. At least you didn't spot anything horribly wrong with these options. So in load balancing mode, is backend_*0 regarded by pgPool to be the primary, or is pgPool clever and polls the servers to figure this out? Thanks Guillaume. Thom _______________________________________________ Pgpool-general mailing list Pgpool-general@pgfoundry.org http://pgfoundry.org/mailman/listinfo/pgpool-general