4

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

2

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.

  • Thanks @Kassandry, one quick question, if I choose to use Bucardo or similar tech, can they also correctly replicate foreign keys? lets say user created roomX in B2 and pk = 1 in local db, then he put some product there pk = 1, room_id = 1, when we replicate data, will it be changed accordingly in central server (product: pk=324, room_id = 543, room_pk=543) then propogate to B2 back again? – user1318496 Jul 30 '15 at 8:39
  • @user1318496 I don't know if they can do that off the shelf. I am fairly certain you'd have to write some custom conflict handlers and/or triggers to do what you're proposing, to properly encode your business logic. The best place to ask though, would be at the specific mailing lists, e.g. bucardo-general@bucardo.org – Kassandry Aug 3 '15 at 20:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.