Database Backup and Recovery

This document describes the database backup and recovery strategy for Open ACE, covering both Kubernetes deployments (k8s/extras/backup/) and Docker Compose deployments (docker-compose.yml).

Issue #1853: Kubernetes database backup reference implementation

Overview

Open ACE provides optional database backup infrastructure:

Scheduled PostgreSQL backups via CronJob (Kubernetes)
Manual pg_dump/pg_restore procedures (Docker Compose)
Integrity verification using pg_restore --list
Multiple object storage backend support (AWS S3, MinIO, etc.)
Restore Job for disaster recovery (Kubernetes)

Deployment options

Option	Environment	Backup owner	Typical RPO
Managed PostgreSQL (RDS/Aurora, Cloud SQL, Azure DB, Alibaba RDS)	Production with SLA	Cloud provider (automated backups, PITR)	< 5 minutes
Self-managed CronJob (`k8s/extras/backup/`)	Kubernetes, cost-sensitive	This repo's manifests	6–24 hours (CronJob frequency)
Manual `pg_dump` on the `postgres-data` volume	Docker Compose single host	You, via this guide	Whatever you schedule

Kubernetes backup (self-managed)

Read this first: the object storage Secret is NOT part of the kustomization

k8s/extras/backup/kustomization.yaml lists exactly these resources:

serviceaccount.yaml
rbac.yaml
backup-script-configmap.yaml
cronjob.yaml
restore-job.yaml

The credentials file (secret-s3.yaml, created from secret-s3.yaml.example, or secret-minio.yaml from secret-minio.yaml.example) is not among them. If you follow the classic "copy the example, then kubectl apply -k ." recipe, the apply succeeds, the CronJob is created, and every backup job then fails at the upload step because the backup-storage-credentials Secret referenced by the CronJob does not exist — the failure is silent unless you check job logs.

Apply the Secret explicitly, then the kustomization:

cd k8s/extras/backup/

# 1. Create and edit credentials
cp secret-s3.yaml.example secret-s3.yaml
# Edit secret-s3.yaml with your S3 credentials

# 2. Apply the Secret explicitly (it is NOT included by kustomization.yaml)
kubectl apply -f secret-s3.yaml

# 3. Apply the rest of the backup manifests
kubectl apply -k .

# 4. Verify CronJob AND Secret
kubectl get cronjob -n open-ace
kubectl get secret backup-storage-credentials -n open-ace

Trigger a manual backup (testing)

# Create a one-time job from the CronJob
kubectl create job --from=cronjob/postgres-backup manual-backup-$(date +%Y%m%d) -n open-ace

# Watch logs
kubectl logs -f job/manual-backup-$(date +%Y%m%d) -n open-ace

CronJob parameters

Values below are what k8s/extras/backup/cronjob.yaml actually ships:

Parameter	Value	Notes
Schedule	`0 2 * * *`	Daily at 02:00 UTC (cronjob.yaml:18)
activeDeadlineSeconds	3600	1 hour timeout; increase to 7200 for large databases (cronjob.yaml:33)
CPU request / limit	200m / 500m	(cronjob.yaml:132-137)
Memory request / limit	256Mi / 512Mi	Increase to 1Gi for databases > 1GB (cronjob.yaml:132-137)
concurrencyPolicy	Forbid	No overlapping runs
backoffLimit	2	Retry up to 2 times on failure
successfulJobsHistoryLimit / failedJobsHistoryLimit	3 / 3	Kept for debugging
Images	`alpine:3.19` (init: installs AWS CLI into an emptyDir) + `postgres:15-alpine` (main)	postgres:15-alpine does not include AWS CLI

Backup content

Component	Method	Frequency	Retention
PostgreSQL database	`pg_dump -Fc` (custom format)	Daily	30 days
PostgreSQL globals	`pg_dumpall --globals-only`	Daily	30 days
Redis (optional, `BACKUP_REDIS`)	RDB snapshot	Daily	7 days

Backup integrity verification

Each backup includes automatic integrity verification:

After backup creation: pg_restore --list db.dump validates the file is parseable
If verification fails: Job exits with code 1, triggering CronJob retry
Success indicator: Job exits with code 0, backup uploaded to object storage

# Manual verification
pg_restore --list backup.dump > /dev/null && echo "Valid" || echo "Corrupted"

Object storage credentials

AWS S3 (secret-s3.yaml):

# secret-s3.yaml
apiVersion: v1
kind: Secret
metadata:
  name: backup-storage-credentials
  namespace: open-ace
type: Opaque
stringData:
  AWS_ACCESS_KEY_ID: "AKIAIOSFODNN7EXAMPLE"
  AWS_SECRET_ACCESS_KEY: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
  AWS_REGION: "us-east-1"
  S3_BUCKET: "my-open-ace-backups"
  S3_ENDPOINT: ""  # Leave empty for AWS S3

MinIO (secret-minio.yaml, from secret-minio.yaml.example):

# secret-minio.yaml
apiVersion: v1
kind: Secret
metadata:
  name: backup-storage-credentials
  namespace: open-ace
type: Opaque
stringData:
  AWS_ACCESS_KEY_ID: "minioadmin"
  AWS_SECRET_ACCESS_KEY: "minioadmin"
  AWS_REGION: "us-east-1"
  S3_BUCKET: "open-ace-backups"
  S3_ENDPOINT: "http://minio.example.com:9000"

Recovery procedure (Kubernetes)

# Step 1: Identify the backup in your object storage
aws s3 ls s3://your-bucket/open-ace/

# Step 2: Download it
aws s3 cp s3://your-bucket/open-ace/20240115/backup.tar.gz .
tar -xzf backup.tar.gz

# Step 3: Verify integrity
pg_restore --list db.dump

# Step 4: Stop application writers, then apply the restore Job
kubectl scale deployment open-ace --replicas=0 -n open-ace
export RESTORE_TIMESTAMP=20240115
kubectl apply -f restore-job.yaml
kubectl logs -f job/postgres-restore -n open-ace

# Step 5: Restart and verify the application
kubectl scale deployment open-ace --replicas=3 -n open-ace
kubectl exec -it deployment/open-ace -n open-ace -- \
  curl -s http://localhost:19888/health

The backup Job is compatible with the existing NetworkPolicy (PostgreSQL 5432, Redis 6379, and external HTTPS 443 egress are already allowed); no NetworkPolicy modification is required.

Monitoring and troubleshooting

# List recent backup jobs
kubectl get jobs -n open-ace -l app.kubernetes.io/component=backup

# Check last job status
kubectl describe job -n open-ace -l app.kubernetes.io/component=backup

# View job logs
kubectl logs job/postgres-backup-$(date +%Y%m%d) -n open-ace

Common failures:

Symptom	Cause	Fix
Job runs, dump succeeds, upload fails with missing credentials	`secret-s3.yaml` was never applied (it is not in the kustomization)	`kubectl apply -f secret-s3.yaml`
Job OOM-killed on large databases	512Mi memory limit too low	Patch the CronJob memory limit to 1Gi
CronJob never starts	Suspended, or previous job still within the 1h deadline	Check `suspend` flag and `activeDeadlineSeconds`

Docker Compose backup and restore

The Compose stack runs PostgreSQL 15 (postgres:15-alpine) with all data on the named volume postgres-data, mounted at /var/lib/postgresql/data. Defaults come from the environment: user ${DB_USER:-ace}, database ${DB_NAME:-ace}. Back up with pg_dump; never copy the volume directory while PostgreSQL is running.

Backup

# Custom-format dump streamed out of the container (-T disables TTY for redirection)
mkdir -p ./backups
docker compose exec -T postgres pg_dump -U ${DB_USER:-ace} -d ${DB_NAME:-ace} -Fc \
  > ./backups/ace-$(date +%Y%m%d-%H%M).dump

# Verify it is parseable
pg_restore --list ./backups/ace-$(date +%Y%m%d-%H%M).dump > /dev/null && echo "Valid"

Plain SQL dump (greppable, larger):

docker compose exec -T postgres pg_dump -U ${DB_USER:-ace} -d ${DB_NAME:-ace} \
  > ./backups/ace-$(date +%Y%m%d-%H%M).sql

Restore

# 1. Stop the writers (app and, if enabled, scheduler)
docker compose stop app scheduler

# 2. Restore over the existing database (custom format only)
cat ./backups/ace-20260929-0200.dump \
  | docker compose exec -T postgres pg_restore -U ${DB_USER:-ace} -d ${DB_NAME:-ace} --clean --if-exists

# 3. Restart and verify
docker compose start app scheduler
docker compose exec -T postgres psql -U ${DB_USER:-ace} -d ${DB_NAME:-ace} -c "SELECT version();"
curl http://localhost:19888/health

Restoring to a fresh volume

If the postgres-data volume was lost entirely:

docker compose down
docker volume rm open-ace_postgres-data   # adjust the compose project prefix if needed
docker compose up -d postgres             # empty database re-created from env vars
cat ./backups/ace-20260929-0200.dump \
  | docker compose exec -T postgres pg_restore -U ${DB_USER:-ace} -d ${DB_NAME:-ace}
docker compose up -d

After any restore, run alembic upgrade head if the backup predates the current schema (see ../dev/SCHEMA_MIGRATION_GUIDE.md).

Scheduling (optional)

Cron on the Docker host:

0 2 * * * cd /path/to/open-ace && docker compose exec -T postgres pg_dump -U ace -d ace -Fc > backups/ace-$(date +\%Y\%m\%d).dump

Multi-tenant note

pg_dump captures all tenants automatically — no special backup configuration is needed. After a restore, verify that all tenants are present and run the application-level permission validation.

Recovery drill checklist

Download the latest backup from object storage (or ./backups/)
Verify integrity (pg_restore --list)
Restore to a test database
Verify row counts and schema validation
Start the application against the restored database and run smoke tests
Document the RTO achieved

KUBERNETES.md — Kubernetes deployment guide
k8s/extras/backup/README.md — backup manifest reference
../dev/SCHEMA_MIGRATION_GUIDE.md — schema migration (run after restoring older backups)

Overview​

Deployment options​

Kubernetes backup (self-managed)​

Read this first: the object storage Secret is NOT part of the kustomization​

Trigger a manual backup (testing)​

CronJob parameters​

Backup content​

Backup integrity verification​

Object storage credentials​

Recovery procedure (Kubernetes)​

Monitoring and troubleshooting​

Docker Compose backup and restore​

Backup​

Restore​

Restoring to a fresh volume​

Scheduling (optional)​

Multi-tenant note​

Recovery drill checklist​

Related documentation​