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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Ted Shab wrote:
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

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 (AT) archonet (DOT) com> wrote:




_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Wed, Oct 13, 2004 at 08:32:04AM -0700, Ted Shab wrote:

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)


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Ted Shab wrote:
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

---------------------------(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

Thanks. I was thinking iirc was the transport
protocol :-)

Looks like dblink is the best bet here.

--Ted
--- Richard Huxton <dev (AT) archonet (DOT) com> wrote:




_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org