Admin: Database Backup & Restore

This document covers instance-level database backup configuration, destination setup, retention semantics, restore procedures, the vetrix-cli backup command surface, and operator troubleshooting.

The backup subsystem is composed of a scheduler (internal/backup/scheduler.go), eight admin HTTP endpoints (internal/api/backup_handler.go), the vetrix-cli backup command tree (cmd/vetrix-cli/), and three database tables: backup_configs, backup_history, and backup_restores.

Purpose & scope

Vetrix takes scheduled or on-demand pg_dump snapshots of the primary PostgreSQL database, gzips them, and writes the result either to a local directory or to a configured cloud destination (S3, GCS, or Azure Blob). A retention pruner reclaims old snapshots according to per-config rules. A restore endpoint replays a chosen snapshot into the live database or into a sibling schema for inspection.

In scope:

• Scheduled and manual pg_dump runs (one config = one cron schedule).
• Four destination types: local, s3, gcs, azure_blob.
• Retention by age (retention_days) and/or count (retention_count).
• Restore against the live database (target=primary) or a sibling schema (target=staging_schema).
• Operator CLI (vetrix-cli backup) wrapping every admin endpoint.

Out of scope (deferred):

• Point-in-time recovery via WAL archiving.
• Cross-cluster / different-host restore.
• At-rest encryption of the dump file itself (only destination_config credentials are encrypted today).
• Webhooks / outbound notifications on restore completion.
• Per-table or per-schema restore.

Runtime settings

The backup subsystem is configured through environment variables and per-config rows in backup_configs. There is no app.toml section dedicated to backups today — the scheduler reads its inputs at process startup (cmd/server/main.go).

Environment variables

Key	Default	Description
VETRIX_BACKUP_DIR	/data/backups	Root directory for the local destination adapter and the scheduler's temp/output path. Snapshots are written under {VETRIX_BACKUP_DIR}/{YYYY-MM-DD}/{configID}_{timestamp}.sql.gz.
SECRET_ENC_KEY	(unset)	64-character hex string (32 bytes) used as the AES-256-GCM key for destination_config (and webhook secrets / SMTP / OAuth client secrets — see security.md). The server refuses to start when the key is unset and any encrypted backup config exists in the database.
DATABASE_URL	(required)	The libpq connection URL the scheduler hands to pg_dump and the restore worker hands to psql / pg_restore. The restore worker enforces current_database() matches expected_database_name to prevent cross-environment mistakes.

Generate SECRET_ENC_KEY:

openssl rand -hex 32

Set the key before starting the server:

export SECRET_ENC_KEY=<64-hex-character-string>

Per-config columns (backup_configs)

Every row in backup_configs represents one schedule + destination + retention policy. The schema is in migration 000054_backup_configs.up.sql:

Column	Default	Description
name	(required)	Human-readable label.
schedule_cron	(required)	5-field cron expression (min hour dom month dow). Validated by backup.ValidateCron.
backup_type	full	full, schema_only, or incremental (incremental falls back to full — pg_dump has no native incremental).
destination_type	local	local, s3, gcs, or azure_blob.
destination_config	''	JSON blob; AES-256-GCM-encrypted at rest when encryption_enabled=true. Schema per type in §3.
retention_days	30	Days to keep an archive. 0 disables the days rule.
retention_count	0	Cap on retained successful archives. 0 disables the count rule.
compression	gzip	gzip, zstd, or none (today the scheduler always pipes through gzip; the column is forward-compat).
encryption_enabled	false	When true, destination_config is sealed with SECRET_ENC_KEY on write.
notify_on_success	false	Emit a notification on successful run.
notify_on_failure	true	Emit a notification on failed run.
enabled	true	When false, the scheduler does not run this config and the pruner skips it.

Scheduler

The scheduler runs as a goroutine inside the vetrix server process (internal/backup/scheduler.go). Its tick(ctx) fires every DefaultPollInterval = 1 minute and does two phases per enabled config:

