[Pgpool-general] pgpool II and optimisitic/lazy replication ( understanding replication mode )

[Day, David]

Hi,

I am somewhat a novice with respect to postgres 9.0.3 and pgpool II (3.1 ) so please don't make many assumptions about what I know.

I am  searching for a way of replicating a database amongst 10 independent machines that collectively form a system and should be able to tolerate an occasional machine outage or addition.   When the everything is up and running and were a failure never to occur,  it would appear to me that the statement based replication of pgool-II replication mode meets all my  needs.  Assume for a moment the each machine has a copy of postgres and pgpool.
The application software on each machine opens a local connection to it's  pgpool and the pgpool.conf file references all machines in the system.
At the moment assume I have WAL  logs for individual machine recovery, yet no other concept of masters and slaves on the backend.  ( Multi-Master ? )
I have pgpool configured for replication mode and load sharing is enabled.

My problems and/or lack of understanding is  what occurs  when one node in the pool is unavailable from pgpools-II perspective and how it tolerates table inconsistencies on insert/update/delete.

Scenario:  A machine goes down. ( 9 machines remaining )
      My impression is that replication through pgpool to the remaining systems will stop ? ( true or false )

     My impression is that it each pgpool might continue to update the node  it considers the master. ( lowest backend node_id_that is reachable ) ?
    ( in other words the other 8 remaining units would not be updated ? )  Meanwhile an automated standard recovery options  appears possible.   How that  worked was not well understood by me and/or  seemed to be limited to a two unit master /slave type data-center operation.  I was not sure this aspect of pg_pools replications mode would prove useful in my overall design and would perhaps like to disable it.

    What corrective pcp_* commands might be useful to restore statement based replication in the remaining 9 nodes.  ( pcp_detach_node ? )

    What actions would be required to bring the restored node back in to the pool ?


Scenario:  Inconsistent table data:
    My impression is when an inconsistent tuple result is returned amongst the pool of replicated databases a master is unit is selected on  the basis of  ? and that the other nodes would be considered degenerated.  ( Only the deduced master would then be written to ? )

Given that my inconsistent scenario assumption above is correct, what I would prefer to see is the concept of a new flag added to the config file called optimistic or lazy replication flag. If optimistic replication is true then  ( with pgpool II code modifications ) differences in query results would be tolerated and only a warning message generated to the pgpool log. ( perhaps also  a callout to a recovery command )   I could also see the same flag being used in the machine down scenario to keep replication going in the remaining up machines in the pool.

With tables that are really important to have true consistency, I would expect to use a tool like pg_comparator both proactively and retroactively to detect and resolve  table differences.


Summary:
In advance thanks for clarification of pgpools-II operations under these scenarios and assumptions,  and for any comments on the general idea of pg-pool and optimistic replication.

I am curious what is the English translation for hatsuiboshi ?

Thanks Much



Dave
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://pgfoundry.org/pipermail/pgpool-general/attachments/20111024/a3239d16/attachment-0001.html>