Skip to content

Database Backup and Restore

The production database host (module.tracker_restore_host, ssh tracker-prod) runs PostgreSQL 16 with TimescaleDB 2.28.2 (since 2026-10-22; 2.18.2 before) on a dedicated EBS data volume mounted at /var/lib/postgresql.

Why backups matter here

Apple's FindMy endpoint returns only the latest ~8 reports per tracker, even though the fetcher asks for 7 days. A lost database cannot be refilled from Apple beyond those few reports, so location data that is not backed up is gone for good. The target recovery point is one hour.

Backup layers

Layer Schedule Retention Restore speed Defined in
DLM EBS snapshot of the data volume Hourly 48 Minutes infra/modules/tracker_restore_host/backup.tf
DLM EBS snapshot of the data volume Daily 03:00 UTC 14 Minutes infra/modules/tracker_restore_host/backup.tf
pg_dump to restic in S3 Daily 01:30 UTC 14 daily, 8 weekly, 6 monthly About an hour tracker-ops roles/db_host/tasks/backup.yml
  • Snapshots are crash-consistent. The data directory and pg_wal share the volume, so PostgreSQL recovers a snapshot as it would after a crash.
  • The restic repository lives in the bucket tracker-restapi-production-tracker-db-backup under the prefix tracker. Its password is in the Secrets Manager secret tracker/production/restic-password.
  • The bucket keeps object versions for 30 days, so backups deleted by mistake or by a compromised host can be recovered for that long.
  • WAL archiving is deliberately off. A failing archive_command filled the disk on 2026-10-22.

Monitoring

Icinga host tracker-prod:

  • restic: the last result of restic-backup.service and the age of the newest tracker.dump and globals.sql snapshots. It warns at 26 hours and goes critical at 50 hours.
  • drivesize and postgresql: disk usage, WAL size and pg_wal partition usage.

DLM snapshot freshness is not monitored. Check it by hand with the command in Find a snapshot.

Restore from an EBS snapshot

Use this for volume loss or corruption, or to recover recent data (up to an hour old).

Find a snapshot

aws ec2 describe-snapshots --profile glimpse-prod --region eu-west-2 --owner-ids self \
  --filters Name=tag:SnapshotCreator,Values=dlm-hourly,dlm-daily \
  --query 'sort_by(Snapshots,&StartTime)[-5:].[SnapshotId,StartTime,Tags[?Key==`SnapshotCreator`]|[0].Value]' \
  --output table

Replace the live volume

  1. Create a volume from the snapshot in the instance's availability zone (eu-west-2a), as gp3 and encrypted with the project KMS key.
  2. On the host, stop PostgreSQL with sudo systemctl stop postgresql@16-main, then unmount /var/lib/postgresql.
  3. Detach the old volume from /dev/sdf. Keep it until the restore is verified.
  4. Attach the new volume as /dev/sdf and mount it. /etc/fstab mounts by UUID, and a volume made from a snapshot keeps the same filesystem UUID.
  5. Start PostgreSQL and follow its log. It replays WAL as it would after a crash.
  6. Check the newest data with select max(timestamp) from location_reports;.

To recover only some rows (for example after a bad DELETE), attach the snapshot volume to a scratch instance instead, start PostgreSQL there and copy the rows across.

Restore from restic

Use this when the snapshots are unusable, or for a portable copy on another host.

Target requirements

  • PostgreSQL 16 with PostGIS. location_reports.location is a geography column. Without PostGIS every geography table fails to restore. Any PostGIS 3.x works: the dump recreates the extension, not its objects.
  • TimescaleDB at exactly the source version. Otherwise timescaledb_post_restore() fails with catalog version mismatch. Each dump snapshot carries the version as a timescaledb=<version> tag. A newer TimescaleDB package is fine, because Debian packages keep older versions available to CREATE EXTENSION ... VERSION.

Fetch the dump

latest on its own picks the host's newest snapshot, which is usually globals.sql, so always pass --path:

source /etc/restic/.env   # or export RESTIC_REPOSITORY / RESTIC_PASSWORD_FILE / AWS credentials yourself
restic snapshots --host tracker-prod --path /tracker.dump    # note the timescaledb=<version> tag
restic dump --host tracker-prod --path /globals.sql latest /globals.sql > globals.sql
restic dump --host tracker-prod --path /tracker.dump latest /tracker.dump > tracker.dump

restic reads static AWS credentials but not SSO profiles. From a workstation, run eval "$(aws configure export-credentials --profile glimpse-prod --format env)" first.

Restore

Apply roles first, then restore in TimescaleDB's restore mode. Set V to the version from the tag:

V=2.28.2   # dumps taken before 2026-10-22 15:17 UTC are tagged timescaledb=2.18.2
# pg_dumpall 16.10+ writes \restrict lines that older psql can't parse; they are safe to drop
grep -v -E '^\\(un)?restrict ' globals.sql | psql -U postgres   # "role already exists" errors are harmless
createdb -U postgres -T template0 -O tracker tracker
psql -U postgres -d tracker -c "CREATE EXTENSION timescaledb VERSION '$V'" -c "SELECT timescaledb_pre_restore()"
pg_restore -U postgres -d tracker -j 4 tracker.dump 2> restore-errors.txt
psql -U postgres -d tracker -c "SELECT timescaledb_post_restore()" -c "ANALYZE"

Expect exactly one error, for fk_geofence_events_location_report_composite. Foreign keys that reference a hypertable can't be checked while restore mode hides the chunks. Re-add the constraint after timescaledb_post_restore():

psql -U postgres -d tracker -c 'ALTER TABLE public.geofence_events
  ADD CONSTRAINT fk_geofence_events_location_report_composite
  FOREIGN KEY (location_report_id, "timestamp") REFERENCES location_reports(id, "timestamp") ON DELETE CASCADE'

Look at any other error in restore-errors.txt before you trust the restore.

Test restore on a workstation

The plain timescale/timescaledb image has no PostGIS, so use the HA image at the matching version:

docker run -d --name tracker-restore -e POSTGRES_PASSWORD=restore timescale/timescaledb-ha:pg16.14-ts2.28.2   # pg16.8-ts2.18.2 for older dumps

Run the restore commands inside the container through docker exec -i tracker-restore, streaming the dump in with restic dump ... | docker exec -i tracker-restore pg_restore -U postgres -d tracker. That way prod data never sits on the workstation's disk. Remove the container and its data with docker rm -f -v tracker-restore when you're done.

Running the backup by hand

sudo systemctl start restic-backup.service
systemctl show restic-backup.service -p Result --value   # expect: success
sudo bash -c 'source /etc/restic/.env && restic snapshots --host tracker-prod'

Restore tests

Date Source Target Result
2026-10-22 staging dump (TimescaleDB 2.27.0) scratch database on the staging host Row counts identical to live
2026-10-22 prod dump, 3.6 GiB (TimescaleDB 2.18.2) timescaledb-ha:pg16.8-ts2.18.2 on a workstation Monthly location_reports counts and trackers, users and geofence_events counts match prod at dump time; about 5 minutes end to end

The prod backup takes about 2 minutes: a 3.6 GiB dump, stored as 667 MiB on the first run.

Changing the setup

  • Terraform: the bucket, DLM policy, secret and IAM are in infra/modules/tracker_restore_host/backup.tf.
  • Ansible: the script, timer and restic configuration come from tracker-ops. Apply them with:
ansible-playbook -i inventories/prod/hosts.yml playbooks/site.yml --tags backup \
  -e ansible_host=tracker-prod -e ansible_connection=ssh