1. Phase 1 — cron-driven backup. If cronMatches(cfg.ScheduleCron, now), spawn a goroutine to RunJob(ctx, cfg, nil). The goroutine acquires a per-config PostgreSQL session-level advisory lock (pg_try_advisory_lock, key namespace 0x4255) and holds it for the full duration of the pg_dump so a concurrent run, a manual trigger, or the pruner cannot overlap.
2. Phase 2 — retention pruner. If cfg.RetentionDays > 0 || cfg.RetentionCount > 0, spawn a goroutine to RunPrune(ctx, cfg). The pruner takes the same advisory lock; if the lock is held by a running job it skips with backup.retention.skipped (reason="locked_by_other_op").

Destination configuration

Every non-local destination stores its credentials inside the destination_config JSON column. When encryption_enabled=true the JSON is sealed with AES-256-GCM under SECRET_ENC_KEY before the row is written; reads on the API never include destination_config in any response. Unknown fields in any of the schemas below are rejected at adapter construction time (json.Decoder.DisallowUnknownFields).

The four adapters live under internal/backup/destination/ (s3.go, gcs.go, azure.go) and internal/backup/local_destination.go.

Local

Stores archives on the local filesystem, under {VETRIX_BACKUP_DIR}/{YYYY-MM-DD}/{configID}_{timestamp}.sql.gz. This is the default and requires no destination_config.

Field	Type	Required	Secret	Validation
root_dir	string	no	no	Absolute path; must exist, be writable, and not be a symlink to outside the root.

Example:

{
  "root_dir": "/var/lib/vetrix/backups"
}

When destination_config is empty for destination_type=local, the scheduler falls back to VETRIX_BACKUP_DIR.

S3

Streams to AWS S3 or any S3-compatible service (MinIO, Ceph, Wasabi). Multipart upload kicks in above 100 MiB (MultipartThresholdBytes) with a 16 MiB part size.

Field	Type	Required	Secret	Validation
bucket	string	yes	no	^[a-z0-9.\-]{3,63}$ — AWS bucket-name rules.
region	string	yes	no	Non-empty; not validated against a fixed list (opt-in regions / S3-compat).
prefix	string	no	no	No leading /. Must end with / if non-empty.
endpoint	string	no	no	http(s):// URL when set; subject to the SSRF guard.
access_key_id	string	yes	no	Required (no IAM-instance-profile fallback for multi-tenant safety).
secret_access_key	string	yes	yes	Required.
session_token	string	no	yes	Only meaningful with temporary credentials.
use_path_style	bool	no (default false)	no	Set true for MinIO and other path-style providers.
sse	enum	no	no	"", "AES256", or "aws:kms".
kms_key_id	string	conditional	no	Required when sse = "aws:kms".

Example:

{
  "bucket": "vetrix-backups",
  "region": "us-east-1",
  "prefix": "prod/",
  "access_key_id": "AKIAIOSFODNN7EXAMPLE",
  "secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
  "use_path_style": false,
  "sse": "AES256"
}

GCS (Google Cloud Storage)

Streams via the Google cloud.google.com/go/storage SDK. Resumable upload kicks in above 100 MiB with a 16 MiB chunk size. Credentials are sourced exclusively from credentials_json — no Application Default Credentials, no metadata server, no environment fallback.

Field	Type	Required	Secret	Validation
bucket	string	yes	no	^[a-z0-9._\-]{3,63}$ — GCS naming rules.
prefix	string	no	no	No leading /. Must end with / if non-empty.
credentials_json	string	yes	yes	A full Google service-account JSON document. Parsed (not just length-checked) — type must be service_account and private_key / client_email / token_uri must be present.

Example (line breaks added for readability — the real value is a single JSON string):

{
  "bucket": "vetrix-backups",
  "prefix": "prod/",
  "credentials_json": "{\"type\":\"service_account\",\"project_id\":\"my-project\",\"private_key_id\":\"...\",\"private_key\":\"-----BEGIN PRIVATE KEY-----\\n...\\n-----END PRIVATE KEY-----\\n\",\"client_email\":\"backup@my-project.iam.gserviceaccount.com\",\"token_uri\":\"https://oauth2.googleapis.com/token\"}"
}

