Database Backup & Restore

On this page

Overview

Canopy uses PostgreSQL for all persistent storage. Per ADR-001 (program service isolation), each benefit program maintains its own database on a dedicated PostgreSQL instance, while shared services share a single instance. This runbook covers backup, restore, point-in-time recovery, and post-restore verification for all Canopy databases.

Database Inventory

Shared PostgreSQL (port 5432)

The shared postgres container hosts databases for services that do not handle FTI or program-specific sensitive data:

Database Service

canopy_rules

canopy-rules

canopy_persons

canopy-persons

canopy_applications

canopy-applications

canopy_eligibility

canopy-eligibility

canopy_enrollment

canopy-enrollment

canopy_renewals

canopy-renewals

canopy_notices

canopy-notices

canopy_appeals

canopy-appeals

canopy_reporting

canopy-reporting

canopy_security

canopy-security

Isolated PostgreSQL — SNAP (port 5432)

The postgres-snap container hosts:

Database Service

canopy_snap

canopy-snap

Isolated PostgreSQL — TANF (port 5432)

The postgres-tanf container hosts:

Database Service

canopy_tanf

canopy-tanf
WARNING
canopy_tanf contains FTI-designated tables (fti_audit_log, fti_tax_data). See FTI Data Handling for mandatory encryption requirements.

Isolated PostgreSQL — Medicaid (port 5432)

The postgres-medicaid container hosts:

Database Service

canopy_medicaid

canopy-medicaid
WARNING
canopy_medicaid contains FTI-designated tables (fti_audit_log, fti_tax_data). See FTI Data Handling for mandatory encryption requirements.

Backup Procedure

Prerequisites

  • pg_dump available (same major version as the target PostgreSQL instance)

  • Sufficient disk space for dump files

  • For FTI databases: GPG key or AES-256 encryption tooling configured

Shared PostgreSQL Databases

Back up each database on the shared instance. Adjust PGHOST, PGPORT, and PGUSER as appropriate for your environment.

#!/usr/bin/env bash
set -euo pipefail

TIMESTAMP=$(date -u +%Y%m%dT%H%M%SZ)
BACKUP_DIR="/backups/shared/${TIMESTAMP}"
mkdir -p "${BACKUP_DIR}"

SHARED_DATABASES=(
  canopy_rules
  canopy_persons
  canopy_applications
  canopy_eligibility
  canopy_enrollment
  canopy_renewals
  canopy_notices
  canopy_appeals
  canopy_reporting
  canopy_security
)

for DB in "${SHARED_DATABASES[@]}"; do
  pg_dump \
    --host=postgres \
    --port=5432 \
    --username=canopy \
    --format=custom \
    --compress=9 \
    --file="${BACKUP_DIR}/${DB}.dump" \
    "${DB}"
  echo "Backed up ${DB} -> ${BACKUP_DIR}/${DB}.dump"
done

Isolated SNAP Database

pg_dump \
  --host=postgres-snap \
  --port=5432 \
  --username=canopy \
  --format=custom \
  --compress=9 \
  --file="${BACKUP_DIR}/canopy_snap.dump" \
  canopy_snap

Isolated TANF Database

pg_dump \
  --host=postgres-tanf \
  --port=5432 \
  --username=canopy \
  --format=custom \
  --compress=9 \
  --file="${BACKUP_DIR}/canopy_tanf.dump" \
  canopy_tanf
IMPORTANT
Immediately encrypt the TANF dump file. See FTI Data Handling.

Isolated Medicaid Database

pg_dump \
  --host=postgres-medicaid \
  --port=5432 \
  --username=canopy \
  --format=custom \
  --compress=9 \
  --file="${BACKUP_DIR}/canopy_medicaid.dump" \
  canopy_medicaid
IMPORTANT
Immediately encrypt the Medicaid dump file. See FTI Data Handling.

FTI Data Handling

The canopy_tanf and canopy_medicaid databases contain Federal Tax Information (FTI) subject to IRS Publication 1075 safeguards. The following tables are FTI-designated:

  • fti_audit_log — immutable audit trail of all FTI access

  • fti_tax_data — cached tax return data from IRS interfaces

Mandatory Controls

Control Requirement

Encryption at rest

