# Add Scheduled Database Backups to a Docker Compose Stack

Yes. An agent adds a backup service that runs pg_dump on a schedule into its own volume, a restore script and a tested round trip to your Compose stack, in a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-database-backups/docker-compose/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent clones the stack into its own cloud sandbox and reads the Compose file and the application before it adds a service. In a stack like `nginx-golang-postgres` in docker/awesome-compose, `compose.yaml` runs three services: `backend`, built from `backend/Dockerfile` at the `builder` target; `db`, the `postgres` image with `restart: always`, `user: postgres`, a `db-data` volume on `/var/lib/postgresql/data`, `POSTGRES_DB=example`, the password from the `db-password` secret through `POSTGRES_PASSWORD_FILE`, and a `pg_isready` healthcheck every 10 seconds with 5 retries; and `proxy`, nginx with `proxy/nginx.conf` bind-mounted read-only on port 80. The secret comes from `db/password.txt`. There is no backup of any kind: the only copy of the data is the `db-data` volume on the host that runs the stack.

The application shapes the design. `backend/main.go` connects with `postgres://postgres:<password>@db:5432/example?sslmode=disable`, and its `prepare()` runs `DROP TABLE IF EXISTS blog` and recreates the table with five rows every time the process starts. That means a restore has to happen with `backend` stopped, or the backend's next start wipes what was restored; the agent writes that constraint into the restore script and the README rather than discovering it in an incident. It also means the round-trip test has a known expected result: after a restore, `curl localhost:80` must return the five `Blog post` titles that `main.go` seeds.

The agent adds a `backup` service to `compose.yaml` that uses the same `postgres` image as `db`, so `pg_dump` matches the server version, reads the password from the same `db-password` secret, waits on `db` with `condition: service_healthy`, and mounts a new `db-backups` volume at `/backups`. Its command runs `db/backup.sh`, a small POSIX shell script copied in by a bind mount: it sets `PGPASSWORD` from `/run/secrets/db-password`, runs `pg_dump -h db -U postgres example` through `gzip` into a timestamped file, prunes files older than `BACKUP_KEEP_DAYS`, sleeps `BACKUP_INTERVAL_SECONDS` and repeats. Both values are environment variables with defaults set in `compose.yaml`, so a daily backup with a week of retention is a two-line change rather than an edit to the script. `db/restore.sh` takes a file name, stops `backend`, pipes the archive into `psql` inside the `db` container with `docker compose exec -T`, and starts `backend` again.

Proof is a real round trip in the sandbox, where Docker is already available. The agent runs `docker compose config` to validate the file, `docker compose up -d --wait`, then `docker compose exec backup sh /backup.sh --once` to take one dump immediately, lists `/backups` and checks with `gunzip -c | head` that the archive contains `CREATE TABLE public.blog`. It then drops the table through `psql`, runs `db/restore.sh` with that file, and curls the proxy to see the five titles return. Finally `docker compose down -v` cleans up. The repository's own CI in `.github/workflows/codeql-analysis.yml` only runs CodeQL, so the agent adds nothing to CI and leaves that choice to a follow-up.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add scheduled Postgres backups to this Docker Compose stack. Read compose.yaml, every Dockerfile, the backend source, the db/ directory and the README first. Add a backup service that uses the same postgres image as the db service, reads the password from the existing db-password secret, depends on db being healthy, and mounts a db-backups volume at /backups. Write db/backup.sh (pg_dump of the example database through gzip into a timestamped file, prune files older than BACKUP_KEEP_DAYS, sleep BACKUP_INTERVAL_SECONDS, repeat; a --once flag runs a single dump) with defaults of 7 days and 86400 seconds set in compose.yaml, and db/restore.sh that stops the backend, restores a named archive with psql via docker compose exec -T, and starts the backend again, since the backend recreates its table on start. Document both in the README. Prove it: docker compose config, docker compose up -d --wait, one immediate backup, check the archive contains the blog table, drop the table, restore, curl localhost:80 and confirm the five titles, then docker compose down -v. Open a pull request listing the files, the schedule and retention defaults, and the round-trip output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request on its own branch that adds the `backup` service and the `db-backups` volume to `compose.yaml`, adds `db/backup.sh` and `db/restore.sh`, and extends the README with a backups section that names the defaults, the volume, and the stop-restore-start sequence. `backend/`, `proxy/` and the `db` service are unchanged: the backup reads the same secret and the same network name the backend already uses.