Note: a use_workload_identity field is reserved for ADC-based auth but the current adapter rejects it; rely on credentials_json only.

Azure Blob Storage

Streams via the Azure azblob SDK. Chunked block-blob upload kicks in above 100 MiB with a 16 MiB block size and 4-way concurrency.

The adapter accepts auth_mode = shared_key and auth_mode = sas. The frontend sub-form additionally renders service_principal and managed_identity, but the adapter does not validate them. Configurations using service_principal / managed_identity fail at adapter construction.

Field	Type	Required	Secret	Validation
account_name	string	yes	no	^[a-z0-9]{3,24}$.
container	string	yes	no	^[a-z0-9](?:[a-z0-9\-]{1,61}[a-z0-9])?$.
prefix	string	no	no	No leading /. Must end with / if non-empty.
endpoint_suffix	string	no (default core.windows.net)	no	Hostname-shaped; supports Azure Gov / sovereign clouds.
auth_mode	enum	yes	no	shared_key or sas. service_principal / managed_identity not supported.
account_key	string	when auth_mode=shared_key	yes	Valid base64.
sas_token	string	when auth_mode=sas	yes	Starts with ? or sv=.

Example (shared key):

{
  "account_name": "vetrixbackups",
  "container": "db-dumps",
  "prefix": "prod/",
  "auth_mode": "shared_key",
  "account_key": "BASE64ENCODEDACCOUNTKEY=="
}

Example (SAS):

{
  "account_name": "vetrixbackups",
  "container": "db-dumps",
  "auth_mode": "sas",
  "sas_token": "?sv=2024-08-04&ss=b&srt=co&sp=rwl&se=2027-01-01T00%3A00%3A00Z&sig=…"
}

Encryption-at-rest of destination_config

When encryption_enabled=true on a config, the API encrypts destination_config with AES-256-GCM keyed from SECRET_ENC_KEY before writing the row. The scheduler decrypts on every job. The encryption applies only to the JSON credential blob — the dump file itself is plaintext (or gzipped plaintext) on disk and on the destination. See security.md for SECRET_ENC_KEY rotation guidance.

Retention

Retention semantics are implemented in internal/backup/prune.go plus internal/backup/local_destination.go. This section is the operator-facing summary.

Sentinel semantics

• retention_days = 0 → days rule disabled (do not prune by age).
• retention_count = 0 → count rule disabled (do not prune by count).
• Both 0 → retention is fully disabled for the config; the pruner skips it and emits backup.retention.skipped with reason="retention_disabled".
• Negative values are rejected at the API.

AND-on-keep precedence

When both rules are active, an archive is kept iff both rules say "keep". Equivalently, the pruner deletes any archive that fails either rule. Raising either knob never shrinks the retained set.

Latest-success pin

The most recent status='success' history row is never pruned, regardless of retention_days / retention_count. If retention_days = 1 and the most recent successful archive is 3 days old, that archive is still kept. When no successful archive has ever landed for a config, the pruner skips it entirely with reason="no_successful_archive" — failed-only history is never a restore-from candidate.

Failed-run handling

• Failed runs are prunable by the days rule (so a broken config does not accumulate indefinitely).
• Failed runs are never pruned by the count rule alone.
• Failed runs are never counted toward the retention_count cap.
• Storage unlink for failed runs is best-effort; failure to unlink is logged but does not abort the prune.

Worked examples

Successful archives only, oldest → newest: A(45d), B(20d), C(10d), D(2d), E(1d).

