Skip to content

Backup & Restore

This page documents how to back up and restore the AppControl backend state. AppControl persists everything required for resilience and audit in PostgreSQL (or SQLite for small deployments). Gateways and agents are stateless from a backup perspective — they hold mTLS certs (re-issuable) and an ephemeral local buffer (≤ 100 MB sled).

For DR scenarios that consume these backups, see Disaster Recovery.

Table of contents


What to back up

Asset Storage Recovery if lost
PostgreSQL data directory /var/lib/postgresql/data/ (or managed service) From backup
Append-only audit (action_log, state_transitions, check_events, switchover_log, config_versions) PostgreSQL tables From backup — no other source
CA private key pki_authorities table (encrypted) From backup — losing it forces re-enrollment of every agent
JWT_SECRET Kubernetes secret appcontrol-jwt Can be regenerated (invalidates all sessions)
DATABASE_URL Kubernetes secret appcontrol-db Known config
OIDC / SAML client secrets env vars or Kubernetes secrets Re-issue from your IdP
Gateway certs Kubernetes secret appcontrol-gateway-tls Re-issue with appctl pki issue-gateway
Agent certs /etc/appcontrol/tls/ on each host Re-enroll if CA is intact; restore from full host backup otherwise
Helm values, config-management state git / SCM From git history

PostgreSQL

pg_dump — full backup

The standard tool for logical backups. Produces a portable .dump file usable across minor versions.

# Backend uses no extensions beyond gen_random_uuid (pgcrypto); a logical dump is sufficient
pg_dump \
  --host=db.internal \
  --username=appcontrol \
  --format=custom \
  --compress=9 \
  --file=appcontrol-$(date +%Y%m%d-%H%M).dump \
  appcontrol

Restore:

pg_restore \
  --host=db-new.internal \
  --username=appcontrol \
  --clean --if-exists \
  --no-acl --no-owner \
  --jobs=4 \
  --dbname=appcontrol \
  appcontrol-20260513-0300.dump

Verifying the dump

A backup that cannot be restored is not a backup. Always:

  1. Restore to a sandbox PostgreSQL instance after every cron run.
  2. Run the validation queries below.
  3. Alert if any check fails.

Incremental backups

PostgreSQL has no native "incremental" logical dump. For incremental, use either:

  • pg_dump --table= per-table scoped to mutable tables (small) plus periodic full dumps of immutable tables.
  • WAL archiving (recommended; see below).

WAL archiving for PITR

Point-in-time recovery requires continuous WAL archiving plus periodic base backups. Recommended setup:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'aws s3 cp %p s3://appcontrol-wal/%f --no-progress'
archive_timeout = 60          # seconds — force a switch every 60s for tight RPO
max_wal_senders = 3

Take periodic base backups (e.g. daily 03:00):

pg_basebackup \
  --host=db.internal \
  --username=replicator \
  --pgdata=/backup/base-$(date +%Y%m%d) \
  --format=tar --gzip \
  --wal-method=stream \
  --checkpoint=fast

Restore to a target time:

# 1. Extract base backup
tar -xzf /backup/base-20260513.tar.gz -C /var/lib/postgresql/data

# 2. Tell PostgreSQL where to fetch WAL and when to stop
cat >> /var/lib/postgresql/data/postgresql.auto.conf <<EOF
restore_command = 'aws s3 cp s3://appcontrol-wal/%f %p'
recovery_target_time = '2026-05-13 10:30:00 UTC'
EOF

touch /var/lib/postgresql/data/recovery.signal
systemctl start postgresql

RPO with archive_timeout=60s: typically 1–2 min in the worst case (60 s of unarchived WAL + sync lag to S3).

Partitioned check_events

check_events is partitioned by month (migration V005). Each monthly partition is an independent PostgreSQL table:

SELECT inhrelid::regclass AS partition,
       pg_size_pretty(pg_total_relation_size(inhrelid)) AS size
FROM pg_inherits WHERE inhparent = 'check_events'::regclass
ORDER BY partition;
-- check_events_y2026m01    8 GB
-- check_events_y2026m02    9 GB
-- check_events_y2026m03    11 GB

Per-partition dump

For very large check_events, dump each partition separately to limit dump time:

for p in $(psql -At $DATABASE_URL -c \
    "SELECT inhrelid::regclass FROM pg_inherits WHERE inhparent = 'check_events'::regclass"); do
  pg_dump --format=custom --compress=9 \
    --table="$p" --file="ce-${p}.dump" $DATABASE_URL
