Backups

Production has two kinds of backup:

	Supabase's own	Complied's off-site copy
What	The whole database, as a physical snapshot	Roles, schema, every row, and the files in every Storage bucket
How often	Daily	Weekly, Saturdays 11:00 UTC, and on demand
Kept for	7 days (Pro plan)	As long as the bucket's lifecycle rule says (90 days as set up below)
Where	Inside Supabase	An S3 bucket in Complied's AWS account (us-east-2)
Survives the project being deleted	No	Yes
Includes Storage files	No	Yes
Can be downloaded	No	Yes, by whoever holds the private key

The off-site copy exists for what Supabase's backups can't cover: losing the project, the account or the region, a bad change that is only noticed after a week, and the Storage files (documents, licences, branding and report assets).

Not in either backup: edge-function secrets, Auth settings (redirect URLs, SMTP, providers) and other project settings. Keep a copy of the secrets in the team's password manager; the settings are described in deployment.

How the off-site copy works

A systemd timer on the ingestion EC2 box, complied-offsite-backup.timer, runs run-offsite-backup.sh every Saturday at 11:00 UTC, clear of the nightly delta, the Saturday identity load and the Sunday records sync. It is versioned and installed like the box's other jobs (the ingestion box), holds the shared deploy lock while it runs, and reports to Healthchecks.io as complied-offsite-backup, so a failed or missing run alerts. It runs on the box rather than in GitHub Actions so it uses none of the organisation's Actions minutes, which CI and deploys depend on.

Each part is streamed straight through gzip and age encryption into S3, so nothing readable is written to the box's disk or to the bucket:

Object	Contents	Made with
roles.sql.gz.age	Cluster roles and their settings	pg_dumpall --roles-only
schema.sql.gz.age	Every schema: tables, policies, functions, triggers	pg_dump --schema-only
data.sql.gz.age	Every row, including public_events (whose ids tenant data points at, so a re-ingest can't stand in for it), auth.users and storage.objects	pg_dump --data-only
storage.tar.age	The files in every Storage bucket, one folder per bucket	aws s3 sync over Supabase's S3 protocol, then tar
manifest.json	Time, host, release, pg_dump version, and each encrypted object's size and sha256

The three dumps are the exact commands supabase db dump runs (CLI 2.119.0, from its --dry-run output), with the same flags and the same filters for Supabase's managed schemas, so they restore the way Supabase documents. The box runs pg_dump 17 directly because the CLI needs Docker. The connection comes from PG* variables loaded from /opt/complied/.env, so no password appears on a command line.

Each run writes to weekly/<UTC timestamp>/. A dump smaller than a sanity minimum fails the run. Log: /var/log/complied/offsite-backup.log.

Encryption is to an age public key. The private key never goes to the box, GitHub or AWS, so neither a compromised box nor a leaked bucket exposes the data. The box's instance role may only write objects to the bucket: it cannot list, read or delete them.

The Storage files are copied to the box's disk (BACKUP_TMP, default /var/tmp/complied-backup) before they are packed, and removed afterwards. The script checks the free space first and fails with the numbers if the files won't fit.

Costs: a few GB a week in S3 Standard is well under a dollar a month at 90 days' retention. If the box and the bucket are in different regions, the upload adds AWS's inter-region transfer charge, a few cents a week at this size. The dump's egress counts against the Supabase plan's included bandwidth.

Setting it up

Until /opt/complied/.env has the backup settings, the timer runs, logs "not configured" and exits cleanly. All of this is done once, by the project owner.

1. The key pair. On a trusted machine, not the box:

age-keygen -o complied-backup.key

The file holds the private key (AGE-SECRET-KEY-1…) and prints the public key (age1…). Store the whole file in two places off the box, GitHub and AWS, for example the team password manager and an offline copy. Without it no backup can be read.

2. The bucket, in us-east-2 (Ohio): block all public access, turn on versioning and default encryption (SSE-S3), and add a lifecycle rule that expires objects under weekly/ after 90 days and noncurrent versions after 7. Object Lock in governance mode is optional extra protection against deletion.

3. Write access for the box. Add this inline policy to the box's instance role (the role behind the instance profile complied-ingest-ec2):

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:AbortMultipartUpload"],
      "Resource": "arn:aws:s3:::BUCKET_NAME/weekly/*"
    }
  ]
}

