Обсуждение: LISTEN/NOTIFY for lightweight replication
Hi, I'm trying to come up with a relatively simple multi-master replication solution. This is for multiple databases that need to be discreet, and change relatively infrequently (10-30 updates an hour), and almost never update each others data (less than once a day). The TCL-based replication project for multi-master is troublesome to configure and seems to really impact performance. It can be assumed that the master-slave setup will not work for me, nor do we want to purchase a commercial soluton, nor can we run this all from one central database. I'm considering the following, and am requesting advice and any suggestions: a. Use listen/notify to develop a notification when changes are made. This could be done between each node, or not (i.e. it could be a chain instead). b1. All of the add events are using sequences so each node has a unique set of new records. b2. When an add, update or delete is recorded, DDL of this is passed via the notify. c. If no local event happened prior during this event envelope (i.e. since the last update notification but before the new event completed), perform the event. d. If there is a record level conflict but no field level one, perform the event. e. If there is a field level conflict, raise an exception (TBD). There are plenty of things that might not work here, but I'm particularly interested in: 1. If this is brain-dead because of performance issues, I'd like to know upfront. 2. Is there a way to get at the system tables that would contain overall change events? Otherwise, is this information available in some log event? 3. Can this be readily translated to DDL? 4. Does anyone have some extended examples of using listen/notify, especially in any kind of distributed transaction capability? Thanks! __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail
Ted Shab wrote: > Hi, > > I'm trying to come up with a relatively simple > multi-master replication solution. This is for > multiple databases that need to be discreet, and > change relatively infrequently (10-30 updates an > hour), and almost never update each others data (less > than once a day). > > The TCL-based replication project for multi-master is > troublesome to configure and seems to really impact > performance. It can be assumed that the master-slave > setup will not work for me, nor do we want to purchase > a commercial soluton, nor can we run this all from one > central database. > e. If there is a field level conflict, raise an > exception (TBD). Exception handling and failure recovery are what makes for all the work in replication. I don't think a pure listen/notify setup will be enough because iirc the system doesn't guarantee delivery of multiple notifications if >1 are queued. Have you looked into the possibility of using dblink to handle updates of each others' data? That would mean your problem reverting to one of single-master replication. -- Richard Huxton Archonet Ltd
Richard, Thanks for the response. I'll look into both the dblink and iirc. Do you know of any extended examples of either? --Ted --- Richard Huxton <dev@archonet.com> wrote: > Ted Shab wrote: > > Hi, > > > > I'm trying to come up with a relatively simple > > multi-master replication solution. This is for > > multiple databases that need to be discreet, and > > change relatively infrequently (10-30 updates an > > hour), and almost never update each others data > (less > > than once a day). > > > > The TCL-based replication project for multi-master > is > > troublesome to configure and seems to really > impact > > performance. It can be assumed that the > master-slave > > setup will not work for me, nor do we want to > purchase > > a commercial soluton, nor can we run this all from > one > > central database. > > > e. If there is a field level conflict, raise an > > exception (TBD). > > Exception handling and failure recovery are what > makes for all the work > in replication. > > I don't think a pure listen/notify setup will be > enough because iirc the > system doesn't guarantee delivery of multiple > notifications if >1 are > queued. > > Have you looked into the possibility of using dblink > to handle updates > of each others' data? That would mean your problem > reverting to one of > single-master replication. > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match > _______________________________ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com
On Wed, Oct 13, 2004 at 08:32:04AM -0700, Ted Shab wrote: > Thanks for the response. > > I'll look into both the dblink and iirc. That's actually only dblink. IIRC is an acronym, meaning "if I recall correctly", IIRC. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "The West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do." (Samuel P. Huntington)
Ted Shab wrote: > Richard, > > Thanks for the response. > > I'll look into both the dblink and iirc. > > Do you know of any extended examples of either? dblink is in the contrib/ folder of the source distribution and possibly your packaged version if you use such a thing. Never needed it myself, but the documentation looks clear enough. As for listen/notify possibly dropping duplicate notifications... Ah! it's in the "SQL COMMANDS" reference part of the manuals NOTIFY behaves like Unix signals in one important respect: if the same condition name is signaled multiple times in quick succession, recipients may get only one notify event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifies received. Instead, use NOTIFY to wake up applications that need to pay attention to something, and use a database object (such as a sequence) to keep track of what happened or how many times it happened. -- Richard Huxton Archonet Ltd
Thanks. I was thinking iirc was the transport protocol :-) Looks like dblink is the best bet here. --Ted --- Richard Huxton <dev@archonet.com> wrote: > Ted Shab wrote: > > Richard, > > > > Thanks for the response. > > > > I'll look into both the dblink and iirc. > > > > Do you know of any extended examples of either? > > dblink is in the contrib/ folder of the source > distribution and possibly > your packaged version if you use such a thing. Never > needed it myself, > but the documentation looks clear enough. > > As for listen/notify possibly dropping duplicate > notifications... Ah! > it's in the "SQL COMMANDS" reference part of the > manuals > > NOTIFY behaves like Unix signals in one important > respect: if the same > condition name is signaled multiple times in quick > succession, > recipients may get only one notify event for several > executions of > NOTIFY. So it is a bad idea to depend on the number > of notifies > received. Instead, use NOTIFY to wake up > applications that need to pay > attention to something, and use a database object > (such as a sequence) > to keep track of what happened or how many times it > happened. > > -- > Richard Huxton > Archonet Ltd > _______________________________ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com