Backup and recovery

What CommonLayer must back up, how, how to restore, and how to prove that a restore is complete and consistent. The PostgreSQL database plus the two object-storage buckets plus one encryption key fully reconstruct the platform; everything else can be regenerated.

Status (WP-70g). The scripts in scripts/ops/ were exercised against PostgreSQL 18.6 and MinIO in the build sandbox: encrypted backups, a restore into a scratch database owned by the owner role, clctl migrate re-granting the runtime role, clctl db check-role, clctl ledger verify and clctl audit verify as the runtime role, the object inventory, clctl secrets rotate on a restored copy, and the preservation check (results in WP-70g). Continuous archiving (PITR) on a managed service or CloudNativePG was not exercised.

What to back up

Database

Continuous archiving (preferred)

Use the managed service's automated backups with point-in-time recovery, or, with the postgresql-ha chart, CloudNativePG's backups (Barman Cloud Plugin to an object store, or volume snapshots). The single-instance postgresql chart and image expose POSTGRES_ARCHIVE_COMMAND and POSTGRES_RESTORE_COMMAND for WAL archiving; the image tests verified that archiving and a restore from a base backup plus archived WAL work (projects/postgresql/tests/postgresql.sh). The CloudNativePG backup paths have not been tested (ADR-0015).

The achievable recovery point is set by the WAL archive interval (archive_timeout) and the backup retention you configure; the operating entity decides the targets and records them in its own runbook.

Logical dumps

In addition, take regular logical dumps (they survive a corrupted physical backup and are portable across major versions). scripts/ops/backup-database.sh does it end to end:

CL_BACKUP_AGE_RECIPIENTS_FILE=backup-recipients.txt \
CL_BACKUP_S3_ENDPOINT=https://s3.example.net CL_BACKUP_S3_ACCESS_KEY_ID=… CL_BACKUP_S3_SECRET_ACCESS_KEY=… \
scripts/ops/backup-database.sh --destination s3://commonlayer-backups/database \
  --database-url "postgres://commonlayer_owner:…@db.internal:5432/commonlayer"

It runs pg_dump -Fc (from the pinned postgres image when the local client is older than the server), streams the dump through age (the plaintext never touches the disk), uploads commonlayer-<db>-<time>.dump.age with a JSON manifest holding its SHA-256, and prints the backup's name. Take it as the owner (or a superuser), which can read every object of the schema. Backup storage credentials are CL_BACKUP_S3_*, so an unrelated AWS profile is never sent to the backup endpoint. make ops-backup does this for the containerised stack.

Restoring a logical dump

Restore into an empty database, owned by the owner role:

CL_BACKUP_AGE_IDENTITY_FILE=backup-identity.txt \
scripts/ops/restore-database.sh --source s3://commonlayer-backups/database --latest \
  --target-url "postgres://postgres:…@db.internal:5432/commonlayer_restore" --create --owner commonlayer_owner

The script checks the encrypted file's SHA-256 against the manifest before decrypting, creates the database OWNER commonlayer_owner, and restores with pg_restore --no-owner --no-privileges --role=commonlayer_owner --single-transaction, so every object belongs to the owner and nothing of the source's grants is carried over. It refuses a non-empty target unless --clean.

pg_restore loads table data before it creates triggers, so the ledger and append-only triggers do not interfere with a full restore. Do not run a data-only restore into a database that already has the CommonLayer schema: the ledger entry trigger would apply every entry to the balances a second time, and the balance guard refuses direct writes to ledger_balances (CL005). The required extensions (citext, pg_trgm, btree_gist, pgcrypto) are trusted extensions the owner may create.

Then re-grant the runtime role and verify as it: run clctl migrate with the owner URL as database.migration_url and the runtime URL as database.url (a no-op for the schema if the dump came from the same version; it grants the runtime role, which must exist on the server), then clctl db check-role with the runtime URL. Point the platform at the restored database and verify (below).

Object storage

• Enable versioning on both buckets, so overwritten or deleted objects can be restored to any earlier version, and replication to a second region or provider.
• Lifecycle rules must never expire current versions in these buckets. Expiring old noncurrent versions after your recovery window is fine.
• The registry's retention executor deletes manifests only through the registry API and, in the default configuration, never deletes anything that was promoted. Distribution's offline garbage collection deletes unreferenced blobs; with versioning they remain recoverable.

To restore an object (an SBOM, a blob) restore its previous version in the bucket. For the registry, prefer restoring the whole repository path's objects to a point in time; partial restores can leave manifests whose blobs are missing, which the preservation check reports.

Consistency between database and storage

The database holds storage keys and digests; the buckets hold the content. After restoring the database to time T, restore the buckets to their state at T or later (later is fine: extra objects are unreferenced). Then:

1. registry.verify_preservation (daily, or run the job) checks every preserved digest's manifest and blobs in the registry and that the manifest still hashes to its digest.
2. Submissions that were in flight at T are resumed by re-submitting the same digest.
3. Provider events received after T are redelivered by the provider on request, or appear in reconciliation's provider check; GitHub deliveries after T can be redelivered from the App's settings.

Verifying a restore

clctl migrate                         # owner: schema at the expected version, runtime role granted
clctl db check-role                   # runtime role: least privilege
clctl ledger verify                   # runtime role: zero sum, balances equal entries, subledger
clctl audit verify                    # runtime role: audit log matches its checkpoint hash chain
clctl registry verify-preservation    # every preserved digest still in the registry, hashing to itself

Then run a reconciliation (POST /api/v1/admin/reconciliation/run, or wait for the hourly job) and pull one image by digest from the registry.

A restore is not done until these checks pass. Practise it: scripts/ops/restore-drill.sh restores the latest backup into a scratch database as the owner (--owner), re-grants and checks the runtime role (--runtime-url), runs the ledger and audit checks as the runtime role, compares the object inventory (every storage key the restored database references must exist in the artifacts bucket; unreferenced objects are counted, not failed), writes a report to var/ops/ and drops the scratch database. make ops-restore-drill runs it against the containerised stack; .github/workflows/ops.yml runs it weekly.

The encryption key

secrets.encryption_key encrypts outbound webhook signing secrets, private security report bodies and messages, and OAuth PKCE verifiers. It is not in the database backups by design. Keep at least two copies in separate secret stores. If it is lost:

• webhook endpoints keep working only after their owners rotate the secret (PATCH /api/v1/webhooks/{id} with {"rotate_secret": true});
• private-report bodies and messages cannot be recovered;
• in-flight OAuth logins fail once and can be retried.

Rotation is supported (secrets.previous_encryption_keys and clctl secrets rotate, see the operations handbook); exercised on a restored copy: with only the new key the command reports the values it cannot decrypt and exits 1; with the previous key it re-encrypts them, a second run finds them current, and afterwards the new key alone reads them.