Tier 1 database backups¶
Built 2026-09-29. pgBackRest 2.59.1, encrypted repo on the R940 tank-bulk pool. Continuous WAL plus a daily full. This is the recovery path for a bad migration, a DELETE, or a botched schema deploy while the R940 is up.
What this does and does not cover¶
Two copies, one machine.
- Live data sits on
tank-fast(the VM disks). - The backup repo sits on
tank-bulk, a separate ZFS pool, at/tank-bulk/backups/pgbackrest. - Both pools are in the same R940 chassis.
That covers a bad migration, an accidental DELETE, or a botched deploy while the host is running. A tank-fast disk failure is also covered, because the backups are on the other pool.
It does not cover loss of the host: fire, theft, or ransomware that has root on the hypervisor. Both copies die together. This is not 3-2-1.
The nightly 02:00 America/Los_Angeles vzdump of VMIDs 100-123 is still a whole-VM copy. It is not point-in-time recovery, and its support@last.vet mail path is not the alert path for these backups.
The WD My Cloud NFS mount on pve-r940 is not part of this design.
Tier 2, queued¶
When the R740 is up, add a second pgBackRest repo (repo2) over SSH. Same cipher. pgBackRest writes both repos on every backup and every archive-push. Do not hand-rsync the repo. After that there are three copies on two machines, which is the 3-2-1 layout.
Repo¶
| Setting | Value |
|---|---|
| Path | /tank-bulk/backups/pgbackrest on pve-r940 (192.168.1.111) |
| Cipher | aes-256-cbc |
| Passphrase source | Bitwarden Secrets Manager project last1-platform, secret PGBACKREST_REPO1_CIPHER_PASS |
| Runtime copies | /etc/pgbackrest/cipher-pass, mode 640, on pve-r940 and on each database host. Not inside the backup directory. |
| Full retention | repo1-retention-full-type=time, repo1-retention-full=56 (daily fulls kept 56 days) |
| WAL retention | repo1-retention-archive-type=full, repo1-retention-archive=14 (WAL for the latest 14 fulls) |
| Schedule | lastvet 04:20 UTC, last1app 04:40 UTC, muster 05:00 UTC |
pgBackRest 2.59 has no separate weekly class. Keeping every daily full for 56 days keeps 8 weekly points and the days between them. That is more retention than "8 weeklies," not less. After WAL for a full expires, that full remains a single restore point until day 56.
archive_timeout=60. PostgreSQL switches a WAL segment within 60 seconds once there has been activity. A completely idle database does not archive on the clock. During a migration, a committed row is in the repo within about a minute.
Daily fulls stay the cadence while these databases are tens of megabytes. A full today finishes in well under a minute. Switch that daily full to a daily diff or incremental, with a weekly full, when a database reaches tens of GB or a full takes long enough to matter. Do not make that change at the current size.
Pre-migration checkpoint¶
Immediately before schema SQL, take one --type=full backup annotated with the migration filename (--annotation=migration=<filename>). Restore that backup set. Do not compute a clock time. This is not part of deploy-from-git.sh. That script builds and swaps the last1.app container and never applies schema.
| Database | Where SQL is applied | Checkpoint |
|---|---|---|
| last1app | lastvet-gates/scripts/last1app/apply-*.sh, run from the operator machine. Each script pipes one SQL file into supabase-db. |
The script runs the full on 192.168.1.103 before that psql. |
| lastvet | node scripts/migrate.js on lastvet-api, against 192.168.1.91. Prod and staging units do not migrate on boot. The host checkout is bf05691, so this file itself takes the full. |
/usr/local/sbin/lastvet-migrate takes the full, then runs the runner with LASTVET_PGBACKREST_CHECKPOINT=1 so it does not take a second full. A direct node scripts/migrate.js on that checkout also takes the full. Local and non-R940 URLs are not checkpointed. |
| muster | There is no migration runner. muster/services/muster-v2/sql/001_schema.sql loads only when the Postgres volume is first created. Later SQL goes through lastvet-gates/scripts/muster/apply-sql.sh. |
That script takes the full on 192.168.1.97 before psql. |
If the full fails, the SQL does not run. Find the set with pgbackrest info --stanza=<stanza> and look for the migration annotation, then restore --set=<label>.
The client config carries the same retention lines. Expire runs on the client, so a client without them would ignore the repo host's retention.
Each database host has a runtime copy of the passphrase because pgBackRest requires repo1-cipher-pass on the client that archives and backs up. The backup directory does not contain it. The passphrase is not in pgbackrest.conf.
Stanzas¶
| Stanza | Host | How Postgres runs | archive_command |
|---|---|---|---|
| lastvet | VM 108, 192.168.1.91 |
Native PostgreSQL 17 | /usr/local/bin/pgbackrest --stanza=lastvet archive-push %p |
| last1app | VM 120, 192.168.1.103 |
supabase/postgres:17.6.1.136, container supabase-db. Image is not forked. pgBackRest is a bind-mounted musl build. |
/opt/pgbackrest-bundle/pgbackrest --stanza=last1app archive-push %p |
| muster | VM 115 admin-app, 192.168.1.97 |
postgres:16, container muster-muster-db-1. Ubuntu pgBackRest binary bind-mounted with libssh2 and /usr/bin/ssh. |
/opt/pgbackrest-bundle/pgbackrest --stanza=muster archive-push %p |
archive_mode=on on all three. That setting is read at server start. A reload does not turn it on.
last1app keeps the mounts in /opt/supabase/docker/docker-compose.override.yml, and COMPOSE_FILE includes that override. Recreating supabase-db without those mounts breaks archive-push. muster mounts are in /opt/muster/docker-compose.yml.
Backups are started from the database side. The repo host config has empty stanza sections and no pg1-host, so a restore run on pve-r940 is not aimed at the live server.
First fulls, 2026-09-29:
- lastvet
20260929-182158F(46MB) - last1app
20260929-183328F(77.3MB) - muster
20260929-183510F(36.3MB)
WAL is archiving after each full. The oldest recoverable point today is that full. The 14-day window fills as the daily fulls accumulate.
Tested restore, 2026-09-29¶
Definition of done: a throwaway cluster, never the live port. Marker row in database backup_drill. Restore to a minute before the row (row absent). Restore to a minute after (row present). Scratch deleted. Live row still there. archive_mode still on.
The "after" target sits between the marker commit and a later anchor commit, because a target past the last archived WAL record fails recovery. Query only after pg_is_in_recovery() is false. Hot standby accepts connections before PITR finishes, and a query at that moment can look like a success when the row is simply not replayed yet.
| Stanza | Before (row absent) | Marker | After (row present) | Scratch |
|---|---|---|---|---|
lastvet tier1-drill-lastvet-c |
2026-09-29 18:29:14.563057+00, count 0. Recovery stopped before commit 18:29:16.634131+00. |
2026-09-29 18:29:16.633793+00 |
2026-09-29 18:29:17.000000+00, count 1. Recovery stopped before the anchor commit 18:29:18.705876+00. |
127.0.0.1:54330, then deleted. Record: /var/lib/pgbackrest/drills/lastvet-20260929T1829Z.txt |
last1app tier1-drill-last1app |
2026-09-29 18:35:52.000000+00, count 0 |
2026-09-29 18:35:53.553204+00 |
2026-09-29 18:35:54.500000+00, count 1 |
Unix socket only, no published port, then deleted. Record: /var/lib/pgbackrest/drills/last1app-20260929.txt |
muster tier1-drill-muster |
2026-09-29 18:38:13.658158+00, count 0 |
2026-09-29 18:38:15.806595+00 |
2026-09-29 18:38:16.806595+00, count 1 |
Unix socket only, no published port, then deleted. Record: /var/lib/pgbackrest/drills/muster-20260929.txt |
Live backup_drill still has each marker (count 1). PUBLIC is revoked on that database. Do not put drill rows in the application database.
Alerts¶
Alertmanager, severity critical. That route already emails ryan@last1.enterprises and alerts@last1.enterprises and posts to the existing webhook. These rules do not use the vzdump sendmail path.
| Alert | Fires when |
|---|---|
PgBackRestFullBackupStale |
Newest full is older than 26 hours, for 15 minutes |
PgBackRestArchivePushFailing |
Newest archive attempt failed, for 10 minutes |
PgBackRestArchiveModeOff |
archive_mode is off, for 5 minutes |
PgBackRestMetricsStale |
The metrics file is older than 10 minutes |
PgBackRestMetricsMissing |
Prometheus loses the series for a stanza |
PgBackRestRepoNotEncrypted |
A stanza is not aes-256-cbc |
Metrics come from node_exporter textfiles. Backup age is written on pve-r940. archive_mode and archive-push failure are written on each database host. last1app-db (192.168.1.103:9100) was added to the Prometheus node job. Port 9100 on that VM is allowed from 192.168.1.93 only.
Checked after reload on 2026-09-29: all three archive_mode series are 1, all three archive-failure series are 0, all three repos report encrypted, fulls are minutes old, and no PgBackRest* alert is firing.
Not in this build¶
- last1.id (VM 113), last1org-db (VM 122), onset-db (VM 119), realoutcomes-db (VM 117).
- Org-admin rollback. That is a later application feature: soft-delete plus audit-undo for one org. It is not per-tenant PITR. The database is shared, so restoring one org by rewinding the cluster would rewind every other org.
Restore sketch¶
Run the restore from the database host, into a path that is not the live data directory. For lastvet the proven scratch path is /var/tmp/pgbackrest-drill/lastvet with its own postgresql.conf (data_directory pointed at that path, max_connections and the other recovery minimums at least as high as the primary, archive_mode=off). For the containers, restore into a throwaway container with listen_addresses empty and no published port, and point restore_command at the pgBackRest wrapper so the cipher is loaded. Wait until pg_is_in_recovery() is false before trusting a query. Delete the scratch when the check is done.