[Pgpool-general] Replication + Recovery with 2 pgpool + 2 backends

[Maxence DUNNEWIND]

Hi,

now my "simple" install seems to work pretty well :
1 apache/zope
1 pgpool
2 backends


Anyway, to set up a full cluster solution, I have to put 2 apache/zope
and 2 pgpool !
zope1 ==> pgpool1 ---> postgresql1
                  \ /
                   X
                  / \
zope2 ==> pgpool2 ---> postgresql2

For both pgpool, postgresql1 is node0 and postgresql2 is node1

Pgpool1 is on the same host than postgresql1, and pgpool2 is on the same
than postgresql2

If a backend fails, I run my own monitoring script which can do 2
things :
* If the script is currently on the master, it checks backend and
call pgp_recovery_node when backend is up
* If the script is currently on a slave, it waits for a /tmp/somefile to
  be created, and then pcp_attach_node the backend

Of course, the /tmp/somefile is created by the master when it finished
to recover the backend.

So, if postgresql2 fails, we have :

1) both pgpool1 and pgpool2 detects it and starts the monitoring script,
pgpool1 is the master, so the script will check postgresql1 peridically.
Pgpool2 is the slave, so it will wait for /tmp/somefile to be created

2) When postgresql2 comes back, the monitor of master will detect it and
call pgp_recovery_node on pgpool1. Pgpool recover the backend and reattach
it. Pgpool then call the failback command

3) The failback command create /tmp/somefile on the slave mode (aka :
pgpool2 host)

4) slave monitor detects it, and call pcp_attach_node on pgpool2

5) All should be ok :)

The problem is : Imagine that after the end of recovery (ie, end of step
2), pgpool2 receive request from its zope. It will exec them on
postgresql1 (since there is no /tmp/somefile).
Then, the failback command is executzed on host1, and so the
/tmp/somefile is created on pgpool2 host. The monitor detects it and
reattach postgresql2. 

The problem is that some queries will be performed on postgresql1 by
pgpool2 before postgresql2 is reattached ... With the previous exemple,
both backends don't have the same data when postgresql2 is reattached to
pgpool2.


My question is "simple", how can I do to avoid having this problem ?

Maxence

-- 
Maxence DUNNEWIND
Contact : maxence at dunnewind.net
Site : http://www.dunnewind.net
02 23 20 35 36
06 32 39 39 93

S'il vous plaît, évitez de m'envoyer des pièces jointes au format Word ou PowerPoint.
Voir http://www.gnu.org/philosophy/no-word-attachments.fr.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://pgfoundry.org/pipermail/pgpool-general/attachments/20080718/a79c17a2/attachment.bin