The description quotes the round trip: the `docker compose up` wait, the archive name and size from `ls -l /backups`, the `CREATE TABLE` line from the archive, the `DROP TABLE`, the restore, and the `curl localhost:80` output with the five titles. The transcript at `GET /o/{org}/sessions/{id}/messages` keeps each command and its output, so a reviewer can check that the restore was tested against a real drop rather than described.

Before merging, a reviewer checks that the schedule and retention suit the data, that `db-backups` is a named volume the host backs up or syncs elsewhere, and that `restore.sh` refuses to run without a file argument. A follow-up on the same session can add a second step that copies each archive to object storage, or switch the dump format to `pg_dump -Fc` for `pg_restore`.

## Run it on every new Compose stack with a database

A team that runs several small services this way runs this once per repository. From a script, send the body above to `POST /o/{org}/sessions` with each repository in `resources`, one run per repository, and a `request_id` that is a UUID v5 of the repository name so a retry is safe. Give the key a `webhook_url` and every run the key starts posts `run.completed` with `data.session_url` when its turn ends; a handler reads the pull request link from the session.

To catch stacks that add a database without a backup, add an automation to the Project with a `github` trigger on `pull_request.opened` and `pull_request.synchronize` and a prompt that reads the pull request's Compose file and comments when a `postgres`, `mysql` or `mariadb` service appears with no backup service beside it. The trigger clones that branch and places the event after the prompt, and the automation starts at most `max_runs_per_hour` runs in an hour.

## Why run it on Rush?

A backup that has never been restored is a guess. The agent does the part people skip: it drops the table and restores it in a throwaway stack, in its own sandbox with Docker already there, and it reads the application first so it knows the backend would wipe a restore on its next start. The pull request carries the constraint and the proof together.

Every `docker compose` and `psql` command and its output stay in the run's transcript, so the review is of a log rather than a promise, and the laptop that handed off the task is free while the stack builds and the round trip runs. Several stacks can get the same backup service at once, each in its own sandbox and its own pull request.

## Frequently asked questions

### Why does the restore stop the backend first?

Because `backend/main.go` runs `DROP TABLE IF EXISTS blog` and reseeds five rows every time it starts. A restore while the backend is running works until the next restart, which wipes it. `restore.sh` stops `backend`, restores through `psql`, and starts it again, and the README says why, so nobody removes the step as an optimisation.

### Where do the backups live?

In a named `db-backups` volume on the same host, which is better than nothing and not an off-site copy. The pull request says so and leaves a follow-up for syncing the directory to object storage, because that needs credentials the repository does not have and the agent does not invent them.

### Does the dump lock the database?

No. `pg_dump` takes a consistent snapshot and lets reads and writes continue, so the backend keeps serving `/` while the archive is written. The dump runs inside the Compose network against the `db` host name, exactly as the backend connects.

### Why a shell loop instead of cron?

The `postgres` image has no cron daemon, and adding one means another image to maintain. A `sleep` loop with `BACKUP_INTERVAL_SECONDS` keeps the service to one image, restarts with the stack, and is easy to replace with a host cron or a scheduler later; the script's `--once` flag already supports that.

### Can I use the same approach for MySQL?

Yes. The service shape is the same: the database image, the existing secret, a volume and a script. The agent would swap `pg_dump` for `mysqldump` and `psql` for `mysql`, and read the credentials the way the stack's own services do; it checks the Compose file first and follows what it finds.

## More Docker Compose jobs

- [Add Dependabot to a Docker Compose Repository](https://getrush.ai/run/add-dependabot/docker-compose/)
- [Add a Dev Container to a Docker Compose Project](https://getrush.ai/run/add-dev-containers/docker-compose/)
- [Move Compose Passwords into Docker Secrets](https://getrush.ai/run/add-docker-secrets/docker-compose/)

## More jobs like this: Hand off a long task

- [Add Database Indexes with an Alembic Migration](https://getrush.ai/run/add-database-indexes/alembic/)
- [Add Database Indexes to a Mongoose App](https://getrush.ai/run/add-database-indexes/mongoose/)
- [Add Database Indexes to a Prisma Schema](https://getrush.ai/run/add-database-indexes/prisma/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