retention_days	retention_count	Kept	Reason
30	0	B, C, D, E	Days rule drops A (>30d). Count rule disabled.
0	3	C, D, E	Count rule keeps newest 3. Days rule disabled.
30	3	C, D, E	Days {B,C,D,E} ∩ Count {C,D,E} = {C,D,E}.
30	10	B, C, D, E	Days drops A; count keeps all 5; AND ⇒ {B,C,D,E}.
7	5	D, E (+ pin)	Days keeps {D,E}; count keeps {A..E}; AND ⇒ {D,E}. The latest-success pin is already E, so no further change.
1	0	E (+ pin)	Days alone would drop everyone older than 1 day, but the pin keeps E (latest success).

Bounded delete batch

The pruner deletes at most MaxDeletePerTick = 100 archives per config per tick. When the cap is hit, the pruner emits backup.retention.batch_truncated with the remaining pending_count; the next minute's tick continues draining.

Two-phase delete (storage first, DB row second)

The two-phase delete proceeds as follows:

1. Storage delete first. Local destinations: os.Remove, ENOENT treated as success. Cloud destinations (when wired): provider DELETE, 404 treated as success. A failure here leaves the DB row in place; the next tick retries.
2. DB row delete second. A failure here orphans the storage object; a future daily sweeper will reconcile.

The invariant: a stale DB row pointing at an existing storage object is the safe failure mode; a missing DB row pointing at orphaned storage is the silent-data-loss failure mode.

Encrypted-archive shredding

encryption_enabled=true only encrypts the destination credentials, not the dump file. The pruner therefore does not call out to a shredder — delete is a normal os.Remove / cloud DELETE. Operators who need secure delete should configure the destination's at-rest encryption (e.g. S3 SSE-KMS, GCS CMEK, Azure CMK).

Restore runbook

The restore endpoint (POST /api/v1/admin/backups/restore) consumes a backup_history row whose status='success' and replays it into the live database (target=primary) or into a sibling schema (target=staging_schema) in the same cluster. The handler is internal/api/backup_handler.go::Restore; the worker is internal/backup/restore.go; persistence is the backup_restores table from migration 000134_backup_restores.up.sql.

Restores are destructive. target=primary replaces every row, table, and schema in the live database with the contents of the chosen backup. Rows written after the chosen backup's timestamp are lost. There is no built-in undo.

Confirmation model

Every restore request must satisfy a layered confirmation. The handler rejects each missing layer with a 400 + machine-readable error_code:

• confirm — the literal string "RESTORE" (uppercase, no quotes around the field on the wire). Boolean true from the legacy stub is rejected as confirm_format_invalid.
• target — "primary" or "staging_schema". Required. There is no default — a default that destroys data is a footgun.
• backup_id — UUID of a backup_history row whose status='success' and whose storage_path is non-empty and readable on the worker host.

When target=primary, additionally:

• acknowledge_data_loss — must be true. Acknowledges that rows written after the backup will be discarded.
• expected_database_name — the operator types the database name. The worker compares against current_database() and returns 409 database_name_mismatch on disagreement. This is the "are you in the right environment" guard.

Optional:

• force_terminate — when true, the worker calls pg_terminate_backend(pid) on remaining non-self sessions before proceeding. Rejected with force_terminate_not_allowed when target=staging_schema.
• Idempotency-Key HTTP header — replays the same key within the lookback window return the original backup_restores row instead of starting a new job.

Step-by-step: target=primary (in-place replace)

1. Pick the backup, then verify it locally.

   vetrix-cli backup history <config-id> --status success --output json | jq '.'
   

   Note the id of the chosen backup_history row, the started_at timestamp, and the storage_path.

2. Confirm the worker can read the archive. For a local destination, shell into the server host and stat the file. For a cloud destination, confirm the credentials in the matching config still work — see §7.

3. Schedule a maintenance window. A target=primary restore is destructive; the API should not be serving live traffic during the window. The endpoint refuses to proceed when active non-restore sessions are attached unless force_terminate=true. The error code is restore_blocked_active_sessions; the response body includes a sample of application_name values.

