Database Backups as Code — Oselio Candido

Why bother — isn't this a managed service?

Managed Postgres offerings — RDS, Cloud SQL, Azure Database for PostgreSQL — will happily take automated snapshots for you. That's not what's running here: Postgres is a plain container on a VPS, with its data directory on a real mounted volume, sitting next to the rest of the stack instead of behind a managed database service.

That's a deliberate cost decision, not an oversight — running the database as another container on infrastructure already being paid for avoids stacking a managed-database bill on top of it. It also means none of the automated-snapshot machinery a managed offering bundles in comes for free; backups had to be built by hand, or not exist at all.

So that's what this is: a self-rolled backup, built from four ordinary pieces — cron to trigger it on a schedule, pg_dump to actually produce the dump, the AWS CLI to ship it off-box, and Docker Compose to package the whole thing as one reviewable service definition instead of a loose shell script on the host. Nothing here is exotic; the point of walking through it is that this combination is enough to get a real, working backup without reaching for a managed product.

The constraint: it shouldn't run all the time

A backup job has no business starting on every docker compose up, holding a database connection open, or running as a long-lived process. It needs to exist, be reviewable, and run on demand or on a schedule — and otherwise not be there.

A cron entry on the host, a script nobody reviews, credentials typed into a shell once and forgotten — that's the usual shape of a homegrown backup, and it doesn't get the things version-controlled infrastructure gets for free: a diff when it changes, a review before it ships, a single file to read to know exactly what it does.

Step 1: Define the job as a Compose profile

A Compose profile is the primitive that solves the "shouldn't always run" constraint. The service is defined in the file like everything else, but it doesn't start on a plain up — it only runs when the profile is explicitly requested.

  backup_postgres:
    image: postgres:15.13-alpine3.22
    profiles: ["backup"]
    env_file:
      - ../env/prd.env
    depends_on:
      db:
        condition: service_healthy
    environment:
      DAY: ${DAY}
      MONTH: ${MONTH}
      YEAR: ${YEAR}
    entrypoint: ["/bin/sh", "-c", "
      apk add --no-cache aws-cli &&
      pg_dump -h db -U $$POSTGRES_USER -F c $$POSTGRES_DB | gzip > /backups/$$POSTGRES_DB_$$YEAR-$$MONTH-$$DAY.dump.gz &&
      aws s3 cp /backups/$$POSTGRES_DB_$$YEAR-$$MONTH-$$DAY.dump.gz s3://$${AWS_S3_BUCKET_NAME}/postgres/$$YEAR/$$MONTH/$${DAY}/$$POSTGRES_DB.dump.gz"]

depends_on: db: condition: service_healthy means the job waits for Postgres's own healthcheck before it runs — no race against a database that's still starting up.

crontriggers on schedule

docker compose --profile backup runone-shot container

pg_dump -F c | gzipcompressed archive

aws s3 cpYEAR/MONTH/DAY key

container exits

Step 2: Trigger it — by hand or from cron

Run once, by hand, it's a single command against the same files and env layering the rest of the stack already uses:

docker compose -f docker-compose.yml -f docker-compose.prd.yml --profile backup run backup_postgres

Scheduled, it's the exact same command sitting behind a crontab entry — cron just supplies DAY/MONTH/YEAR and fires it daily:

# /etc/cron.d/postgres-backup — daily at 02:00
0 2 * * * root DAY=$(date +\%d) MONTH=$(date +\%m) YEAR=$(date +\%Y) \
    docker compose -f /app/docker-compose.yml -f /app/docker-compose.prd.yml \
    --profile backup run --rm backup_postgres >> /var/log/postgres-backup.log 2>&1

cron's only job is to show up on time and set three date variables. Everything that actually matters — the image, the dump command, the S3 destination — lives in the versioned Compose file, not in the crontab line.

Step 3: What the container actually does

The backup_postgres service runs a disposable Postgres container that reaches db over the internal network, dumps in custom format, compresses it, and ships it out:

Same image as the database — keeps the pg_dump client version aligned with the server version, instead of leaving that to whatever client happens to be installed wherever the job runs.
Custom format, not plain SQL — pg_dump -F c produces Postgres's own compressed archive, restorable selectively (one table, one schema), not only as an all-or-nothing SQL script.
Date-partitioned object keys — $YEAR/$MONTH/$DAY/ in the S3 key means listing or expiring backups by age is a prefix operation, not something that requires parsing filenames.
Runs where the data already is — the container sits on the same internal network as db, so the dump never crosses a public boundary while it's produced; the separate aws s3 cp transfer is encrypted in transit over TLS.
Disposable by design — the container installs its own tooling (aws-cli) on start and exits when the dump finishes; there's no long-running backup agent to patch or babysit.

The image contains code, not configuration — the same profile definition, pointed at a different env file, is what makes this job identical in dev, staging, or production; only which bucket and which database it talks to changes.

What this is, and isn't

This isn't a replacement for a managed backup service with point-in-time recovery, retention policies, and a tested restore button — a self-hosted Postgres container on a VPS never had one to begin with. It's the minimum viable substitute: cron, pg_dump, the AWS CLI, and Compose, reviewable in a diff like the rest of the stack, in exchange for not paying for a managed database.