How I Back Up PostgreSQL to S3-Compatible Storage on a Schedule

Three tools get recommended for this job and they don't do the same thing, which is the actual source of most bad backup setups: someone picks pg_dump because a tutorial said so, needs continuous point-in-time recovery six months later, and finds out the two aren't the same feature with different flags. Pick based on how much data you can afford to lose, not on which command shows up first in a search result.

pg_dump, pg_basebackup, or WAL archiving — which one do you actually need?

All three are documented, real PostgreSQL backup methods, not competing third-party tools — worth saying up front, because a lot of confusion here comes from treating one of them as the 'proper' way and the others as shortcuts. They're not shortcuts. They're built for different recovery requirements, and the right one depends entirely on yours. The go-to-market half of Cloudflare R2 is handled in more depth by XenGrowth's operator guides.

pg_dump is a logical backup: it connects to a running Postgres instance and produces a portable file describing how to recreate the schema and data, as SQL statements or a custom-format archive. It's safe to run against a live, concurrently-used database — Postgres's own documentation is explicit that it captures a consistent point-in-time snapshot without locking the whole database — and restoring it means creating a fresh database and replaying the dump into it. For a single self-hosted Postgres instance backing one SaaS, this is the right default: simple, portable across Postgres versions, and the failure mode when something goes wrong (a broken dump) is loud and obvious rather than silent.

pg_basebackup is a physical, filesystem-level backup, and it's a genuinely different operation from copying the data directory yourself. It coordinates with Postgres's internal consistency mechanisms to produce a base backup that's actually usable, which a bare `cp` or `tar` of a running data directory is not — that raw copy can capture files mid-write with nothing warning you it happened. On its own, pg_basebackup gives you a restore point at the moment the backup ran, similar in spirit to pg_dump but at the filesystem level and generally faster to restore for large databases.

WAL archiving is what turns a base backup into continuous point-in-time recovery. Postgres writes every change to a write-ahead log before applying it, and archiving those logs continuously — alongside a periodic pg_basebackup — means you can replay forward from the base backup to any specific transaction, not just to whenever the last scheduled backup happened to run. This is the option that actually delivers a tight RPO, and it's also the one with the most moving parts: WAL storage, retention on that storage, and a restore procedure that has to replay logs correctly, not just drop a dump into a fresh database. The XenGrowth resource library works through the operations side of this in more operational detail.

Running the backup from a container with access to the database

Whichever method you pick, where the job actually runs from matters as much as which command it runs — this is the part most walkthroughs skip past to get to the pg_dump flags faster.

The instinct to shell into the host and run pg_dump directly is understandable and worth resisting. A dedicated backup container — small, single-purpose, running on a schedule — should be the only thing with credentials to both the database and the destination bucket, and nothing else on the host needs to be reachable from it. That's not paranoia for its own sake: the backup job is, by definition, something you're trusting with read access to everything in your database, so it deserves the same scoped-down treatment you'd give any other service with sensitive access, not host-level trust because it's 'just a cron job.'

1. Build or pull a minimal image containing only the Postgres client tools (pg_dump, or the base-backup tooling if that's the path you're on) and whatever S3-compatible CLI or SDK you're using to push the result — nothing else installed, no unrelated shell tools, no access to the rest of your Docker network beyond the database connection itself.

