All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Hand off a long task.

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

More jobs like this: Hand off a long task

Keep reading