Add setup to backup database and schedule weekly checks to ensure backups are valid #171

New issue

Open

opened 2026-04-09 00:58:00 +00:00 by ryansquared · 1 comment

ryansquared commented

2026-04-09 00:58:00 +00:00

(Migrated from codeberg.org)

Goal

Back up the platform database hourly to append-only S3 storage, retain recoverable backups for 14 days, and encrypt to all designated team PGP public keys before anything is uploaded. Preserve this issue's original requirement for weekly restore checks.

Related infrastructure tracking: #312. This offsite, client-encrypted backup plan does not depend on a migration to RDS; managed database backups can be complementary.

Proposed plan

Hourly, consistent database backups. Use a PostgreSQL-aware dump, not a copy of live database files. Start with compressed, independent full backups: the database is expected to be small, but measure backup size, runtime and storage/request costs before assuming that. Avoid overlapping runs and detect failed or incomplete uploads.
14-day recovery window. Every retained backup must be independently restorable. If incremental backups are chosen instead, retain all full backups, increments and metadata needed to restore every recovery point in the window; a blanket 14-day object expiry must not break chains.
Append-only S3. Use unique immutable backup keys/prefixes, versioning and Object Lock with a default 14-day retention period. Explicitly select/document the retention mode. Restrict the backup identity to the minimum required upload/list permissions: it must not delete backups or versions, hide them with delete markers, bypass/shorten retention, change bucket policy/lifecycle, or replace an existing backup's authoritative contents. Validate overwrite protection rather than treating versioning alone as append-only. Lifecycle cleanup, not the backup job, expires eligible data after retention; account for noncurrent versions/delete markers and asynchronous expiry.
Encrypt locally to every approved recipient. Maintain an explicit list of verified PGP public-key fingerprints. Each designated recipient must be able to decrypt independently. The backup job needs public keys only; do not place recipients' private keys on the backup host. Fail closed if recipient configuration or encryption fails. No plaintext dump or unencrypted sensitive metadata may reach S3; protect and promptly remove any local plaintext staging.
Operational checks. Alert on failed/stale backups and failed restore checks. Keep a concise recovery runbook covering download, decryption, PostgreSQL restore, required roles/extensions and separately managed application encryption keys. Document recipient rotation and access to older retained backups.

Job execution and operations

Use the existing Docker + user-systemd deployment model: run a short-lived backup container on the database host, with no additional always-on server or Hermes dependency. These are proposed units, not an already-deployed job.

caution-db-backup.service: Type=oneshot, launching a pinned backup image on caution-network. Connect to PostgreSQL using a dedicated, least-privilege backup account and a compatible pg_dump client. Dump, compress, encrypt, upload to a unique immutable prefix, and report success only after the full backup and required metadata are uploaded. Set an explicit TimeoutStartSec; failure/timeout must stop the owned container, clean up staging and return a failing status.
caution-db-backup.timer: OnCalendar=hourly, Persistent=true. Use the same service for scheduled and manual runs so systemd prevents overlapping instances. Persistent scheduling triggers a catch-up run after downtime, not one backup for every missed hour. Ensure user lingering is enabled for the designated service account so scheduling survives logout and starts at boot.
Restricted inputs: mount approved public keys read-only; supply only dedicated database credentials and narrowly scoped S3 backup credentials. Do not pass the platform's entire .env, mount the Docker socket inside the backup container, or give it recipient private keys. Protect any plaintext staging and keep secrets out of logs.
Installation: extend platform setup to install both .service and .timer files (it currently installs services only). Enable backups explicitly after configuring the intended environment, bucket and recipients; do not auto-enable production backups in test/development setup.
Monitoring: send job failures to the alerting channel, and monitor backup freshness from outside the database host. Check for a recent complete backup, not just a successful timer invocation; host failure must still produce an alert.
Weekly restore worker: schedule separately in an isolated restore environment, with recovery credentials and securely managed decryption-key access kept off the upload host. Download from S3, decrypt, restore into a disposable database, verify schema/data and alert on failure. Include the oldest-in-window recovery point.

After implementation, configuration and installation, the operator interface should be:

# Enable hourly scheduling
systemctl --user enable --now caution-db-backup.timer
# Run one backup immediately, through the same service
systemctl --user start caution-db-backup.service
# Inspect schedule and logs
systemctl --user list-timers caution-db-backup.timer
journalctl --user -u caution-db-backup.service

A later Kubernetes migration can reuse the backup image in an hourly CronJob with equivalent overlap, timeout and monitoring controls; Kubernetes is not a prerequisite for this work.

Tool evaluation

Evaluate Duplicity, including its S3 backend and compatibility with the append-only permissions above. Its --encrypt-key option can be repeated to encrypt to multiple public keys, e.g. --encrypt-key <fingerprint-A> --encrypt-key <fingerprint-B>.

The originally suggested name was "ruplicity". The project with that name describes itself as a library for exploring Duplicity backups, not a backup/restore tool; Duplicity appears to be the intended candidate. Keep the implementation simple and verify the selected tool/version rather than assuming append-only backend compatibility.

Acceptance criteria

Hourly runs produce complete encrypted, database-consistent backups in S3; failures or stale backups trigger an alert.
Each configured recipient can independently decrypt a test backup, and the uploader has no recipient private keys.
Tests with the actual backup identity prove it cannot delete, replace or hide retained backups, shorten retention or alter protection policies.
The full 14-day window remains recoverable as lifecycle expiry proceeds; retention does not leave broken incremental chains or permanently accumulating versions.
Weekly automated restore checks retrieve a backup from S3, decrypt it in an isolated restore environment, restore to a disposable database and verify schema/data. Include an oldest-in-window recovery point, not only the latest backup. Recovery credentials remain separate from the uploader.
Record measured backup size/runtime, estimated storage/request costs, restore duration and a tested recovery runbook.
The installed timer and manual start use the same service; verify scheduling after reboot/logout, no overlapping runs, and timeout/failure cleanup of the owned container and plaintext staging.
External freshness monitoring alerts when the backup host or scheduler is unavailable, independently of host-local job-failure alerts.