4. From the Supabase dashboard of the production project:

• The session pooler connection details (Connect, Session pooler): host, port, user (postgres.<project ref>) and the database password.
• A Storage S3 access key (Storage, Settings, S3 Connection, New access key), and the region shown there. This key can read and write every bucket, so it lives only in the box's .env.

5. The tools, once, as root on the box:

sudo bash /opt/complied/current/map-service/deploy/ec2/install-backup-tools.sh

It installs postgresql17 (for pg_dump 17) and age, from a pinned release whose checksum it verifies. It is separate from install.sh so a package mirror being down can never fail a deploy.

6. Settings in /opt/complied/.env:

BACKUP_S3_URI=s3://BUCKET_NAME
BACKUP_S3_REGION=us-east-2
BACKUP_AGE_RECIPIENT=age1...                     # the public key from step 1
BACKUP_PGHOST=aws-0-<region>.pooler.supabase.com
BACKUP_PGPORT=5432
BACKUP_PGUSER=postgres.<project ref>
BACKUP_PGPASSWORD=...
BACKUP_STORAGE_S3_KEY_ID=...                     # step 4; without these the files are skipped
BACKUP_STORAGE_S3_SECRET=...
BACKUP_STORAGE_REGION=us-east-1                  # the region shown with the Storage key
SUPABASE_PROJECT_REF=<project ref>

7. Monitoring: add a Healthchecks.io check with the slug complied-offsite-backup, schedule 0 11 * * 6 and a 6-hour grace (monitoring).

8. First run: sudo systemctl start complied-offsite-backup.service, then read /var/log/complied/offsite-backup.log and check the objects arrived under weekly/ in the bucket.

Restoring

Reading a backup needs AWS credentials that may read the bucket (an admin's, not the box's) and the private key.

1. Download and check.

aws s3 cp --recursive s3://BUCKET_NAME/weekly/2026-11-07T1100Z/ ./backup/
cd backup
cat manifest.json
sha256sum *.age          # must match encrypted_objects in the manifest

2. Decrypt.

for f in roles schema data; do age -d -i complied-backup.key $f.sql.gz.age | gunzip > $f.sql; done
age -d -i complied-backup.key storage.tar.age | tar -xf - -C ./storage

3. Restore the database into a new Supabase project on the same Postgres major version. Supabase's own services must have set the project up first, so restore into a hosted project or a full supabase start stack, never a bare Postgres container:

psql "$NEW_DB_URL" -v ON_ERROR_STOP=1 -f roles.sql
psql "$NEW_DB_URL" --single-transaction -v ON_ERROR_STOP=1 \
  --file schema.sql \
  --command 'SET session_replication_role = replica' \
  --file data.sql

session_replication_role = replica turns triggers and foreign-key checks off while the rows go in, so table order doesn't matter. A line in roles.sql that the new project already has, or that only a superuser may run (a GRANT SET ON PARAMETER, for example), can be removed and the file run again.

4. Restore the files into the new project's buckets over its S3 endpoint:

for bucket in storage/*/; do
  aws s3 sync "$bucket" "s3://$(basename "$bucket")" \
    --endpoint-url "https://NEW_REF.storage.supabase.co/storage/v1/s3" --profile new-supabase
done

5. Put back what the backup doesn't hold: edge-function secrets, Auth settings and the custom domains, then redeploy the edge functions (deployment).

Restore drill

Run on 2026-11-05 against the local database:

• The pipeline. The dump, gzip, age and checksum steps decrypted byte for byte back to the dump, and the recorded size and sha256 matched the file.
• The restore, with the commands above into a fresh supabase/postgres:17.11.0.002 container: the schema restored with no errors, all 322 policies and 169 functions in public; the public data restored with no errors, and row counts matched the source on every table checked, units (411,846 rows) among them.
• The box script. install-backup-tools.sh and run-offsite-backup.sh ran in an amazonlinux:2023 container against the local database, with S3 replaced by a local folder.
• roles.sql needed a superuser for one GRANT SET ON PARAMETER, and the auth rows needed the tables Supabase's Auth service creates, which a bare Postgres container lacks. Both are why step 3 restores into a full Supabase project.

Repeat the drill after any change to the backup script, and from a real weekly backup into a scratch Supabase project at least once a quarter.