All backup files containing FTI tables must be encrypted with AES-256 (or equivalent FIPS 140-2 validated algorithm) immediately after creation. Unencrypted FTI dumps must never persist on disk.

Access control

Backup files containing FTI data must be stored in a location accessible only to authorized personnel with a current IRS background investigation.

Audit logging

Every backup and restore operation involving FTI data must be logged to the security audit trail (canopy-security).

Retention

FTI backup files must be destroyed when no longer needed, and in no case retained longer than the IRS-mandated retention period. Destruction must be logged.

Transport encryption

If backup files are transferred across a network, TLS 1.2+ or equivalent transport encryption is required.

Encrypting FTI Backups

# Encrypt with GPG (AES-256)
gpg --symmetric --cipher-algo AES256 \
  --output "${BACKUP_DIR}/canopy_tanf.dump.gpg" \
  "${BACKUP_DIR}/canopy_tanf.dump"

gpg --symmetric --cipher-algo AES256 \
  --output "${BACKUP_DIR}/canopy_medicaid.dump.gpg" \
  "${BACKUP_DIR}/canopy_medicaid.dump"

# Securely delete unencrypted dumps
shred -u "${BACKUP_DIR}/canopy_tanf.dump"
shred -u "${BACKUP_DIR}/canopy_medicaid.dump"

Decrypting FTI Backups for Restore

gpg --decrypt \
  --output "${BACKUP_DIR}/canopy_tanf.dump" \
  "${BACKUP_DIR}/canopy_tanf.dump.gpg"

gpg --decrypt \
  --output "${BACKUP_DIR}/canopy_medicaid.dump" \
  "${BACKUP_DIR}/canopy_medicaid.dump.gpg"
NOTE
Delete decrypted files immediately after the restore completes.

Restore Procedure

Step 1: Stop Application Services

Stop all Canopy services to prevent writes during restore:

cargo xtask dev stop

Step 2: Restore Databases

Restore each database using pg_restore. The --clean flag drops existing objects before recreating them.

# Shared databases
for DB in "${SHARED_DATABASES[@]}"; do
  pg_restore \
    --host=postgres \
    --port=5432 \
    --username=canopy \
    --dbname="${DB}" \
    --clean \
    --if-exists \
    --no-owner \
    "${BACKUP_DIR}/${DB}.dump"
  echo "Restored ${DB}"
done

# SNAP
pg_restore \
  --host=postgres-snap \
  --port=5432 \
  --username=canopy \
  --dbname=canopy_snap \
  --clean --if-exists --no-owner \
  "${BACKUP_DIR}/canopy_snap.dump"

# TANF (decrypt first if encrypted)
pg_restore \
  --host=postgres-tanf \
  --port=5432 \
  --username=canopy \
  --dbname=canopy_tanf \
  --clean --if-exists --no-owner \
  "${BACKUP_DIR}/canopy_tanf.dump"

# Medicaid (decrypt first if encrypted)
pg_restore \
  --host=postgres-medicaid \
  --port=5432 \
  --username=canopy \
  --dbname=canopy_medicaid \
  --clean --if-exists --no-owner \
  "${BACKUP_DIR}/canopy_medicaid.dump"

Step 3: Run Migrations

After restoring, run sqlx migrations to ensure the schema is up to date. This is a no-op if the backup already contains all migrations, but it guarantees correctness if restoring an older backup:

# Run migrations for each service crate
for SERVICE_DIR in services/canopy-*/; do
  if [ -d "${SERVICE_DIR}/migrations" ]; then
    echo "Running migrations for ${SERVICE_DIR}..."
    sqlx migrate run --source "${SERVICE_DIR}/migrations"
  fi
done

Verify migration status:

for SERVICE_DIR in services/canopy-*/; do
  if [ -d "${SERVICE_DIR}/migrations" ]; then
    echo "=== ${SERVICE_DIR} ==="
    sqlx migrate info --source "${SERVICE_DIR}/migrations"
  fi
done

All migrations should show status applied.

Step 4: Restart Services

cargo xtask dev start --shared-db

Point-in-Time Recovery

For production deployments, configure PostgreSQL continuous archiving (WAL archiving) to enable point-in-time recovery (PITR). PITR is the production rollback path for schema regressions per ADR-016 — the dev-side cargo xtask migrate snapshot/rollback pair is not the production tool.

