What is the best way to synchronize with the server with an unstable Internet?

Question

PostgreSQL

There are 40-50 devices, each of which generates 3-7k records per day.
You need to save all this data in a database on the server, but devices do not always have a stable Internet.
At the same time, the device needs about 1k records from the server to work (they can be added and changed from time to time).
I want to use postgresql on the server. On devices, you can also use postgresql or something simpler (sqlite) and synchronize them when the Internet appears. At the same time, the generated data after synchronization does not need to be stored on devices.
What is the best way to implement this and what can be read on this topic?

Answer 1

Apache CouchDB allows you to automatically synchronize between local client instances and a server instance. Also allows you to work offline between exchange sessions.
After getting more information, I can offer another solution.
Store the date of the last client synchronization session on the server.
The client must store a table with data in columns:
As soon as the time has come to send the data, we check whether the data has accumulated in the table. We select the X first records (let's say 1000) and asynchronously try to send them to the server. If successful, then delete records with the selected IDs from the table. If not, then at the next attempt, according to the timer, we send the data with the accumulated changes.
In another thread, as soon as new data from the sensors has appeared, we execute INSERT into the table.

Answer 2

An unstable Internet can be different, but in the general case it is solved by a layer between clients and the database (it’s a common thing, and for the sake of increasing security, the database is not opened to the world), the time is now that the best solution is http / https. In this case, the database can be absolutely any, even a text log, even oracle.
At the same time, if a layer is used, you can organize replication and synchronization with several of your servers on your own, sometimes it is even more convenient and easier than using regular database replication, especially if the Internet is unstable. Those. when receiving data from devices, you send two / many requests to your servers (asynchronously by itself) and wait for the allotted timeout (by which you disconnect the failed server from the list and issue an alert) and then answer ok. When the server is returned to service, data from the workers is uploaded to it (log or field over time, if the data is not deleted).