From: John Fabiani <johnf@jfcomputer.com>
To: Steven Crandell <steven.crandell@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: syncing - between databases
Date: Sun, 13 May 2012 13:01:23 -0700
Message-ID: <2043249.nCji8px3co@linux-12> (raw)
In-Reply-To: <CALvesgkty17rD5w2ptoOmjT-iK9oT8STOHK=L6wdUSxC-cF+iQ@mail.gmail.com>
References: <1545853.rUp5XqBn04@linux-12>
<CALvesgkty17rD5w2ptoOmjT-iK9oT8STOHK=L6wdUSxC-cF+iQ@mail.gmail.com>
All on the same cluster (only one server). Although, it would be nice to
have only one table there are real business reasons to dup the databases.
I am interested in how you dealt with a queue table. Would you take a little
time to describe the way it worked.
Johnf
On Saturday, May 12, 2012 08:53:52 PM you wrote:
> Are these 5 databases on different servers and at different locations or
> are they on the same local cluster?
> If they are all on the same local cluster you may want to rethink how you
> are storing customer data. The design you describe seems redundant.
>
> If you are dealing with multiple servers (and perhaps business rules that
> require duplicate, writable user tables at each location?) then your plan
> needs to account for network failure. A synchronous cross-network dblink
> trigger mechanism left to its own devices will eventually fail and you will
> be left with inconsistent data. Nothing wrong with dblink but you need to
> build in some error handling.
>
> I've built systems that accomplished similar things by writing data to a
> queue table (in addition to your local master customer table) which is then
> reconciled/synced out to other nodes or process by an periodic script that
> is able to deal with or alert on locking/dupe key/network and other errors
> that keep it from properly syncing a row to all other nodes. This
> introduces added durability to your sync mechanism but also introduces some
> lag time. Pick your poison.
>
> -steve
>
> On Sat, May 12, 2012 at 7:28 AM, John Fabiani <johnf@jfcomputer.com> wrote:
> > I need to maintain a sync-ed table across several databases. For
> > example I have a customer table in 5 databases. If a user of any of
> > the databases inserts a new customer I need to insert the new record
> > into the other four databases. But question is updates and deletes.
> >
> > I can use a trigger and dblink to update the other databases when the
> > action
> > is an insert because in each of the other databases I don't have to
> > worry
> > about a locked record. But what happens if a user is updating at the
> > same moment as a different user in a different database is updating the
> > same customer. Can a race condition occur?
> >
> > I was thinking I could create a master database. And have all the other
> > databases use dblink to excute the master trigger.
> >
> >
> > Any advise would be helpful,
> >
> > Johnf
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: johnf@jfcomputer.com, steven.crandell@gmail.com
Subject: Re: syncing - between databases
In-Reply-To: <2043249.nCji8px3co@linux-12>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox