By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,712 Members | 1,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,712 IT Pros & Developers. It's quick & easy.

LISTEN/NOTIFY for lightweight replication

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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 <de*@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

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

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

Nov 23 '05 #3

P: n/a
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)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4

P: n/a
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

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

Nov 23 '05 #5

P: n/a
Thanks. I was thinking iirc was the transport
protocol :-)

Looks like dblink is the best bet here.

--Ted
--- Richard Huxton <de*@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

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

http://archives.postgresql.org

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.