I need to replicate certain sets of data from a master database site (head office) to multiple sites geographically distributed.
There is no specific software chosen yet for head office or the remote sites.
- The remote sites should be able to create new data and this new data should be synced to head office and the other way around.
- The remote sites should contain only sub sets of data from the master and some sites will need to have different sets than other sites.
- We need transactions
- There is very poor connectivity between H/O and some remote sites. So I would like to only retrieved changed data when I refresh the data on the sites.
- There are approximately 5000 remote sites.
- The initial load to populate a slave is approximately 1 mil records. (lookup data) This data can be changed only at the master site (head office).
- Daily sales data will be generated by all nodes and needs to be replicated to the one master site.
- Two remote sites will not modify the same record – all data generated by remote sites is newly created and uploaded to the master. Head office is the only site that should change the lookup data so remote sites will have read-only access to this data.
We are currently testing on Postgres at the remote sites and using database links with materialized views to retrieve data from head office.
We also tested the head office system with Oracle and Postgresql.
I know Oracle uses the materialized view log to track changed data for fast refreshes.