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
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
