Skip site navigation (1) Skip section navigation (2)

Re: syncing - between databases

From: John Fabiani <johnf(at)jfcomputer(dot)com>
To: Steven Crandell <steven(dot)crandell(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: syncing - between databases
Date: 2012-05-13 20:01:23
Message-ID: 2043249.nCji8px3co@linux-12 (view raw or flat)
Thread:
Lists: pgsql-sql
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(at)jfcomputer(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql

In response to

Responses

pgsql-sql by date

Next:From: Carlos MennensDate: 2012-05-14 02:38:01
Subject: Re: Finding Max Value in a Row
Previous:From: Steven CrandellDate: 2012-05-13 03:53:52
Subject: Re: syncing - between databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group