Backups and restore

Two kinds of backup go to your Cloudflare R2 bucket:

• From the web app (Admin → Backups, for system admins): two halves taken under one name, a Postgres dump (pg_dump, custom format) and a ClickHouse backup (BACKUP DATABASE ... TO S3), with a schedule, restore drills and a one-click restore of both databases while the instance runs.
• pgBackRest, for Postgres alone (below): every change, continuously, with base backups the pgbackrest service takes by itself, restored by hand on the server to any moment of the last BACKUP_RETENTION_DAYS.

Neither replaces the other: ClickHouse's events are only in the web app's backups, and Postgres's last minutes and every moment between two backups only in pgBackRest's archive. They never touch each other's objects.

What you needCopy Anchor Link

• The four R2_* values in your secrets (Object storage). The R2 token needs Object Read & Write on the bucket, and Admin Read & Write as well if the web app's backups should maintain their retention rule; without it, delete old backups by hand.
• Optionally BACKUP_TIMEOUT_SECONDS (default 3 hours) and BACKUP_RETENTION_DAYS (default 30) (Backups settings).
• A copy of the secrets (.env or Doppler) kept off the server: a backup cannot be used without them, and pgBackRest's cannot even be decrypted without PGBACKREST_REPO1_CIPHER_PASS.

What is in R2Copy Anchor Link

The R2 lifecycle rule backups-retention deletes everything under backups/ after BACKUP_RETENTION_DAYS; R2 applies expirations within about a day. If the token cannot edit lifecycle rules, a run says old backups are NOT expiring, and still succeeds.

