Re: syncing - between databases - Mailing list 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@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