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>
User-Agent: KMail/4.7.2 (Linux/3.1.10-1.9-desktop; KDE/4.7.2; x86_64; ; )
In-Reply-To: 
 <CALvesgkty17rD5w2ptoOmjT-iK9oT8STOHK=L6wdUSxC-cF+iQ@mail.gmail.com>
References: <1545853.rUp5XqBn04@linux-12>
	<CALvesgkty17rD5w2ptoOmjT-iK9oT8STOHK=L6wdUSxC-cF+iQ@mail.gmail.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7Bit
Content-Type: text/plain; charset="us-ascii"

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