How to synchronize database between server (PostgreSQL) and client (SQLite)?

Question

PostgreSQL

Interested in one-way synchronization (replication) from the server to the client. The PostgreSQL database is running on the server, and SQLite is running on the client (Android). What is the best way to sync them? Create a backup of the database on the server, translate it into SQLite format and simply transfer it to the client as a database file? Or is there a more elegant way? If the database is large, then "too fat" every time to transfer it all

Answer 1

Search here.
https://www.google.com/search?q=postgresql+sqlite+...
If purely by hand. Then you need to do the following, it doesn’t matter through a web service or through sqldump
1. Transfer all dictionaries (1 time)
2. Transfer all data or a subset (1 time)
3. The process of synchronizing the client and server (many times)
So step 1 in this At the moment the data exchange scheme is formed and fixed, it is important to take into account changes in the database in the future. The transfer occurs to the client either by hand or with the help of a service.

GET /api/dbsync/getdictionaries/
{
"schema": "0.4",
[ 
{ "name":"cityes", "sql": "create table ....."},
.....
]
}

where schema and start date are passed
GET /api/dbsync/getdata/0.4/31.05.2020
This query returns a subset of data or just a list of tables to be created.
Pay attention to the primary keys, it is desirable that they be of the UUID or GUID type
, or that the tables contain service fields that allow you to identify the user.
In addition, the created, updated fields are required.
It is also important that the data be given specifically for this user. A typical example, the users table will contain only him, and possibly a list of his subordinates, and the customer table will contain only his customers
point 3
GET /api/dbsync/sync/0.4/31.05.2020
receiving only delta data from the server for the specified period
POST /api/dbsync /sync/0.4
the client logic forms a package of changes, the server itself understands what to commit, and what data to swear at, they say it has already been fixed, what changes to leave.
The response is a list of statuses for each entry.
Here is an example simple circuit.

Answer 2

This is a rather complex topic.
Usually they do it so that the first time they take the entire database, and then only the changes, so as not to download a huge database every time.