## Goal Back up the platform database hourly to append-only S3 storage, retain recoverable backups for 14 days, and encrypt to all designated team PGP public keys before anything is uploaded. Preserve this issue's original requirement for weekly restore checks. Related infrastructure tracking: #312. This offsite, client-encrypted backup plan does not depend on a migration to RDS; managed database backups can be complementary. ## Proposed plan - **Hourly, consistent database backups.** Use a PostgreSQL-aware dump, not a copy of live database files. Start with compressed, independent full backups: the database is expected to be small, but measure backup size, runtime and storage/request costs before assuming that. Avoid overlapping runs and detect failed or incomplete uploads. - **14-day recovery window.** Every retained backup must be independently restorable. If incremental backups are chosen instead, retain all full backups, increments and metadata needed to restore every recovery point in the window; a blanket 14-day object expiry must not break chains. - **Append-only S3.** Use unique immutable backup keys/prefixes, versioning and Object Lock with a default 14-day retention period. Explicitly select/document the retention mode. Restrict the backup identity to the minimum required upload/list permissions: it must not delete backups or versions, hide them with delete markers, bypass/shorten retention, change bucket policy/lifecycle, or replace an existing backup's authoritative contents. Validate overwrite protection rather than treating versioning alone as append-only. Lifecycle cleanup, not the backup job, expires eligible data after retention; account for noncurrent versions/delete markers and asynchronous expiry. - **Encrypt locally to every approved recipient.** Maintain an explicit list of verified PGP public-key fingerprints. Each designated recipient must be able to decrypt independently. The backup job needs public keys only; do not place recipients' private keys on the backup host. Fail closed if recipient configuration or encryption fails. No plaintext dump or unencrypted sensitive metadata may reach S3; protect and promptly remove any local plaintext staging. - **Operational checks.** Alert on failed/stale backups and failed restore checks. Keep a concise recovery runbook covering download, decryption, PostgreSQL restore, required roles/extensions and separately managed application encryption keys. Document recipient rotation and access to older retained backups. ## Job execution and operations Use the existing Docker + user-systemd deployment model: run a short-lived backup container on the database host, with no additional always-on server or Hermes dependency. These are proposed units, not an already-deployed job. - **`caution-db-backup.service`:** `Type=oneshot`, launching a pinned backup image on `caution-network`. Connect to PostgreSQL using a dedicated, least-privilege backup account and a compatible `pg_dump` client. Dump, compress, encrypt, upload to a unique immutable prefix, and report success only after the full backup and required metadata are uploaded. Set an explicit `TimeoutStartSec`; failure/timeout must stop the owned container, clean up staging and return a failing status. - **`caution-db-backup.timer`:** `OnCalendar=hourly`, `Persistent=true`. Use the same service for scheduled and manual runs so systemd prevents overlapping instances. Persistent scheduling triggers a catch-up run after downtime, not one backup for every missed hour. Ensure user lingering is enabled for the designated service account so scheduling survives logout and starts at boot. - **Restricted inputs:** mount approved public keys read-only; supply only dedicated database credentials and narrowly scoped S3 backup credentials. Do not pass the platform's entire `.env`, mount the Docker socket inside the backup container, or give it recipient private keys. Protect any plaintext staging and keep secrets out of logs. - **Installation:** extend platform setup to install both `.service` and `.timer` files (it currently installs services only). Enable backups explicitly after configuring the intended environment, bucket and recipients; do not auto-enable production backups in test/development setup. - **Monitoring:** send job failures to the alerting channel, and monitor backup freshness from outside the database host. Check for a recent complete backup, not just a successful timer invocation; host failure must still produce an alert. - **Weekly restore worker:** schedule separately in an isolated restore environment, with recovery credentials and securely managed decryption-key access kept off the upload host. Download from S3, decrypt, restore into a disposable database, verify schema/data and alert on failure. Include the oldest-in-window recovery point. After implementation, configuration and installation, the operator interface should be: ```sh # Enable hourly scheduling systemctl --user enable --now caution-db-backup.timer # Run one backup immediately, through the same service systemctl --user start caution-db-backup.service # Inspect schedule and logs systemctl --user list-timers caution-db-backup.timer journalctl --user -u caution-db-backup.service ``` A later Kubernetes migration can reuse the backup image in an hourly CronJob with equivalent overlap, timeout and monitoring controls; Kubernetes is not a prerequisite for this work. ## Tool evaluation Evaluate **Duplicity**, including its S3 backend and compatibility with the append-only permissions above. Its [`--encrypt-key` option can be repeated](https://manpages.debian.org/bookworm/duplicity/duplicity.1.en.html) to encrypt to multiple public keys, e.g. `--encrypt-key <fingerprint-A> --encrypt-key <fingerprint-B>`. The originally suggested name was "ruplicity". The [project with that name](https://github.com/mbrt/ruplicity) describes itself as a library for exploring Duplicity backups, not a backup/restore tool; Duplicity appears to be the intended candidate. Keep the implementation simple and verify the selected tool/version rather than assuming append-only backend compatibility. ## Acceptance criteria - [ ] Hourly runs produce complete encrypted, database-consistent backups in S3; failures or stale backups trigger an alert. - [ ] Each configured recipient can independently decrypt a test backup, and the uploader has no recipient private keys. - [ ] Tests with the actual backup identity prove it cannot delete, replace or hide retained backups, shorten retention or alter protection policies. - [ ] The full 14-day window remains recoverable as lifecycle expiry proceeds; retention does not leave broken incremental chains or permanently accumulating versions. - [ ] **Weekly automated restore checks** retrieve a backup from S3, decrypt it in an isolated restore environment, restore to a disposable database and verify schema/data. Include an oldest-in-window recovery point, not only the latest backup. Recovery credentials remain separate from the uploader. - [ ] Record measured backup size/runtime, estimated storage/request costs, restore duration and a tested recovery runbook. - [ ] The installed timer and manual start use the same service; verify scheduling after reboot/logout, no overlapping runs, and timeout/failure cleanup of the owned container and plaintext staging. - [ ] External freshness monitoring alerts when the backup host or scheduler is unavailable, independently of host-local job-failure alerts.

ryansquared commented

2026-04-13 20:36:55 +00:00

(Migrated from codeberg.org)

move to RDS, use RDS backups

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference

public/platform#171

No description provided.

Rows
Columns