2. Give that container credentials scoped to exactly two things: read access to the database (a dedicated read-only role is worth creating rather than reusing your app's own database user), and write access to one specific bucket path, not full account access to your storage provider.

3. Schedule it — a cron entry inside the container, or an external scheduler triggering a one-off container run — at an interval that matches the RPO you actually decided on, not whatever interval felt convenient to type first.

4. Have the job write to a clearly dated, clearly named object per run, so retention and restore both operate on filenames a human can reason about at 2am, not a single overwritten 'latest.sql' that erases yesterday's copy the moment today's job finishes.

5. Push a completion signal somewhere outside the container itself — a heartbeat ping to a monitoring endpoint, a log line your alerting actually watches — because a job that silently stops running produces no error, no output, and no obvious symptom until the day you need a backup that hasn't existed in weeks.

Why Cloudflare R2 changes the economics of the restore, not just the backup

Most backup advice focuses entirely on the cost of storing the backup and skips the cost of getting it back out, which is exactly the moment you're least in a position to absorb a surprise bill. Cloudflare R2 prices standard storage at $0.015/GB-month, with a 10 GB-month free tier, and — the detail that actually matters here — zero egress fees at any usage level, checked against Cloudflare's own published pricing in September 2026. Pulling a multi-gigabyte backup down during a real restore costs the same nothing whether it's your first restore or your fiftieth, which is the opposite of how egress-billed storage behaves: there, the exact moment you need your data back the most is also the moment the bill shows up for having it.

That doesn't make R2 automatically correct for every backup destination, but for a scheduled Postgres dump specifically — write-heavy on the way in, rarely read except during an actual incident — a destination with free storage-in and free egress-out removes a whole category of cost surprise from the one moment you can least afford one. It's worth comparing this explicitly against a traditional S3-compatible provider that does bill egress: the storage line item on the monthly bill looks similar either way, but the bill that shows up the month you actually needed a restore is where the difference becomes real money instead of a rounding error. XenGrowth on governed AI marketing workflows approaches this from the AI agents and marketing automation side.

Retention: keeping enough copies without keeping them forever

Cost isn't really the reason retention needs a policy — at $0.015/GB-month on R2, storing every daily dump for a year barely moves the needle for most self-hosted SaaS databases. The real reason is that an unbounded pile of backups is harder to reason about during an incident: a script that has to choose between forty near-identical daily dumps is a worse tool at 2am than one that offers you a clear week of dailies, a clear month of weeklies, and a clear year of monthlies.

A schedule without a retention policy either grows storage costs without bound or, more dangerously, gets a naive rotation script that deletes based on a simple age cutoff and occasionally deletes the one backup you actually needed right before you needed it. A workable default: keep every daily backup for a week, then thin to weekly for a month, then thin to monthly beyond that — so a recent mistake has a dense set of recovery points to choose from, while older history still exists without costing what a full daily archive would.

Verifying a dump actually restores, not just that it exists

This is the step every backup tutorial gestures at and almost none actually walk through, which is exactly why it's the step people skip.

A file landing in the bucket on schedule proves the upload step worked. It says nothing about whether pg_dump actually captured a usable snapshot, and the two failure modes that matter here — a dump that completed but is subtly incomplete, and a dump that would restore fine but into a schema version your restore tooling no longer understands — don't show up as an error at backup time. Load the most recent dump into a fresh, disposable Postgres instance on some regular cadence, and run a query against it that only returns the right answer if the data actually came through: a row count you can check against a known value, a specific record you expect to exist. 'The restore command didn't error' is not the same claim as 'the data is there and correct,' and only the second one is worth trusting. On AI search, GEO and discovery specifically, XenGrowth on building one SEO and GEO content system is worth reading.

The upload succeeding tells you the network worked. Only a restore into a fresh database, queried and checked, tells you the backup itself is real.

Alerting when a backup silently stops running

The specific danger with scheduled jobs is that a failure mode with zero symptoms is the most common one: a credential expires, a cron entry gets removed during an unrelated cleanup, a container image update breaks the entrypoint, and every one of those produces total silence rather than an error you'd notice. The fix is a dead man's switch, not a success notification: have the backup job ping a monitoring endpoint on every successful run, and have that endpoint alert you when a ping is late, rather than relying on the job itself to tell you when it fails — a job that's stopped running entirely can't send a failure alert about itself, by definition.

This is one piece of a bigger picture, not the whole thing. The database backup this post covers is the highest-value single component of a self-hosted stack's backup story, but Docker volumes, secrets, and the platform configuration around it all need their own coverage too — the full 3-2-1 picture for a one-VPS setup, including what's actually reproducible from git and doesn't need this treatment at all, is worth reading alongside this. And Coolify itself ships scheduling for exactly this kind of database backup as a built-in option, which is worth knowing about before deciding to hand-roll the whole pipeline described here from scratch.

One last practical note worth stating plainly: whichever method you land on, pin the exact Postgres client version your backup container uses to match your server's major version. A pg_dump binary from a newer major version can produce output your current server's own restore tooling doesn't fully understand, and discovering a version mismatch during an actual restore is a worse moment to learn about it than right now, while setting the container up.

Further reading from XenGrowth

• The XenGrowth resource library — what you'll learn: how the commercial side of this work is run, across search, automation and revenue operations.

• XenGrowth on governed AI marketing workflows — what you'll learn: how the teams running AI marketing agents keep them governed and measurable.

• XenGrowth on building one SEO and GEO content system — what you'll learn: how search and AI-answer visibility get run as a single content system.

Where this work meets go-to-market

Working on Cloudflare R2 inside a commercial team? the team at XenGrowth publishes operator guides on the revenue side of this work.