> Pascal Cohen wrote: > > Hello all > > I am new to PgPool (and also Slony). > > I am currently studying the way to ensure more availability but also > > load balancing to reduce the load on a single DB whenever we would need it. > > PgPool-II is very nice and is running well in the tests I did and the > > way to integrate it with PgPool-HA seems nice. > > But I am concerned with the replication between several DBs. > > I can handle that in two ways: > > First use PgPool only as a load balancer and rely on Slony for > > replication or use PgPool as load balancer and replicator. > > I would like to know what are the drawbacks and the advantages of each > > solution.
One drawback using pgpool as a load balancer and let slony-I do the replication is, in the case when master goes down. In this case pgpool detaches the master. Then queries including updates will be sent to the slave and slony-I will rejects any query other than SELECTs. > > I would like also to get details on the PgPool replication behavior. > > The simplest is probably a dummy example. > > Let's say we have a master DB containing a table A and a transaction > > updates one row in A. Table A in S is synchronized with M. > > Let's say then that I have two connections that get the updated row in A. > > > > Using PgPool as load balancer between a Master M and a Slave S. I can > > have the following situation (not necessary much probable but > > technically possible): > > Update of A in M. > > First request on A goes on M. > > Second request on A goes to S. > > Slony performs synchro between M and S. > > In that case my two requests may return different results. Yes, this could happen. > > Can that situation occurs also with PgPool replication like: > > PgPool sends update of A in M . > > First request on A goes on M. > > Second request on A goes to S. > > PgPool sends update to S. This could happen too, though the window is very narrow comparing with Slony-I. As of pgpool-II 2.0, any query is executed as an explicit transaction. Let's say you have a master and two slaves and you send an update query to pgpool-II. M: start a transaction S1: start a transaction S2: start a transaction M: do update S1: do update S2: do update S2: commit the transaction --(1) S1: commit the transaction --(2) M: commit the transaction --(3) So the window is between (1) and (3). In most cases duration of commit is small, comparing with the update query itself. > > My main concern is in fact that there can be transitional state where we > > can get different data depending on the targeted DB choosing PgPool > > replication or Slony replication. Also note that in pgpool, if you send an update then a select to retrieve the result in a *same session*, it's guaranteed that the select returns the result of update since the update and select will be executed on a same PostgreSQL node even if the load balanace option is enabled. -- Tatsuo Ishii SRA OSS, Inc. Japan _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
