LizSync
A QGIS plugin for PostgreSQL synchronisation between a central database & clones
Michaël Douchin - 3liz
## 3liz
* **QGIS** and **PostgreSQL** lovers
* QGIS **core** contributors (mainly server)
* 8 employees
* QGIS / QGIS Server / PostgreSQL / Lizmap
* **Open Source** forever
* Development, server hosting, consulting, support, training
## 
## Presentation
**LizSync** is a set of tools allowing to perform PostgreSQL **database synchronisation** between a **central** database and one or many **clone** databases.
Two-way synchronisation is done:
* for **data of tables** in chosen schemas,
* between tables having **the same structure**.
* No synchronisation is made on structure changes (adding a column, creating or droping tables, etc.).
### 
## LizSync workflow
* For the 1st time
* **install** the needed PostgreSQL **structure** in the central database
* **prepare the central database**:
- add an **uid column** to every synchronized table
- add needed **audit triggers**
* Before each campain / when structure has changed
* **create an archive** from the central database with data from chosen schemas
* **deploy** it to one or many clones
* Whenever needed
* **perform** a two-way synchronisation from the clone
## PostgreSQL structure
LizSync uses **2 dedicated schemas**:
* **audit**
- in charge of recording every actions made on tables: **inserts, updates and deletes.**
- It is a slightly modified version of the [audit trigger tool](https://github.com/Oslandia/audit_trigger/blob/master/audit.sql)
* **lizsync**
- stores information on central and clones databases
- manages the sync actions
- maintains an history of synchronisations
## 
## Auditing changes
LizSync uses a modified version of the [audit trigger tool](https://github.com/Oslandia/audit_trigger/blob/master/audit.sql) to **monitor the changes** made in the central and clone databases.
The audit tool stores data in **two tables**:
* **audit.logged_relations**: the list of audited tables and their primary key(s)
* **audit.logged_actions**: the logs of every data modification made on the audited tables
## Demo !
**Videos**
* 1/ Install database structure, create and deploy a ZIP archive: https://youtu.be/l8a1Pn7CpN0
* 2/ Data editing and 2-way synchronisation: https://youtu.be/tnWVBJGqD0M
## Key features
* **Two-way sync**: clone A <-> central <-> clone B <-> central <-> clone C <-> central
* **Field granularity**: replay only needed changes
```sql
UPDATE test.pluviometers SET name = 'New name' WHERE uid = 'XYZ';
```
* **Manage UPDATE conflicts**: last date of modification wins (last person in the field)
* **SQL based**: run from any clone DB
```sql
SELECT lizsync.synchronize();
```
* **Processing algs**: can be run in CLI if needed
* **Unit tests**: installation/upgrade/synchronisation
# Links
* Github repository: https://github.com/3liz/qgis-lizsync-plugin/
* Documentation: https://docs.3liz.org/qgis-lizsync-plugin/
* This presentation: https://docs.3liz.org/qgis-lizsync-plugin/presentation-reveal/
* Temporary plugin repository: https://github.com/3liz/qgis-lizsync-plugin/releases/latest/download/plugins.xml
* Twitter: https://twitter.com/3LIZ_news/
Thank you for your attention
QGIS user meeting / automn 2020