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