4. Trigger the restore. Using the CLI (recommended):

   vetrix-cli backup restore <history-id> \
     --target primary \
     --acknowledge-data-loss \
     --expected-database-name vetrix_prod \
     --idempotency-key "$(uuidgen)" \
     --force-terminate
   

   The CLI prompts the operator to type RESTORE (skip with --yes); the server returns 202 Accepted and the CLI then polls GET /api/v1/admin/backups/restore/{id} every 2 s until the row reaches a terminal status (success, failed, cancelled, aborted).

   Or via curl:

   curl -X POST https://<vetrix-host>/api/v1/admin/backups/restore \
     -H "Authorization: Bearer <admin-token>" \
     -H "Content-Type: application/json" \
     -H "Idempotency-Key: $(uuidgen)" \
     -d '{
       "backup_id": "0c2c1a37-…",
       "confirm": "RESTORE",
       "target": "primary",
       "acknowledge_data_loss": true,
       "expected_database_name": "vetrix_prod",
       "force_terminate": true
     }'
   

5. Watch the restore progress.

   vetrix-cli backup restore <history-id> --no-wait   # returns immediately
   curl https://<vetrix-host>/api/v1/admin/backups/restore/<restore-id> \
     -H "Authorization: Bearer <admin-token>"
   

   Statuses: pending → running → success | failed | cancelled | aborted.

6. After success:

   • Run database migrations if the binary version has advanced past the backup's schema_migrations.version. The restore worker does not run migrations.
   • Restart application connections; the existing pool may hold cached prepared statements that no longer match.
   • Verify a few critical tables (SELECT count(*) FROM users;, etc.).

7. After failed:

   • Read error_code and error_message from the restore row.
   • The database is in whatever partially-applied state Postgres landed in. psql --single-transaction on plain dumps means many failures roll back cleanly; failures during pg_restore against a custom-format archive can leave the DB in a mixed state.
   • Decide: re-run the same restore (different Idempotency-Key), pick a different backup, or roll forward via your own psql recovery.

Step-by-step: target=staging_schema (inspection-only)

The staging-schema flow restores into a sibling schema named restore_<short-history-id>_<UTC-timestamp> in the same cluster. It does not swap the running app over; it never touches existing schemas; it does not require force_terminate or active-session checks. Use it to inspect what's in a backup, run ad-hoc queries against it, or pg_dump it back out.

vetrix-cli backup restore <history-id> \
  --target staging_schema \
  --idempotency-key "$(uuidgen)"

After success, connect as a privileged user and:

SET search_path TO restore_0c2c1a37_20260428T143055;
SELECT * FROM information_schema.tables WHERE table_schema = current_schema();

The schema is not auto-cleaned. Drop it manually when you're done:

DROP SCHEMA restore_0c2c1a37_20260428T143055 CASCADE;

Concurrency rules

• At most one restore (pending or running) is permitted across the entire instance. A second POST /restore returns 409 restore_in_progress with the conflicting restore_id in Location: and the response body.
• A target=primary restore is not permitted while a backup job is running for the source backup's config. The handler rejects with restore_in_progress.
• A target=staging_schema restore is permitted concurrently with backup jobs for unrelated configs.

Idempotency

Pass an Idempotency-Key HTTP header on every POST /restore. Replays of the same key within the lookback window return the original record without starting a new restore. The pgx-backed store enforces uniqueness via a partial unique index on backup_restores.idempotency_key.

A second request with the same Idempotency-Key but a different body returns 422 idempotency_key_replay_mismatch.

Error taxonomy (selected)

The full code list is in internal/backup/errors.go. Common ones:

HTTP	error_code	Meaning
400	confirm_format_invalid	confirm was not the literal string "RESTORE".
400	acknowledge_data_loss_required	target=primary without acknowledge_data_loss=true.
400	expected_database_name_required	target=primary without expected_database_name.
400	force_terminate_not_allowed	force_terminate set with target=staging_schema.
404	backup_id_invalid	No matching backup_history row.
409	backup_not_successful	Source backup row's status != 'success'.
409	restore_in_progress	Another restore is pending or running. Location: header carries the conflicting restore id.
409	database_name_mismatch	expected_database_name did not match current_database().
422	idempotency_key_replay_mismatch	Same key, different body.
500	restore_worker_error	pg_restore / psql returned non-zero, decryption failed, etc. error_message carries the trimmed worker output.

