I am doing research on database architectures, and I am looking for which software can meet my requirements.
To try to explain the requirements simply:
You have a master database server. This may make a one or a few identical replicates for distance purposes - such as one for US and another for Europe. These masters would have identical clones locally for failover.
This master then does one-way slave propagation to many read-only servers via VPN. These servers serve-up all data reads to a LAN at that server's location, serving a few hundred clients at each location. Ideally, the read-only slave DB could mirror locally for failover.
All writes are made to the master server(s) and then make their way back to the slaves. The application this architecture serves is far more read intensive than write.
The slaves require intelligent granularity. The replication must not only be table-specific per site, but also must be row-specific in some tables. For example, the master server would have a monolithic client table with all clients across all sites. The client table should replicate in part to the slaves, containing the same columns however only having those clients that have purchased from that local branch. We do not want the local branch to have access to all clients.
Though relatively novice in database administration, I have done a bit of research thus far. It seems Postgresql is quite a positive choice for a database, and the closest replication middleware I can find to fit these requirements is PgPool-II. I do not, however, know if it will meet all of these requirements easily and in a way that will scale.
What, if any, major database such as PostgreSQL or MSSQL, is capable of satisfying the above requirements? Does it do this natively? And if not natively, what middleware for that database is capable of handling it?