Re: syncing - between databases

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