CLI reference

The CLI binary is vetrix-cli (cmd/vetrix-cli/); the backup verbs are split across cmd/vetrix-cli/cmd/backup_*.go. Every backup command requires an admin bearer token.

Global flags

Inherited by every subcommand. Source: cmd/vetrix-cli/cmd/root.go.

Flag	Env	Default	Description
--server	VETRIX_SERVER	(none)	Vetrix API base URL.
--token	VETRIX_TOKEN	(none)	Admin bearer token.
--token-file	VETRIX_TOKEN_FILE	(none)	Path to a file containing the bearer token. Resolved lazily.
--insecure		false	Skip TLS verification. Warns on stderr when set.
-o, --output		table	json, table, or yaml.
--timeout		60s	HTTP request timeout.
-v, --verbose		false	Increase log verbosity.
-h, --help			Print help.

vetrix-cli backup list

Synopsis: vetrix-cli backup list [--limit N] [--offset N]

Wraps GET /api/v1/admin/backups. Lists backup configurations.

Flag	Default	Description
--limit	0	Page size (1–100; server defaults to 20).
--offset	0	Page offset.

vetrix-cli backup get

Synopsis: vetrix-cli backup get <id>

Wraps GET /api/v1/admin/backups/{id}. No flags beyond the global set.

vetrix-cli backup create

Synopsis: vetrix-cli backup create --name … --schedule … [other flags]

Wraps POST /api/v1/admin/backups. --name and --schedule are required.

Flag	Default	Description
--name	(req.)	Human-readable label.
--schedule	(req.)	5-field cron expression. Validated server-side.
--backup-type	full	full, schema_only, or incremental. Sent verbatim to the server.
--destination-type	local	local, s3, gcs, azure (server canonical name is azure_blob).
--destination-config	(empty)	JSON string with destination-specific options. See §3.
--retention-days	30	Days to retain backup archives.
--retention-count	0	Max number of archives to retain (0 = disabled).
--compression	gzip	gzip, zstd, or none.
--encryption	false	Enable AES-GCM encryption of destination_config.
--notify-on-success	false	Emit notification on successful run.
--notify-on-failure	true	Emit notification on failed run.
--no-notify-on-failure	false	Disable failure notification (overrides --notify-on-failure).

vetrix-cli backup update

Synopsis: vetrix-cli backup update <id> [flags]

Wraps PATCH /api/v1/admin/backups/{id}. Only flags the operator actually passes are sent; the server merges them into the existing row. Flag set is the same as create, plus --enabled to toggle the enabled column. At least one flag is required.

vetrix-cli backup delete

Synopsis: vetrix-cli backup delete <id> [--purge] [--yes]

Wraps DELETE /api/v1/admin/backups/{id}. Prompts for an interactive yes confirmation unless --yes is set. --purge adds ?purge=true to the URL; the server-side file purge is a no-op today (best-effort).

Flag	Default	Description
--purge	false	Also remove stored backup files (best-effort no-op today).
--yes	false	Skip the interactive confirmation prompt.

vetrix-cli backup trigger

Synopsis: vetrix-cli backup trigger <id> [--wait] [--poll-interval D]

Wraps POST /api/v1/admin/backups/{id}/trigger. Returns the freshly-created history row (status="running") immediately. The server returns 409 if a backup or pruner is already running for the same config (the advisory lock is the source of truth).

Flag	Default	Description
--wait	false	Poll history until the run reaches success or failed.
--poll-interval	5s	Poll interval when --wait is set.

vetrix-cli backup history

Synopsis: vetrix-cli backup history <id> [--limit N] [--offset N] [--status S]

Wraps GET /api/v1/admin/backups/{id}/history. The --status flag filters client-side today (the server does not yet accept the query parameter).

