I want to set up a central database and several satellite databases
which use some of the data from the central database.
For example
Say my central database contains people records, with a many to many
relationship with clients records.
Each client has their own database but needs read, write access to
their people. They will have other data of course which they will want
to join with their people table. Each client has a different business
and different requirements but they are all dealing with humans.
Initially all the databases will be on the same server in the same
postgres cluster.
I have looked into dblink but I don't think it's going to work unless
I keep a local people table and figure out a way to sync the local
table with central table using dblink queries.
The other way would be set up some sort of bidirectional replication
using an off the shelf product like bucardo or londiste. When
replicating from central to peripheral databases it would need to use
a query or a view so it doesn't replicate all the records but when
replicating back it could use the entire table. My main worry in that
scenario is that the same person record could show up in multiple
client databases and therefore can have collision problems.
Has anybody set up anything like this? Is there some other way to do
this that is cleaner?
TIA.