Re: syncing - between databases

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
>