I have a Django application which uses PostgreSQL as a database.
The application stores hierarchical data. It is like a warehouse management system with multiple branches and hierarchy.
The current version is deployed on one main server and every other warehouse communicates directly with that server.
We want to add additional warehouses, but they are not well connected to internet (bad signal, bad channels, slow) so we are going to deploy application locally and synchronize the data with the main server in the background using database tools.
We don't want to rewrite the whole application and tables with additional "is_synchronized" columns.
Main
- Branch A (connects to Main directly via Web UI)
--- Branch A.1 (fine)
--- Branch A.2 (fine)
...
- Branch B (slow connection to Main) deploy locally
--- Branch B.1 (slow -> B) deploy locally
--- Branch B.2 (slow -> B) deploy locally
B.1 should synchronize the data to B's server and B should see all the data from their remote B.1, B.2 branches eventually. The same should happen with the Main server, it should see all the data from A and B branches eventually and same with their child branches.
In summary every parent should see every child's data (in case of a slow connection, eventually is fine)
How can we achieve this? What should we read? (we are total beginners in DB technologies, only web guys)
UPDATE: The child branches don't get data from parent. The child stores its own data and sends data to parent, parent stores all child data and its own. Main stores everything from everywhere
Best Answer
From your description, it sounds like you need something like Bucardo to try and merge data from multiple sources into one.
Though you'll probably have to write some custom conflict handlers to deal with data that conflicts across B, B1, and B2. There are examples out there like this for custom conflict resolution:
http://blog.endpoint.com/2014/07/postgresql-conflict-handling-with.html
Londiste also lets you merge data from multiple tables into one:
http://skytools.projects.pgfoundry.org/skytools-3.0/doc/howto/londiste3_merge_howto.html
If you can just replicate many child tables from different databases to a single database, and have your application look across all of the tables for the consistent view of your data, then you might be able to get away with something as simple as Mimeo on github from OmniTI Labs.
Without further detail on your requirements, I can only make broad suggestions.
Hope that helps.