9

I am trying to create an app that receives an Sqlite database from a server for offline use but cloud synchronization. The server has a postgres database with information from many clients.

1) Is it better to delete the sql database and create a new one from a query, or try to synchronize and update the existing separate sqlite files (or another better solution). The refreshes will be a few times a day per client.

2) if it is the latter, could you give me any leads to resources on how I could do this?

I am pretty new to database applications so please excuse my ignorance and let me know if there is any way I could clarify.

Improve this question
3
  • please show what you have tried.. Commented Aug 9, 2013 at 16:44
  • Hi popeye, I am considering different approaches but have not tried implementing any particular one yet. The one thing that I am thinking of is using pgdump and trying to dump the results of a postgres query. if I do that, I could either dump the whole set of tables for the 'client'. the other option is that I could try to check the last update date for the sqlite file and constrain my postgres query based on that. From that file, I could then somehow individually send the update and set requests to the sqlite file to update the sqlite file with the lastest data from the postgres database. Commented Aug 13, 2013 at 14:21
  • @ThinkBonobo You have a great deal of reading to do. It's really not that simple. There is a great deal of literature on disconnected sync, and nobody in twenty+ years has come up with a simple and easy answer that doesn't involve design compromises in apps or lots of complexity. You really need to study this topic. Commented Nov 9, 2013 at 14:57

2 Answers 2

Reset to default
4

There is no one size fits all approach here. You need to carefully consider exactly what needs to be done, what you are replicating, how much data is involved, and what your write models are, all before you build a solution. Along the way you have to decide how to handle write conflicts and more.

In general the one thing I would say is that such synchronization works best with append-only write models (i.e. inserts, no deletes, no updates), and one way to do it is to log changes that need to be made and replicate those changes.

However, master-master replication is difficult on the best of days and with the best of tools available. Jumping between databases with very different capabilities will introduce a number of additional problems. You are in for a big job.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

If it is not append only, your next-best bet is to design your app to be tolerant of a last-update-wins design. This is harder than you probably think; it means you can never do a read-update-write cycle.
It's been a while since the question and your answer and in between I discovered that you are all too right. In the end I decided to treat it almost like a separate application (which made sense, afterall the sqlite database was for a specific usage). We basically worked as if we were creating a new database and I used python to query the postgres database for the information I need it and feed it into the sqlite database table by table making the proper adjustments and putting them in python model objects in between. Thank you for your answer!
A separate application I think is often the most sane way.
4

Here's an open source product that claims to solve this for many database types including Postgres. I have no affiliation or commercial interest in this company.

If you're able and willing to step outside relational databases to use an object store you might want to have a look at CouchDb and perhaps PouchDb that use a MVCC based replication protocol designed to support multi-master replication including conflict resolution. Under the covers, PouchDb uses adaptors for Sqlite, IndexDb, Local storage or a remote CouchBb instance to persist client side data. It auto selects the best client side storage option for the given desktop or mobile browser. The Sqlite engine can be either WebSQL or a Cordova Sqlite plugin.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.