• What a backup holds. Every Postgres table (accounts, organizations, sites, settings, API keys, share links, invitations, the job queue) and every ClickHouse table of the analytics database. Dumps contain password hashes, hashed API keys and share tokens, and invitations; R2 encrypts at rest, nothing else does. Scope the R2 token to this bucket.
• What it does not hold. Valkey (sign-in sessions, visitors' open sessions, live presence, rate limits, caches), the geoip and maps volumes, the files stored in R2 itself (session replays, uploaded imports), and the secrets.

From the web appCopy Anchor Link

The Backups tab of the Admin page (/admin?tab=backups, system admins only) shows whether R2 is configured (the bucket and endpoint, never the key pair), the retention, the last backup and the last failure, the next scheduled run and the API's pg_dump version. Without R2_* the tab says so and nothing runs. From it you can:

• Run backup now or Run drill (a restore drill, below).
• Schedule backups: Off (the default), Every 12 hours, Daily or Weekly, at a whole UTC hour (Weekly also on a weekday). A scheduled backup starts within 10 minutes of its time.
• Read the History of every backup, drill and restore: who started it, its sizes, its R2 keys, and its error (with the R2 key pair and the database password removed).
• See the Backups in R2 and Restore… one whose two halves are both there and complete.
• Drop the leftover ClickHouse copies a restore or an interrupted run left next to the live database.

The same operations are in the API, for system admins only:

How runs workCopy Anchor Link

• Where. A run is a row of the Postgres table backup_runs, worked by a background job (backup.run) in a process with workers (APP_ROLE all, the default, or worker). The API image ships PostgreSQL 17's pg_dump, pg_restore and psql, the server's major version. The status line pg_dump turns red when the process's pg_dump is older than the server, which it refuses to dump.
• A backup streams pg_dump --format=custom straight into R2 (a multipart upload of 8 MiB parts; no local copy), then runs the ClickHouse backup, then applies the retention rule. Each step has its own BACKUP_TIMEOUT_SECONDS, and the whole run stops after 24 hours, the job queue's limit for one job.
• A drill proves a restore works: the dump is uploaded, read back and compared byte for byte (SHA-256), restored into a side database <database>_drill, the members, organizations, sites and users counted and compared with the live ones, and the copy dropped; then the same for ClickHouse into <database>_drill. The counts are in the run's history. The drill needs free disk about the size of the data. ClickHouse tables are compared by row count within what ingest can change meanwhile; re-run a failed drill (after an admin change or a user erase during it) before treating it as lost data. A row-count check is not a content check.
• One run at a time. At most one backup, drill or restore is queued or running for the whole instance: a second request gets 409. A failed run is not retried. The worker holds a lock for the whole run, which the backup CLI's ClickHouse commands take too, so they never run at the same time as a web app run.
• Schedule. A job checks every 10 minutes and starts the backup of a slot that has passed, once per slot. A slot missed while no worker ran is caught up once, by the first check after it. Saving the schedule never starts a slot that already passed.
• Bad settings. An invalid BACKUP_* value shows on the tab and stops runs from starting until it is fixed.
• Deadlines. Every step has the wall-clock deadline BACKUP_TIMEOUT_SECONDS. A ClickHouse job that stays CREATING_BACKUP (an R2 that never answers) ends when you restart ClickHouse: docker compose restart clickhouse.

RestoreCopy Anchor Link

Restore… replaces all data, in Postgres and ClickHouse, with a backup whose two halves are both in R2 and complete. You confirm by typing the backup's name exactly. It is refused (409) while another run is active, while a user erase, a site purge or an import is due or running (they write data the restore would replace; wait for them), or while a copy left by an interrupted restore exists. When queued imports are all that holds it off, the dialog offers to cancel them: each is marked failed ("cancelled for a restore") and its uploader has to upload it again. A running import, a user erase or a site purge is never cancelled.

What the restore does, in order:

1. Waits for a payment-provider, Search Console or notification job that was already running (at most 15 minutes, else it fails and changes nothing); until the restore ends, those jobs do nothing and catch up afterwards.
2. Downloads the dump into the API's data volume and refuses a backup taken by a newer version of PilotFox.
3. Takes the ClickHouse half of a safety backup of the current data (its own run in the history). If it fails, nothing is restored.
4. Restores ClickHouse into a side database, brings it to this version (migrations and retention), and checks it has exactly the live tables. This needs free disk about the size of the data.
5. Takes the Postgres half of the safety backup, now, so that what was written meanwhile is in it.
6. Replaces Postgres in one transaction (the job queue's own schema is kept), applies the migrations the backup lacks, and moves the site id sequence past every site id ever handed out, so no new site gets the id of one created after the backup.
7. Swaps each ClickHouse table with its restored copy, then commits Postgres. If a swap or the commit fails, both databases are put back as they were, checked table by table.
8. Keeps the replaced ClickHouse tables as <database>_pre_restore_<time> until you drop them, and clears Valkey's sign-in sessions and the caches built from the old data.

Afterwards:

• While Postgres is replaced, every request that touches it waits. No restart is needed.
• Everyone, the admin included, is signed out, and signs in with the accounts as they were in the backup. API keys and share links are the backup's. Visitors' analytics sessions, live presence and rate limits carry on.
• Everything recorded after the backup is gone, deletions included: sites deleted and users erased since come back, so delete or erase them again.
• R2 is not restored: replays and uploads stay as they are.

If ClickHouse cannot be put back after a failed swap, the run fails with MANUAL RECOVERY NEEDED, names the tables, and keeps the tables that were live in <db>_pre_restore_<time>; Postgres is unchanged. To finish by hand, for each table it names:

EXCHANGE TABLES <db>.<table> AND <db>_pre_restore_<time>.<table>;
-- check: every table of <db> now has the UUID it had (system.tables), then
DROP DATABASE <db>_pre_restore_<time>;

Continuous Postgres backups (pgBackRest)Copy Anchor Link

With the R2_* values set, Postgres also backs itself up continuously with pgBackRest, under pgbackrest/ in the same bucket. docker-compose.yml configures it; nobody needs to run a command.

• The WAL archive. Postgres hands each completed 16 MiB segment of its write-ahead log to pgBackRest, which sends it to R2, and starts a new segment after at most 60 seconds with writes: at most about a minute of committed changes exists on the server alone. Without R2, archiving is off and Postgres keeps no WAL for it.
• Base backups by the pgbackrest service: once Postgres answers, it creates its stanza (retrying every minute until R2 answers) and takes a full backup at once; then a full backup a week and a differential backup on the other days, in the UTC hour 21. It reads the last backup's age from R2, so restarts neither skip nor repeat one.
• Encryption. Everything is encrypted (AES-256) with PGBACKREST_REPO1_CIPHER_PASS before it leaves the server. That passphrase can never change once the repository exists, and nothing in it can be read without it.
• Retention. BACKUP_RETENTION_DAYS (30 by default), as days: a full backup expires, with its differentials and the WAL only it needs, once a newer full backup is at least that old. A point-in-time restore reaches at least that many days back.
• When R2 cannot be reached, Postgres keeps the WAL and retries. At most PGBACKREST_ARCHIVE_PUSH_QUEUE_MAX (4 GiB) waits; beyond it pgBackRest drops the waiting WAL rather than let it fill the disk, and logs dropped WAL file '…' because archive queue exceeded … (docker compose logs postgres). A point-in-time restore then cannot reach a moment after the first dropped segment from a backup taken before it. After such a line, take a backup once R2 answers: docker compose exec pgbackrest pgbackrest --type=full backup.
• A bucket in an EU or FedRAMP jurisdiction: with R2_ENDPOINT set to its URL, set PGBACKREST_REPO1_S3_ENDPOINT to that URL's host (Compose settings).

Check it, after setting it up and after an R2 outage (nothing mails a failure):

docker compose exec pgbackrest pgbackrest info   # status ok, the backups, the oldest and newest archived WAL
docker compose logs pgbackrest                   # the stanza, the backups, the expiry
docker compose exec postgres psql -U app -d app -c 'select archived_count, last_archived_wal, failed_count, last_failed_time from pg_stat_archiver'

Point-in-time restoreCopy Anchor Link

Postgres goes back to its state at a moment of the last BACKUP_RETENTION_DAYS, for example just before a mistaken delete. ClickHouse, Valkey and R2 stay as they are. The instance is down from step 2 to step 5.

docker compose stop cloudflared api
docker compose exec postgres psql -U app -d app -Atc 'select pg_walfile_name(pg_switch_wal())'
docker compose exec postgres psql -U app -d app -Atc 'select last_archived_wal from pg_stat_archiver'   # until it names that file
docker compose stop pgbackrest postgres

docker compose run --rm --no-deps -T --entrypoint sh postgres -c 'find /var/lib/postgresql/data -mindepth 1 -delete'

docker compose run --rm --no-deps -T --entrypoint pgbackrest postgres \
  --type=time "--target=2026-11-02 13:47:47.464894+00" --target-action=promote restore

• A moment the archive does not reach (after its newest WAL, or before its oldest backup) makes Postgres stop with "recovery ended before configured recovery target was reached", and up --wait fails: empty the directory again and pick a moment in reach.
• Everything written to Postgres after the moment is gone. A site created after it may still have events in ClickHouse: before anyone creates a site, move the site id sequence past ClickHouse's highest site_id (Disaster recovery, step 3).
• Take a full backup afterwards (docker compose exec pgbackrest pgbackrest --type=full backup), so the next restore starts from it.

Disaster recoveryCopy Anchor Link

On a running instance, a restore from Admin → Backups does it all in one go and puts both databases back if it fails. On a new server, or after losing the old one:

docker compose run --rm -T --no-deps api node dist/ops/backup-cli.js clickhouse-restore <name> analytics_restored
docker compose exec clickhouse clickhouse-client -u app --password \
  -q "RENAME DATABASE analytics TO analytics_empty; RENAME DATABASE analytics_restored TO analytics"

docker compose exec clickhouse clickhouse-client -u app --password -q "
            SELECT maxOrNull(site_id) FROM analytics.events
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.bot_events
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.replay_chunks
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.replay_sessions
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.identity_aliases
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.payments
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.subscriptions
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.search_console_rows
  UNION ALL SELECT maxOrNull(site_id) FROM analytics.search_console_days"

docker compose exec postgres psql -U app -d app -c "SELECT setval('public.sites_id_seq', <N>) FROM public.sites_id_seq WHERE last_value <= <N>"

Backup CLI referenceCopy Anchor Link

The CLI is node dist/ops/backup-cli.js inside the API image, for a recovery by hand (above); run it with docker compose run --rm -T --no-deps api node dist/ops/backup-cli.js <command>. It reads its settings from the env file the API container gets, never from the shell. It exits 0 when it worked, 1 when it failed, was stopped or hit BACKUP_TIMEOUT_SECONDS, and 2 for a usage error, missing R2_* values, an invalid BACKUP_* setting, a get of a key that is not in R2, or a restore target that is the live database or not a plain name. Every message goes to stderr; only get writes to stdout.