Skip to content

Database Backup And Restore Runbook

This runbook covers TradeMate Postgres/Timescale backups for production and staging. It uses custom-format pg_dump artifacts so restores can be validated before data is written anywhere.

Scope

  • Primary database: TradeMate API Postgres/Timescale database.
  • Included data: schema, data, Timescale hypertables, pgvector tables, and RLS policy definitions present in Postgres.
  • Excluded data: Valkey cache, broker/provider secrets stored outside Postgres, and object storage assets.

Safety Rules

  • Never restore directly into production.
  • Restore into a disposable local or staging database first.
  • Keep backups encrypted at rest in the storage layer.
  • Do not paste database URLs into logs or tickets.
  • Run verify before every restore.
  • Use a separate target database name for restore drills, for example trademate_restore_20260609.

Prerequisites

  • pg_dump and pg_restore installed from a Postgres version compatible with the production server.
  • DATABASE_URL set for backup, or pass --database-url.
  • A disposable restore target database already created.
  • S3-compatible object storage bucket with lifecycle encryption enabled.
  • Backup runner environment:
  • BACKUP_S3_BUCKET
  • BACKUP_S3_PREFIX, for example trademate/postgres/prod
  • BACKUP_S3_ENDPOINT_URL for Hetzner/Object Storage-compatible endpoints
  • AWS_ACCESS_KEY_ID
  • AWS_SECRET_ACCESS_KEY
  • optional BACKUP_NOTIFY_WEBHOOK_URL for success/failure notifications

Scheduled Production Backup

TradeMate uses scripts/scheduled_database_backup.py for unattended backups. The runner creates one custom-format dump, uploads it to S3-compatible storage, and prunes old backup prefixes.

Configure a Coolify scheduled task or dedicated backup service with:

bash python /app/scripts/scheduled_database_backup.py

Schedule it daily after the quietest production window, for example 15 2 * * * UTC. The runner writes:

  • daily/YYYY/MM/DD/trademate-YYYYMMDDTHHMMSSZ.dump
  • weekly/YYYY-Www/... on Mondays
  • monthly/YYYY-MM/... on the first day of the month

Default retention is:

  • 7 daily backups
  • 4 weekly backups
  • 6 monthly backups

Use these env vars to override retention if needed:

bash BACKUP_RETENTION_DAILY=7 BACKUP_RETENTION_WEEKLY=4 BACKUP_RETENTION_MONTHLY=6 BACKUP_PRUNE_LOOKBACK_DAYS=400

Dry-run the exact production schedule before enabling the cron:

bash DATABASE_URL="$DATABASE_URL" \ BACKUP_S3_BUCKET="$BACKUP_S3_BUCKET" \ BACKUP_S3_PREFIX="trademate/postgres/prod" \ BACKUP_S3_ENDPOINT_URL="$BACKUP_S3_ENDPOINT_URL" \ python scripts/scheduled_database_backup.py \ --run-at 2026-06-10T02:15:00Z \ --dry-run

The command output must redact the database password. S3 credentials are read from the AWS-compatible environment variables and are never printed.

Create A Backup

Dry-run first:

bash python scripts/database_backup_restore.py backup \ --database-url "$DATABASE_URL" \ --output output/backups/trademate-$(date +%Y%m%d-%H%M%S).dump \ --dry-run

Run the backup:

bash python scripts/database_backup_restore.py backup \ --database-url "$DATABASE_URL" \ --output output/backups/trademate-$(date +%Y%m%d-%H%M%S).dump

The helper prints a redacted command. It never prints the database password.

Verify A Backup

bash python scripts/database_backup_restore.py verify \ --backup output/backups/trademate-20260609-120000.dump

This runs pg_restore --list and confirms the custom-format artifact can be read before a restore is attempted.

Restore Drill

Create a disposable database, then dry-run:

```bash createdb trademate_restore_20260609

python scripts/database_backup_restore.py restore \ --backup output/backups/trademate-20260609-120000.dump \ --target-database-url "postgresql://postgres:postgres@localhost/trademate_restore_20260609" \ --confirm-database trademate_restore_20260609 \ --jobs 4 \ --dry-run ```

Run the restore:

bash python scripts/database_backup_restore.py restore \ --backup output/backups/trademate-20260609-120000.dump \ --target-database-url "postgresql://postgres:postgres@localhost/trademate_restore_20260609" \ --confirm-database trademate_restore_20260609 \ --jobs 4

The restore command refuses remote targets unless --allow-remote-target is passed. Use that only for staging restore drills where the target is known and empty.

Post-Restore Checks

Run these checks against the restored database:

bash psql "$RESTORE_DATABASE_URL" -c "select count(*) from alembic_version;" psql "$RESTORE_DATABASE_URL" -c "select count(*) from users;" psql "$RESTORE_DATABASE_URL" -c "select count(*) from portfolios;" psql "$RESTORE_DATABASE_URL" -c "select count(*) from trades;" psql "$RESTORE_DATABASE_URL" -c "select count(*) from market_data;"

Then point a staging API container at the restored database and run:

bash curl -fsS https://staging-api.yourtrademate.io/ready curl -fsS https://staging-api.yourtrademate.io/version

Recovery Procedure

  1. Freeze writes by disabling public API traffic or routing users to maintenance.
  2. Take a final emergency backup of the current database if it is reachable.
  3. Restore the selected backup into a new database.
  4. Run the post-restore checks.
  5. Repoint the API and worker DATABASE_URL values to the restored database.
  6. Restart API, Celery, and dependent services.
  7. Verify /ready, critical workflows, and audit logs.
  8. Record backup filename, source deployment commit, restore target, checks, and operator in the incident notes.

Evidence To Attach To TM-82

  • Backup command dry-run output with redacted URL.
  • Scheduled backup dry-run output showing daily, weekly, monthly, and prune commands.
  • Coolify scheduled task configuration screenshot or deployment log.
  • S3 object listing showing the most recent backup uploaded under the expected prefix.
  • Success/failure webhook notification payload or delivery log.
  • pg_restore --list success output.
  • Restore drill command output.
  • Post-restore table counts.
  • Staging /ready and /version responses.