[Pgpool-general] replication changes for support of a multi-mastering. ( ignore tuple mismatch and zero config )

[Day, David]

> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at sraoss.co.jp]
> Sent: Saturday, November 05, 2011 8:09 PM
> To: Day, David
> Cc: pgpool-general at pgfoundry.org
> 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
> >
> >
> >
> >
> >
> >
> >
> >