done

Restore:

for f in ce-*.dump; do
  pg_restore --dbname=$DATABASE_URL_NEW "$f"
done

This is dramatically faster than restoring the full DB because partition restores parallelize trivially.

Skipping cold partitions

You can choose to not back up partitions older than your retention requirement (e.g. > 6 months):

# Drop locally, no backup, before they're rotated by retention
psql $DATABASE_URL -c "DROP TABLE check_events_y2025m11;"

This is a regulatory decision — DORA Article 16 expects incident records for 5+ years; check policy before dropping.


SQLite

AppControl supports SQLite for small / air-gapped / single-host deployments (see SQLite Implementation).

Live backup with .backup

SQLite's .backup command produces a transactionally consistent copy of the live DB without locking:

# From the backend host
sqlite3 /var/lib/appcontrol/appcontrol.db ".backup '/backup/appcontrol-$(date +%Y%m%d-%H%M).db'"

This uses the SQLite online backup API: it copies pages while the backend continues to write.

rsync (cold backup)

For an idle database (backend stopped), rsync is fastest:

systemctl stop appcontrol-backend
rsync -a /var/lib/appcontrol/appcontrol.db /backup/appcontrol.db
systemctl start appcontrol-backend

This causes a service outage; prefer .backup for live systems.

Verify integrity

sqlite3 /backup/appcontrol.db "PRAGMA integrity_check;"
# Should return: ok

What can be regenerated vs preserved

The system distinguishes append-only from mutable tables. Append-only tables are the regulatory record; losing them is a compliance breach.

APPEND-ONLY — must be preserved

Table Purpose DORA mapping
action_log Every user action, written before execute Art. 16 (incident records)
state_transitions Every FSM state change Art. 16
check_events Every health/integrity/infra check (partitioned) Art. 8(2), Art. 11
switchover_log DR switchover phase-by-phase records Art. 11, Art. 25
config_versions Every config change with before/after JSONB Art. 8
enrollment_audit (if present) Token uses for agent/gateway enrollment Art. 16
break_glass_sessions Emergency access sessions Art. 16

REGENERABLE — backup nice-to-have

Table Regenerated by
components.current_state The next health-check cycle
fsm_cache / cluster_member_state Agent reconnect + first check
agent_metrics Resumed on agent reconnect
revoked_tokens Tokens auto-expire; entries are short-lived
notification_queue Notifications re-delivered if subscribed

CONFIG — back up via Helm/secrets

  • organizations, users, teams, team_members, app_permissions_users/teams, applications, components, dependencies, sites, hostings, binding_profiles, agents, gateways, api_keys, enrollment_tokens.

These tables hold the operational configuration. They are not append-only — they evolve over time — but they are critical to restore exactly. A full PostgreSQL dump covers them.


Restore validation

Run this checklist after every restore (DR drill or production).

1. Row counts

SELECT
  (SELECT count(*) FROM organizations)         AS organizations,
  (SELECT count(*) FROM users)                 AS users,
  (SELECT count(*) FROM applications)          AS applications,
  (SELECT count(*) FROM components)            AS components,
  (SELECT count(*) FROM dependencies)          AS dependencies,
  (SELECT count(*) FROM agents)                AS agents,
  (SELECT count(*) FROM action_log)            AS action_log,
  (SELECT count(*) FROM state_transitions)     AS state_transitions,
  (SELECT count(*) FROM switchover_log)        AS switchover_log;

Compare to the last known-good counts (snapshot them before the disaster).

2. Last action_log entry

SELECT max(created_at) AS last_action FROM action_log;
-- Should be close to the recovery_target_time (PITR) or just before the backup time

3. Foreign-key integrity

-- Components without an application (should be 0)
SELECT count(*) FROM components c
LEFT JOIN applications a ON a.id = c.application_id
WHERE a.id IS NULL;

-- action_log without a user (should be 0)
SELECT count(*) FROM action_log al
LEFT JOIN users u ON u.id = al.user_id
WHERE u.id IS NULL AND al.user_id IS NOT NULL;

-- check_events partitions all present
SELECT generate_series('2026-01-01'::date, current_date, '1 month'::interval) AS month
EXCEPT
SELECT date_trunc('month', tableoid::regclass::text::date)
FROM check_events LIMIT 1;

4. Backend /ready

kubectl exec -n appcontrol deploy/appcontrol-backend -- curl -s localhost:3000/ready
# {"status":"ready"}

5. Agent reconnection