When PITR is the right answer (vs. forward-fix migration)

Situation Right tool

A migration shipped that adds a column, indexes incorrectly, or has a typo in a default — no data was lost

Forward-fix migration. Author a new migration that corrects the schema. PITR would also discard any legitimate data committed since the bad migration; the forward fix doesn’t.

A migration accidentally `DROP COLUMN’d a populated column or `DELETE’d rows that should not have been removed

PITR. Forward-fix can’t bring back data the WAL stream already shipped a destructive change for. Restore the basebackup and replay WAL up to the moment before the destructive statement.

Application bug wrote bad data into rows (no schema change) — bad data is contained to a known time window

PITR if the bad-data volume is large, forward-fix (UPDATE/DELETE corrections) if it’s small. Bias toward forward-fix below ~10 000 rows.

Cluster compromised; integrity of all writes since time T is suspect

PITR. Roll the cluster back to T; everything after is treated as compromised.

Schema is fine, but the application’s serialised state in PostgreSQL needs to be reverted to a known-good moment for incident reproduction

PITR for the affected DB only, leave others running. Use the per-database recovery procedure below.

Pre-PITR checklist (before stopping PostgreSQL)

  1. Identify recovery target time — the moment to roll back to. This is the latest moment before the destructive event. Round down to the nearest WAL flush if uncertain — over-rolling is recoverable; under-rolling silently keeps the bad state.

  2. Communicate with on-call. PITR is a service-down operation. Post in the incident channel; freeze deploys; notify stakeholders the affected services will be unavailable for the recovery window.

  3. Snapshot current (broken) state. Take a final pg_basebackup of the broken cluster before you destroy the data directory — incident forensics may need it later. Tag it pre-pitr-broken-{timestamp}.

  4. Confirm WAL archive coverage. Verify the WAL archive contains a continuous sequence from the most-recent basebackup through the recovery target time. Gaps in WAL = no PITR; you’d be forced to restore a basebackup with no replay.

    ls /wal_archive/ | grep -v '\.backup$' | sort | head -3
ls /wal_archive/ | grep -v '\.backup$' | sort | tail -3

  5. Stop application services. Bring down every Canopy service that reads/writes the target database. Confirm zero active connections via SELECT count(*) FROM pg_stat_activity WHERE datname = '<db>' AND pid <> pg_backend_pid();.

  6. Record the recovery operator (your username), recovery target, and motivation in the incident log. The post-recovery validation step writes this back into the audit log so chain readers can trace the gap.

Single-database PITR procedure

For one Canopy database (e.g. canopy_snap). The cross-service procedure below extends this for the case where multiple databases must roll to the same wall-clock moment.

  1. Stop the affected service’s PostgreSQL instance. Per ADR-001, each program service has its own DB; stopping postgres-snap does not affect TANF / Medicaid.

    docker stop canopy-postgres-snap-1

  2. Move (don’t delete) the existing data directory so the broken state is preserved for forensics:

    docker run --rm -v canopy_postgres_snap_data:/data --user 0 alpine \
  sh -c "mv /data/18/docker /data/18/broken-$(date -u +%Y%m%dT%H%M%SZ)"

  3. Restore the most-recent basebackup taken before the recovery target time:

    docker run --rm \
  -v canopy_postgres_snap_base:/base \
  -v canopy_postgres_snap_data:/dst \
  --user 0 alpine \
  sh -c "mkdir -p /dst/18/docker && cp -a /base/. /dst/18/docker/ && \
         touch /dst/18/docker/recovery.signal && \
         chown -R 70:70 /dst/18"

    (The 70:70 UID matches the alpine postgres image’s postgres user.)

  4. Write the recovery configuration. Append to the restored data directory’s postgresql.auto.conf:

    restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '<RECOVERY_TARGET_UTC>'
recovery_target_action = 'promote'

  5. Start PostgreSQL. It replays WAL up to the target time, then promotes to read-write:

    docker start canopy-postgres-snap-1
docker logs -f canopy-postgres-snap-1 | grep -E 'recovery|consistent'

    Look for the lines:

    LOG:  starting point-in-time recovery to <target>
LOG:  consistent recovery state reached
LOG:  recovery stopping before commit of transaction <xid>, time <stop-time>
LOG:  archive recovery complete

    The "stopping before commit" line confirms WAL replay halted at the requested target.

  6. Verify the recovery succeeded before bringing application services back up — see Post-recovery validation.

