Database Backup & Restore

Both the on-demand developer backup and the daily production backup run the same script: backend/docker/common/db-backup.sh. It produces a portable plain-SQL dump and streams it to S3.

On-demand Backup & Restore (development)

Two Claude Code skills wrap the workflow:

• /db-backup — Runs db-backup.sh inside the container and uploads a fresh dump to S3
• /db-restore — Downloads the latest production dump from S3 and restores it into the local database

Prerequisites

• Sail running (./vendor/bin/sail up -d)
• AWS CLI installed and configured
• DB_DUMPS_BUCKET set, with credentials that can read/write it
• For /db-restore: the production AWS profile (assumes the role that owns production-1-volare-db-dumps) — not the volare-production profile

What Gets Produced

The dump is plain SQL (pg_dump --format=plain), not the custom -Fc format:

• Flags --no-owner --no-privileges --no-acl --clean --if-exists --quote-all-identifiers make it restore into any database without manual role/ownership edits
• Uploaded to s3://${DB_DUMPS_BUCKET}/Volare/latest.sql — a single object, overwritten each run (no dated files, no rolling history)
• Data of transient/high-churn tables is excluded (schema still ships); see the production section below for the full list

When to Create a Backup

• Before running artisan migrate:fresh or db:wipe
• After significant manual data entry in the backoffice
• Before running destructive test suites
• Before major migration changes

Restore Notes

• /db-restore drops and recreates the public schema, then loads the dump with psql
• The dump is ~860 MB; the skill prints size + LastModified before downloading so you can confirm freshness
• A few psql errors on excluded debug tables (telescope_*, pulse_*, jobs, sessions, …) are acceptable; errors on real domain tables (users, offers, bookings) mean the restore failed

Source: .claude/skills/db-backup/SKILL.md, .claude/skills/db-restore/SKILL.md

Production Automated Backup

A fresh dump is uploaded to S3 every day, produced by the same script.

Schedule: Schedule::exec('/usr/local/bin/db-backup.sh')->dailyAt('05:00')->timezone('UTC') in backend/routes/console.php (~line 67), running on the scheduler container. 05:00 UTC sits just after the RDS backup window (04:00–04:30 UTC).

Why a shell script (not spatie/laravel-backup or an Artisan command): the backup keeps working even when the app itself can’t boot, and plain SQL avoids the role/ownership edits the previous zipped-SQL approach forced at restore time.

How It Works

1. pg_dump --format=plain streams straight to aws s3 cp -, so a multi-GB dump never lands on local disk and the upload starts before the dump finishes
2. Streamed to <key>.tmp first
3. Sanity-checks the uploaded object: size ≥ 10 KB and the first bytes contain the PostgreSQL database dump header
4. Only then server-side copies .tmp → the final key; a half-failed run never destroys the previous good backup (set -o pipefail fails the pipeline on any pg_dump error)

Excluded Table Data

Schema always ships, but the contents of these transient tables are skipped (concurrent writes during the dump would break unique-index creation on import, and the data has no restore value):

• telescope_*, pulse_*
• health_check_result_history_items
• jobs, job_batches, failed_jobs
• sessions, cache, cache_locks

Environment Variables

Variable	Default	Purpose
DB_DUMPS_BUCKET	(required)	Destination bucket (production-1-volare-db-dumps in prod)
DB_BACKUP_KEY	Volare/latest.sql	S3 object key
AWS_DEFAULT_REGION	us-east-2	Region for the S3 API calls

Authentication uses the AWS credential chain (EC2 instance role via IMDS in production). DB credentials come from the standard DB_* env vars already present in the scheduler container. The script is installed at /usr/local/bin/db-backup.sh by the Dockerfile.

Source: backend/docker/common/db-backup.sh, backend/routes/console.php