Within the heartbeat timeout window (default 180 s), expect all previously-active agents to reconnect:

SELECT
  count(*) FILTER (WHERE last_heartbeat_at > now() - interval '5 minutes') AS connected,
  count(*) FILTER (WHERE last_heartbeat_at <= now() - interval '5 minutes') AS disconnected
FROM agents WHERE is_active = true;

disconnected should approach 0 within 5 minutes.

6. Application state

-- Components in UNKNOWN should resolve within one check_interval
SELECT current_state, count(*) FROM components GROUP BY current_state;

Migration from SQLite to PostgreSQL

If you outgrew SQLite, migrate to PostgreSQL with the following procedure. The DB schema is bytewise identical apart from UUID encoding (TEXT on SQLite, native UUID on PostgreSQL) and JSON column types (TEXT vs JSONB).

Step 1 — provision PostgreSQL

docker run -d --name pg-target \
  -e POSTGRES_DB=appcontrol \
  -e POSTGRES_USER=appcontrol \
  -e POSTGRES_PASSWORD=appcontrol \
  -p 5432:5432 postgres:16-alpine

Step 2 — run migrations on the empty target

DATABASE_URL=postgres://appcontrol:appcontrol@localhost:5432/appcontrol \
  appcontrol-backend --migrate-only

Step 3 — export from SQLite

For each table in dependency order (see migrations/CLAUDE.md):

sqlite3 /var/lib/appcontrol/appcontrol.db <<EOF
.mode csv
.headers on
.output /tmp/organizations.csv
SELECT * FROM organizations;
.output /tmp/users.csv
SELECT * FROM users;
-- repeat for each table
EOF

Step 4 — transform and import

UUIDs in SQLite are stored as TEXT in canonical form (550e8400-...). PostgreSQL accepts the same form when inserted into UUID columns.

JSON columns in SQLite are TEXT; PostgreSQL wants ::jsonb. The migration helper handles the cast on import:

psql $DATABASE_URL <<'EOF'
\copy organizations FROM '/tmp/organizations.csv' WITH (FORMAT csv, HEADER true);
\copy users FROM '/tmp/users.csv' WITH (FORMAT csv, HEADER true);
-- ...

-- Re-cast JSON columns if needed
UPDATE organizations SET settings = settings::text::jsonb;
EOF

Step 5 — switch the backend

# Update the secret
kubectl create secret generic appcontrol-db --namespace appcontrol \
  --from-literal=database-url="postgres://appcontrol:appcontrol@pg.internal:5432/appcontrol?sslmode=require" \
  --dry-run=client -o yaml | kubectl apply -f -

# Restart the backend
kubectl rollout restart -n appcontrol deployment/appcontrol-backend

Step 6 — run validation queries

Use the restore validation queries above.

Migration is one-way

There is no automated PostgreSQL → SQLite path. SQLite is intended for small installs (< 50 agents); once you migrate, plan to stay on PostgreSQL.


Keys & secrets

CA private key

The per-organization CA is stored encrypted in the pki_authorities table. If you lose this row:

  • Every agent must be re-enrolled with a new CA — there is no other way.
  • Plan for this by including pki_authorities in your DB backup (it is part of the pg_dump).

Roadmap: external CA

Delegating CA signing to HashiCorp Vault PKI or AWS KMS so the private key never lives in the backend database is on the roadmap. It is not yet implemented (see Hardening). Today the CA lives encrypted in organizations.ca_private_key; protect it via PostgreSQL TDE or volume-level encryption (LUKS, EBS encryption) and a hardened Kubernetes secret-access policy.

JWT_SECRET

Stored in the Kubernetes secret appcontrol-jwt. Back it up the same way you back up other Kubernetes secrets (Sealed Secrets, HashiCorp Vault, AWS Secrets Manager). Losing it forces every user to re-authenticate.

kubectl get secret -n appcontrol appcontrol-jwt -o yaml > appcontrol-jwt.yaml.enc
# Store encrypted in your secret store

Agent client certificates

Each agent host has its own client cert at /etc/appcontrol/tls/agent.crt. If the host is wiped:

  • If the CA is intact, re-enroll: appcontrol-agent --enroll ….
  • If the CA is gone, see CA recovery above.

Gateway server certificate

Stored as Kubernetes secret appcontrol-gateway-tls. Re-issue at any time via appctl pki issue-gateway.

KMS recommendation (roadmap)

