> -----Original Message----- > From: Tatsuo Ishii [mailto:[email protected]] > Sent: Saturday, November 05, 2011 8:09 PM > To: Day, David > Cc: [email protected] > Subject: Re: [Pgpool-general] replication changes for support of a mut- > mastering. ( ignore tuple mismatch and zero config ) > > > Hi, > > > > I am a bit new to Postgres(9.03) and PGPOOL (3.1. After a bit of > experimentation with these components, > > I am considering making some changes to PGPOOL and would appreciate > any comments from the pgpool > > community. > > > > Given: > > A node could consist of application software and optionally PGPOOL > and/or POSTGRES. The application on startup > > opens a "permanent" database session. The database has decision > making tables where consistency is desired but > > not necessarily vital. The other area of the database is for logging > where consistency would not be important at all. > > E.g A report could be created from the sum of reports of all > nodes log tables. > > Eventual consistency of important tables would be achieved through > audit methods > > outside of PGPOOL though perhaps triggered by PGPOOL reporting tuple > inconsistency. > > > > > > Therefore 2 Changes I am considering. > > > > First : Change degeneration of node on TUPLE mismatch: > > I want to have a new flag and/or per command over-ride that commits > write updates despite finding tuple > > differences between the nodes, and without degenerating the node > status. Degeneration for replication > > would only occur if the connection to the backend becomes > unavailable. > > I'm not sure I fully understand you requirement but it seems > "failover_if_affected_tuples_mismatch" does the trick for you. By > turning it to off, inconsistent update/insert/delete do not cause > degeneration.
My experiment showed that yes it did not generate the node. However neither did it commit the write to any node in my two node experiment. I prefer that it go ahead and make the changes and then generate a Warning or call out to a routine that might do some conflict resolution. > > > Second: Determination of backend nodes dynamically through zero > configuration (Avahi ) rather then pg_pool.conf > > It assumes postgres installed with a zeroconfig avahi patch which > announces itself. > > > > pgpool.conf would have a new variables added. > > zeroconf_browse_service_type = _postgresql._tcp > > zeroconf_browse_service_name = some qualifying name from the real > postgres server(s). > > zeroconf_publish_service_type =_ postgresql_._tcp > > zeroconf_publish_service_name = some name that is important to the > application software zeroconf browser. > > > > Pgpool on starting up with zeroconf service enabled would immediately > publish itself while awaiting client connection. > > It would simultaneously browse for service type and service name as > declared. ( the real postgres(s)). On discovering > > candidates, it would add them to a dynamic growing and shrinking > backend list. pcp_attach_node and pcp_remove_node > > would be used to grow/shrink the backend pool upon discovery/loss of > the browsed service type. > > When a actual connection request comes to pgpool it would connect > that request to all current backends and > > any backend s that are subsequently discovered with matching browse > criteria. > > I'm not familiar with Avahi and again I'm not sure I fully understand > your requirement but... So you are suggesting that instead of getting > hostname# info from pgpool.conf, getting it from Avahi? If so, some > questions come to my mind: > > 1) How do you get weight etc info other than hostname info from Avahi? > I was considering building the weighting factor into text records associated With the mDNS cache of the published service. The "proposed pgpool service type/name browser" would parse the text record component to find the weight. > 2) If you configure pgpool with streaming replication, just adding > live PostgreSQL will break data consistency among clusters. Can I > assume that someone other than pgpool (Avahi?) could take care of > that? Apologies, I agree with your statement and I was unclear in my laying out my thoughts. I am thinking I don't need streaming replication at all. I do need WAL logs on a per node basis simply for restoration/recovery if I were only a single node system. I which case I would not use pgool in that site execution. However, the general premise was that pgpools statement based replication would provide an acceptable level of consistency to a multi-node pool. Therefore, before a new or failed node would re-appear on the network it would require restoration/creation from any snapshot of a healthy nodes. Then background audits ( pg_comparator ) would eventually bring slightly out of line nodes/tables to a consistent state. > -- > Tatsuo Ishii > SRA OSS, Inc. Japan > English: http://www.sraoss.co.jp/index_en.php > Japanese: http://www.sraoss.co.jp > > > That's my thoughts in a nutshell. > > It certainly stretches the notion of multi-mastering as consistency > requirements are relaxed. > > Hopefully someone that is more familiar with the current internals > could comment on the potential scope of change, > > the general usefulness, or suggest some other tools that might be > more easily adapted, > > and/or that I have not yet fully understood how PGPOOL works :+) > > > > > > > > Thanks > > > > > > Dave > > > > > > > > > > > > > > > > _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
