Skip to content

using.md

Latest commit

 

History

History
170 lines (132 loc) · 7.63 KB

using.md

File metadata and controls

170 lines (132 loc) · 7.63 KB
Raw

Using DB Sync

How does it work

A single GeoPackage file in a Mergin Maps project is treated as an equivalent of a database schema - both GeoPackage and a database schema can contain multiple tables with data - and the DB Sync tool keeps content of the tables in the database and in the GeoPackage the same.

A project with several GeoPackage files needs one connection per file (see the connections list in the configuration). Each connection keeps its own local checkout of the project in <working_dir>/<project name>-<modified schema>.

There are two ways how the synchronization can be started:

  1. Init from GeoPackage: if you have a Mergin Maps project with an existing GeoPackage, the tool will create the destination schema and tables in the database (and populate those with data from GeoPackage).
  2. Init from database: if you have database already populated with tables and data, the tool will create the destination GeoPackage in your Mergin Maps project and initialize it.

More technical details:

  • to keep track of the changes in the database, the DB sync tool adds an extra schema in your database (called "base" schema). This schema contains another copy of the data and it should not be touched, otherwise the sync may get to invalid state.
  • the "base" schema contains the same data as the most recently seen project version in Mergin Maps. Whenever the tool attempts to synchronize data, it looks up any pending changes in the database by comparing data between the "base" schema and the "modified" schema (used by editing) - if any changes are detacted, they will be pushed to the appropriate GeoPackage in Mergin Maps project.

Sample configuration file

The configuration of the tool is done using a simple YAML file - that file is then passed as a command line argument to the DB Sync tool.

Here's a sample configuration:

# How to connect to Mergin Maps server
mergin:
  url: https://app.merginmaps.com
  username: john
  password: mysecret

# How to initialize the sync - one of the two options:
# - "gpkg" - use existing GeoPackage from given Mergin Maps project (and create database schema during init)
# - "db" - use existing database schema (and create GeoPackage in Mergin Maps project during init)
init_from: gpkg

connections:
   - driver: postgres
     # Parameters to PostgreSQL database
     conn_info: "host=localhost dbname=mydb user=myuser password=mypassword"
     # Database schema that will be synchronized with Mergin Maps project
     # (it must exist if doing init from database, it must not exist if doing init from geopackage)
     modified: myproject_data
     # Extra database schema that will contain internal data and should never be edited
     # (it must not exist before the sync starts - it will be created automatically)
     base: myproject_data_base
     
     # Mergin Maps project to use (<workspace>/<project>)
     mergin_project: john/myproject
     # Path to the GeoPackage within the Mergin Maps project above
     # (it must exist if doing init from geopackage, it must not exist if doing init from database)
     sync_file: data.gpkg

daemon:
  # How often to synchronize (in seconds)
  sleep_time: 10

When running as a daemon, the tool does not exit immediately when login, initialization or synchronization fails. The failed step is retried with exponential backoff, starting at sleep_time and doubling after each consecutive failure up to 10 minutes (or sleep_time if it is longer). Failed logins are retried less often, up to 1 hour, as rejected credentials can not be fixed by retrying. Once a sync succeeds, the regular sleep_time interval is used again.

Synchronization errors after the daemon has successfully started (e.g. temporary network or server issues) are retried indefinitely. If the start of the daemon (login or initialization) keeps failing, or unexpected errors keep occurring, the daemon exits after max_failed_retries consecutive failed retries (10 by default, which takes about 50 minutes with sleep_time: 10, or longer if the login is failing). Set max_failed_retries: 0 to never exit:

daemon:
  sleep_time: 10
  # Number of consecutive failed retries of the daemon start or unexpected errors before the daemon exits (0 = never exit)
  max_failed_retries: 10

Useful command line options

  • config_file_name.yaml The file name with path of yaml config can be provided. By default the tool uses config.yaml file from the current directory.

  • --force-init forces reinitialization of the sync. Drops dbsync schemas from database and the sync file and inits them all from scratch. This should be used to fix issues with dbsync init.

  • --single-run instead of running the daemon indefinitely, performs just one single run. Such run consists of initialization, pull and push steps. Unlike the daemon, it exits with a non-zero code on failure. Avoid running it in a tight loop (e.g. from cron every minute) - use the daemon instead.

  • --skip-init allows skipping the initialization of sync step. Should be only used if you know, what you are doing, otherwise issues are likely to occur.

  • --log-file specify file to store log info into. If it is not set the log info will only be printed to the console.

  • --log-verbosity use errors or messages to specify what should be logged. Default is messages.

  • --test-notification-email used to test send notification email (see below for details about sending emails in case of sync fails)

Selecting which tables are synced

Sometimes in the database there are tables that should not be synchronised to Mergin Maps projects. There are two mutually exclusive ways to control which tables get synced. Use only one of them per connection - setting both skip_tables and include_tables for the same connection is a configuration error.

Excluding tables (skip_tables)

Add skip_tables to the corresponding connections entry to ignore the listed tables and sync everything else:

connections:
   - driver: postgres
     # ...
     mergin_project: john/myproject
     sync_file: sync.gpkg
     skip_tables:
      - table1
      - table2

Including only specific tables (include_tables)

Alternatively, add include_tables to sync only the listed tables and ignore everything else. This is useful when a database contains many tables but only a few should be synced:

connections:
   - driver: postgres
     # ...
     mergin_project: john/myproject
     sync_file: sync.gpkg
     include_tables:
      - table1
      - table2

Email notifications on sync failures

To simplify db-sync monitoring, it is possible to set up notification emails when a sync failure happens. Simply add notification section in the configuration file as described below.

Use the --test-notification-email command line option to check whether notification emails get sent to make sure the notification configuration is correct.

notification:
  # [required] email sender to be displayed in the message
  email_sender: "dbsync@example.com"
  # [required] email subject
  email_subject: "DB Sync Error"
  # [required] list of recipients of the email notification
  email_recipients: ["recipient1@example.com", "recipient1@example.com"]

  # [required] address of stmp server to send emails
  smtp_server: "smtp.example.com"
  # [optional] smtp server user
  smtp_username: "user@example.com"
  # [optional] smtp user's password
  smtp_password: "password"
  # [optional] smtp port
  smtp_port: 25
  # [optional] use ssl true/false
  use_ssl: false
  # [optional] use tls true/false
  use_tls: false

  # [optional] interval for sending emails (in hours) to avoid sending too many emails (default is 4 hours)
  minimal_email_interval: 4