In a future release the backend will be able to delegate CA signing to a KMS or HSM (AWS KMS, GCP Cloud KMS, Azure Key Vault, HashiCorp Vault PKI). The benefits will be:

  • Prevent an admin from exfiltrating the CA from the backend DB.
  • Help satisfy DORA Article 9 (cryptographic key management).
  • Allow hardware-backed key rotation.

This is not yet implemented. The CA private key is currently held encrypted in the database. Until KMS integration ships, mitigate by:

  • enabling at-rest encryption on the PostgreSQL volume (PostgreSQL TDE, LUKS, AWS EBS encryption);
  • restricting SELECT on pki_authorities / organizations.ca_private_key to the backend service account only;
  • rotating the CA on a regular cadence (see PKI rotation).

Sample crontab and retention policy

Cadence What Retention
Every 1 min WAL archive segment to S3 30 days
Daily 03:00 Full pg_dump of all DBs 30 daily files
Weekly Sun 04:00 Tar+gzip last 7 daily dumps to cold storage 12 weekly archives
Monthly 1st 05:00 Promote last weekly to year-tier cold storage 12 monthly + 7 yearly archives
Daily 06:00 pg_basebackup for fast PITR base 7 base backups
Daily 23:00 Sandbox-restore the latest daily and run validation n/a

crontab example

# m h dom mon dow command
0  3  *  *  *  /usr/local/bin/appcontrol-backup-daily.sh
0  4  *  *  0  /usr/local/bin/appcontrol-backup-weekly.sh
0  5  1  *  *  /usr/local/bin/appcontrol-backup-monthly.sh
0  6  *  *  *  /usr/local/bin/appcontrol-basebackup.sh
0 23  *  *  *  /usr/local/bin/appcontrol-restore-verify.sh

appcontrol-backup-daily.sh

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

DEST="s3://appcontrol-backups/daily/$(date +%Y/%m/%d)"
DUMP="/tmp/appcontrol-$(date +%Y%m%d-%H%M).dump"

pg_dump --host=db.internal --username=appcontrol \
        --format=custom --compress=9 \
        --file="$DUMP" appcontrol

# Server-side encryption for at-rest protection
aws s3 cp "$DUMP" "$DEST/" \
    --sse aws:kms \
    --sse-kms-key-id "$KMS_KEY_ID"

rm -f "$DUMP"

# Retention: delete > 30 days
aws s3 ls s3://appcontrol-backups/daily/ --recursive |
  awk '$1 < "'$(date -d '30 days ago' +%Y-%m-%d)'"' |
  awk '{print $4}' |
  xargs -r -I {} aws s3 rm "s3://appcontrol-backups/{}"

appcontrol-restore-verify.sh

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

# Find the most recent daily backup
LATEST=$(aws s3 ls s3://appcontrol-backups/daily/ --recursive |
  sort | tail -1 | awk '{print $4}')

aws s3 cp "s3://appcontrol-backups/$LATEST" /tmp/latest.dump

# Wipe and restore the sandbox DB
PGPASSWORD=$SANDBOX_PASS psql -h sandbox-db.internal -U postgres -c "DROP DATABASE IF EXISTS appcontrol_test;"
PGPASSWORD=$SANDBOX_PASS psql -h sandbox-db.internal -U postgres -c "CREATE DATABASE appcontrol_test;"
PGPASSWORD=$SANDBOX_PASS pg_restore -h sandbox-db.internal -U postgres \
    -d appcontrol_test /tmp/latest.dump

# Validation queries
COUNT=$(PGPASSWORD=$SANDBOX_PASS psql -At -h sandbox-db.internal -U postgres \
    -d appcontrol_test -c "SELECT count(*) FROM action_log;")
echo "action_log rows: $COUNT"

if [ "$COUNT" -lt 100 ]; then
  echo "VALIDATION FAILED: action_log too small" >&2
  exit 1
fi

Encryption at rest

All cron jobs use --sse aws:kms to ensure backups are encrypted at rest with a customer-managed KMS key. This satisfies DORA Article 9 (cryptographic protection of data at rest).

Off-site replication

Daily backups are replicated to a second region via S3 cross-region replication. The replicated region is used for the doomsday scenario (§9 of Disaster Recovery).

resource "aws_s3_bucket_replication_configuration" "appcontrol_backups" {
  bucket = aws_s3_bucket.backups_primary.id
  role   = aws_iam_role.replication.arn

  rule {
    id     = "replicate-all"
    status = "Enabled"
    destination {
      bucket        = aws_s3_bucket.backups_secondary.arn
      storage_class = "GLACIER_IR"
    }
  }
}

Reference