[Pgpool-general] Zimbra Detected SpamPgPool replication or Slony

[Tatsuo Ishii]

> 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