Flag	Default	Description
--limit	0	Page size (1–100; server defaults to 20).
--offset	0	Page offset.
--status	(any)	Client-side filter: pending, running, success, failed.

vetrix-cli backup restore

Synopsis: vetrix-cli backup restore <history-id> [flags]

Wraps POST /api/v1/admin/backups/restore plus polling GET /api/v1/admin/backups/restore/{id}. Destructive — see §5.

Flag	Default	Description
--yes	false	Skip the interactive RESTORE prompt. The CLI still sends confirm: "RESTORE" to the server.
--target	primary	primary (replace live DB) or staging_schema (sibling schema).
--expected-database-name	(empty)	Required for target=primary. Compared against current_database() server-side.
--acknowledge-data-loss	false	Required for target=primary. Acknowledges that rows written after the backup will be discarded.
--force-terminate	false	Forcibly disconnect non-restore sessions before restoring (target=primary only).
--idempotency-key	(empty)	Sent as the Idempotency-Key HTTP header (24 h replay window).
--poll-interval	2s	Poll cadence while waiting for terminal status.
--no-wait	false	Return immediately after the server accepts (202); skip polling.

The CLI exits non-zero for any terminal status other than success and prints error_code / error_message.

Troubleshooting

The two backup tables and the new restore table are documented in detail above; this section is the operator's first stop for "the dashboard says X, what does that actually mean?"

Inspecting backup_configs

-- All configs, summarised.
SELECT id, name, schedule_cron, destination_type,
       retention_days, retention_count,
       encryption_enabled, enabled, created_at
FROM backup_configs
ORDER BY created_at DESC;

-- Configs with retention fully disabled (pruner skips them).
SELECT id, name, retention_days, retention_count
FROM backup_configs
WHERE retention_days = 0 AND retention_count = 0;

-- Configs whose destination_config is encrypted but whose key is suspect
-- (e.g. mid-rotation). Pair with cmd/server/main.go::countSecretEncKeyDependencies.
SELECT id, name, destination_type, length(destination_config) AS cipher_len
FROM backup_configs
WHERE encryption_enabled = TRUE AND destination_config <> '';

Inspecting backup_history

-- Most-recent runs across all configs.
SELECT h.id, c.name, h.status, h.started_at, h.finished_at,
       h.duration_ms, h.size_bytes, h.error_message
FROM backup_history h
JOIN backup_configs c ON c.id = h.config_id
ORDER BY h.started_at DESC
LIMIT 50;

-- Stuck "running" rows (scheduler crash detection — design §4 stale-lock recovery).
SELECT h.id, c.name, h.started_at,
       NOW() - h.started_at AS age
FROM backup_history h
JOIN backup_configs c ON c.id = h.config_id
WHERE h.status = 'running'
  AND h.started_at < NOW() - INTERVAL '1 hour'
ORDER BY h.started_at;

-- Failed-only configs (the days rule still prunes these; the count rule does not).
SELECT c.name, COUNT(*) AS failed_runs, MAX(h.started_at) AS last_attempt
FROM backup_history h
JOIN backup_configs c ON c.id = h.config_id
WHERE h.status = 'failed'
GROUP BY c.name
HAVING bool_and(h.status = 'failed')
ORDER BY last_attempt DESC;

-- Total bytes per config (sum of size_bytes on successful runs).
SELECT c.name, COUNT(*) AS successful_runs,
       pg_size_pretty(SUM(h.size_bytes)) AS total_bytes
FROM backup_history h
JOIN backup_configs c ON c.id = h.config_id
WHERE h.status = 'success' AND h.size_bytes IS NOT NULL
GROUP BY c.name
ORDER BY SUM(h.size_bytes) DESC;

Inspecting backup_restores

-- All recent restore attempts.
SELECT id, backup_history_id, target, target_schema, status,
       started_at, finished_at, duration_ms, bytes_read,
       expected_database_name, error_code, error_message
