LISTEN/NOTIFY for lightweight replication

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