Backup, restore & update

We provide tools to perform both physical and logical database backups. For physical backups, we use pgBackRest installed inside the Docker container with TimescaleDB. Logical backups are done with standard PostgreSQL tools and can be used to migrate between major PostgreSQL versions.

Backups are stored in BACKUP_DIR. The directory is owned by the postgres user (uid 999)

Physical backup

The default pgBackRest stanza name is main. We leave physical backups for the user to handle. Login into the container to manage the backups:

docker exec -it timescaledb bash
pgbackrest --stanza=main info
pgbackrest --stanza=main backup
...

By default, we keep 2 full backups + 4 differential backups and 7 days PITR via WAL. See /etc/pgbackrest/pgbackrest.conf for details.

To restore the latest physical backup + replay most recent WAL:

docker stop timescaledb
docker volume rm pgdata
docker volume create pgdata
docker run --rm -v pgdata:/var/lib/postgresql/data \
    -v ${BACKUP_DIR}:/backups \
    -v ./docker/pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf:ro \
    --entrypoint pgbackrest timescaledb:latest \
    --stanza=main --type=default --target=latest restore

Logical backup

By default, all TimescaleDB and Grafana databases are backed up. For Timescale, we perform a full logical backup with pg_dump which can be used to restore the database between different PostgreSQL versions. For Grafana, we simply backup its SQLite database file.

emhealth db backup

Restore a logical backup

You can restore either TimescaleDB or Grafana database from a backup file.

emhealth db restore

Updating

Due to Timescale extension, updating the database might get complicated, we recommend the procedure below:

  1. Run pip install -U em_health. This will update the python package and current schema version

  2. Run emhealth update. For each database, the script will try to:

    • migrate the current db schema to the latest version

    • do the full backup

    • pull the latest container images which may contain newer PostgreSQL / Timescale / Grafana versions

    • restore PostgreSQL and Grafana db from the backup

    • upgrade Timescale and other extensions

  3. Update historical stats: emhealth db -d tem create-stats

Updating PostgreSQL from v17 to v18

Starting from EMHealth 0.1a5 we have migrated PostgreSQL from v17 to v18. Major server version upgrades are not automated, so please follow the steps below:

  • install the latest package that still support PG17: pip install em_health==0.1a4

  • update everything except PG17 to the latest version: emhealth update

  • upgrade EMHealth to 0.1a5 or later: pip install -U em_health

  • update again: emhealth update