FROM backup_restores
ORDER BY created_at DESC
LIMIT 50;

-- The single in-progress restore (the handler's single-running guard).
SELECT id, target, status, started_at, expected_database_name
FROM backup_restores
WHERE status IN ('pending', 'running');

-- Idempotency-key replays (one row per key; the partial unique index ensures it).
SELECT idempotency_key, COUNT(*) AS attempts,
       MAX(created_at) AS last_attempt
FROM backup_restores
WHERE idempotency_key IS NOT NULL
GROUP BY idempotency_key
ORDER BY last_attempt DESC;

-- Find restores that terminated with a database-name mismatch
-- (operator typed the wrong expected_database_name).
SELECT id, expected_database_name, error_message, created_at
FROM backup_restores
WHERE error_code = 'database_name_mismatch'
ORDER BY created_at DESC;

-- Restores blocked by active sessions (target=primary, no force_terminate).
SELECT id, expected_database_name, error_message, created_at
FROM backup_restores
WHERE error_code = 'restore_blocked_active_sessions'
ORDER BY created_at DESC;

Common failure modes

SECRET_ENC_KEY is unset, encrypted configs exist. The server refuses to start with a message naming the count of encrypted backup configs / webhook secrets / admin settings. Set the key (or rotate per security.md) before restarting.

# Count the encrypted-data dependencies (matches the boot-gate query).
docker exec mydev_postgres psql -U postgres -c \
  "SELECT COUNT(*) FROM backup_configs WHERE encryption_enabled = TRUE;"

A backup job claims 409 conflict on every trigger. The advisory lock is held by another goroutine (a still-running cron-driven job, or the pruner). Look for an in-progress history row:

SELECT id, started_at, NOW() - started_at AS age
FROM backup_history
WHERE config_id = '<config-id>' AND status = 'running';

If the row's age exceeds 1 hour, the worker likely crashed and Postgres has already released the advisory lock — manually transition the row to failed:

UPDATE backup_history
SET status = 'failed',
    finished_at = NOW(),
    error_message = 'scheduler crash detected (manual cleanup)'
WHERE id = '<history-id>' AND status = 'running';

Pruner does not delete archives that should be deleted. Check the events recorded by the pruner (backup.retention.pruned / skipped / delete_failed / batch_truncated are emitted through the analytics recorder). Common causes:

• Both retention knobs are 0 → fully disabled, by design.
• No successful archive has ever landed → pruner refuses to prune (the latest-success pin needs a target).
• The lock is held by a long-running backup job → next tick will retry.
• Storage delete returned a permission error → the DB row is left in place; next tick retries. Inspect the storage destination's permissions (e.g. for S3, the IAM principal needs s3:DeleteObject on the bucket prefix).

Restore returns restore_blocked_active_sessions. A target=primary restore is blocked because non-restore sessions are attached. Either drain traffic (preferred) or pass --force-terminate to the CLI. The endpoint's response body contains a sample of application_name values; look for unexpected long-lived clients.

Restore returns database_name_mismatch. The expected_database_name the operator passed does not match current_database() on the worker. This guard exists exactly to catch "restore production backup into staging" type mistakes — verify the environment, then re-issue with the correct name.

Cloud destination upload fails with auth_failed / permission_denied. Adapter-level errors are wrapped with the type-and-code sentinels in internal/backup/destination/destination.go. Confirm the config's credentials are still valid (rotate if recently changed in the provider) and that the principal has at least PutObject (S3) / storage.objects.create (GCS) / Blob Contributor (Azure) on the configured prefix. The "test connection" UI button issues a probe (small PUT

• DELETE under .vetrix-probe/<uuid>) without touching real archive objects.

See also

• ../registry/admin-cli.md — operator CLI reference for the container registry (vetrix-cli registry); follows the same global-flag and exit-code conventions as vetrix-cli backup.
• security.md — SECRET_ENC_KEY rotation guidance.
• mirrors.md — sibling instance-level subsystem; same operator patterns.