Cross-service PITR (multiple databases to the same wall-clock moment)

When a coordinated incident affected multiple services (e.g. a buggy cross-service migration ran in parallel), every affected database must restore to the same wall-clock target.

  1. Pre-PITR checklist as above, but verify WAL coverage on every affected DB.

  2. Stop every affected PostgreSQL instance simultaneously. Do not roll one DB before stopping the others — partial state inconsistency is worse than total downtime.

    docker stop canopy-postgres-snap-1 canopy-postgres-tanf-1 \
            canopy-postgres-medicaid-1 canopy-postgres-1

  3. Repeat the single-database procedure for each instance with the same recovery_target_time. Each DB has its own basebackup + WAL archive but uses the same target moment.

  4. Bring DBs up in dependency order: shared postgres (canopy-persons, canopy-applications, etc.) first, then per-program DBs (postgres-snap, postgres-tanf, postgres-medicaid). The order matches `cargo xtask dev start’s health-gate sequence.

  5. Run cross-service validation before any application restart — the ADR-014 audit chain spans services and any chain break becomes immediately reportable per Pub 1075 §9.

Post-recovery validation

Run all of the following before re-enabling application traffic. Any failure means the recovery is not safe to bring online.

Hash-chain integrity (ADR-014)

Post-restore chain validation runs through the unified /v1/security/chain/* namespace (#1205, ADR-014 Amendment 9 — the old GET /v1/security/verify-chain and POST /v1/security/fti/chain-verify are deleted; the sync verify trigger no longer exists). The endpoints are service-or-admin gated; use the on-call admin token.

IMPORTANT
Dormancy caveat (pre-#1279): until the cutover the verifier is OFF by design — chain/status reports unknown → 503 and chain/verify returns 503 verifier_unavailable. That is NOT a restore failure; pre-cutover chain validation remains a manual DBA procedure (bounded ad-hoc re-hash of the affected window, per Security Operations). The steps below are the post-cutover procedure.

  1. Trigger a full manual verification per family (a durable job — never a synchronous walk; family-full covers tail + scrub + census
    manifest):

    curl -sf -X POST -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"family":"audit","loop":"family-full"}' \
  http://canopy-security:40641/v1/security/chain/verify | jq .
# → 202 {"job_id": "...", "poll_url": ".../v1/security/chain/verify-jobs/<id>"}
# FTI families likewise (since #1206 MR-3):
#   -d '{"family":"fti","service":"canopy-tanf","loop":"family-full"}'
#   -d '{"family":"fti","service":"canopy-medicaid","loop":"family-full"}'

    CLI equivalent: canopy security chain-verify --family audit --wait (trigger + poll in one step).

  2. Poll each job to completion and require state: "done" with the linked run outcome: "ok":

    curl -sf -H "Authorization: Bearer $ADMIN_TOKEN" \
  http://canopy-security:40641/v1/security/chain/verify-jobs/$JOB_ID | jq .

    state: "error" → inspect error_code; integrity_rejected means a finding LATCHED an incident → escalate per ADR-014 §7 (Pub 1075 reportable) and the incident-resolution runbook — never re-run to green.

  3. Confirm status per family:

    curl -s -H "Authorization: Bearer $ADMIN_TOKEN" \
  "http://canopy-security:40641/v1/security/chain/status?family=audit" | jq .

    healthy or verifying (200) is acceptable post-restore (verifying while the tail catches up); any 503 state (unknown/stale/error/ breached — the SAME typed body) blocks bringing traffic back. FTI status rides the same endpoint (#1206 MR-3): …/chain/status?family=fti&service=canopy-{tanf,medicaid} — confirm each configured FTI family too; breached with reason legacy_breach_latched is the latched v1 evidence and blocks like any other breach.

Signed-determination integrity

PITR can truncate signed JWS determinations (the WAL replay stops before a transaction commit). Compare row counts between the broken state and the recovered state for each program’s *_determinations table:

-- In the broken (forensic) DB:
SELECT count(*), max(created_at) FROM snap_determinations;
-- In the recovered DB:
SELECT count(*), max(created_at) FROM snap_determinations;

The recovered count must be ≤ broken count, and max(created_at) must be ≤ recovery_target_time. A higher recovered count means recovery overshot (target time was after a determination’s commit but the row was already there) — review the gap and confirm no out-of-range determinations slipped through.

Spot checks

  1. Health endpoints return 200 OK for all restarted services.

  2. Sample records exist in canopy_persons, canopy_applications, and program databases (counts within expected pre-incident range).

  3. Ruleset evaluation returns expected results via canopy-rules API for a known-good test fixture.

  4. The integration test suite (cargo xtask test --integration) passes against the recovered cluster.

Audit-log the PITR itself

Once the recovery is validated, write a record into audit_events so future chain readers understand the gap:

INSERT INTO audit_events (event_type, source_service, action, resource_type, resource_id,
                          user_id, metadata, event_timestamp)
VALUES ('pitr.recovery_completed', 'canopy-security', 'recovered_to_point_in_time',
        'database_cluster', 'canopy-postgres-snap-1', '<operator-username>',
        '{"recovery_target": "<target-utc>", "incident_ticket": "<ticket-id>",
          "broken_state_snapshot": "pre-pitr-broken-<timestamp>"}'::jsonb,
        now());

This row anchors the recovery in the chain (the new event_hash rolls forward from the recovered tail) so post-recovery verification (GET /v1/security/chain/status, once the verifier’s tail re-covers the window) can return to healthy with a clean operator-attributable recovery marker in the log rather than an unexplained gap.

IMPORTANT
For TANF and Medicaid instances, WAL archive storage must meet the same FTI encryption-at-rest requirements as database backups (Pub 1075 §4.7).

Tested execution log

This runbook was executed end-to-end against a one-off PostgreSQL 18.3 container on 2026-05-03 by the operator who shipped #353. The reproduction steps below produce a known-good reference point for future on-call.

Field Value

Date / operator

2026-05-03, bitskrieg (issue #353)

PostgreSQL version

PostgreSQL 18.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 15.2.0) 15.2.0, 64-bit

Test rig

One-off postgres:18-alpine container with wal_level=replica, archive_mode=on, archive_command='test ! -f /wal_archive/%f && cp %p /wal_archive/%f'. Three named volumes: WAL archive, data dir, basebackup.

Workload

CREATE TABLE canary + 2 rows (state to recover to) → pg_basebackup → record recovery target time → DELETE row 1 + INSERT row 3 (the "incident") → pg_switch_wal to flush.

Recovery target

2026-05-04 01:00:23.955986 UTC

Recovery duration

~3 seconds from container start to "consistent recovery state reached" log line. Real production recovery scales with WAL archive size; the test exercised 6 WAL segments.

Outcome

✓ Pass. Recovered cluster shows rows 1 + 2 (the pre-incident state) and not row 3 (the post-incident bad row). Recovery log shows: starting point-in-time recovery to 2026-05-04 01:00:23.955986+00 → consistent recovery state reached at 0/3000158 → recovery stopping before commit of transaction 767, time 2026-05-04 01:00:27.025736+00 → archive recovery complete.

Reproduction

The test commands are bash-paste-able; the procedure follows the single-database PITR procedure above with the workload-then-incident scaffolding scripted.

The test exercised: WAL archiving, basebackup capture, recovery-target configuration via postgresql.auto.conf, recovery.signal-driven recovery boot, point-in-time stop, post-recovery row-state verification. It did not exercise: cross-service multi-DB recovery, encrypted WAL archive storage, FTI-tier compliance, signed-determination row-count validation. Those depend on the production cluster’s actual configuration and should be re-tested against staging when the production WAL archive infrastructure lands.

Post-Restore Verification

After every restore, verify data integrity by running the integration test suite against the restored environment:

# Start all services
cargo xtask dev start --shared-db

# Run the full test battery
cargo xtask test

Expected result: all tests pass. If any tests fail, investigate whether the backup is corrupt or migrations are missing before putting the environment back into service.

Spot Checks

In addition to automated tests, verify:

  1. Health endpoints return 200 OK for all services.

  2. The canopy-security hash chain is intact (no gaps in fti_audit_log sequence numbers).

  3. Sample records exist in canopy_persons, canopy_applications, and program databases.

  4. Ruleset evaluation returns expected results via canopy-rules API.

Edit this page · default