How to Automate PostgreSQL Backups to S3 on a Schedule

Your managed database already backs itself up every night. So why write a script to copy it somewhere else? For a lot of projects the honest answer is that you don't have to. But there's a specific kind of failure that a provider's own backups can't save you from, and the fix is one scheduled job. This is about when a second copy earns its keep, and how to set one up without turning it into a weekend project.

Doesn't a managed database already back itself up?

It does, and it does it well. Runsite's managed PostgreSQL takes a daily automated backup, keeps it for up to 30 days, and supports point-in-time recovery, so you can roll the database back to any second inside that window. For everyday accidents like a bad migration, a DELETE that forgot its WHERE clause, or the table someone dropped at 2am, that's exactly what you want, and it's already running.

The gap isn't quality. It's that every one of those copies lives in the same place: same provider, same account, same 30-day window. That's a single point of failure dressed up as a backup. The old sysadmin answer to this is the 3-2-1 rule: keep three copies of your data, on two kinds of storage, with one of them somewhere else entirely. A provider snapshot covers the first copy. A dump in your own bucket covers the rest.

Here are a few situations where that second copy stops being paranoia:

• You need to keep backups longer than 30 days. Retention caps out at a month. If an audit, a compliance rule, or your own archive policy says "keep a yearly snapshot for seven years," the provider window can't do that on its own.
• You want a copy you can take with you. A pg_dump file in a bucket you control isn't tied to any platform. You can restore it to a laptop, another host, or a different provider without asking anyone's permission. It's an exit plan you hope to never use and are glad to have.
• You want disaster recovery beyond the provider. Account lockout, a billing dispute, a region-wide incident: rare, but if your only backups live inside the account you just lost access to, they may as well not exist. A separate bucket, ideally a separate account, is cheap insurance.

What a good automated backup actually needs

Before any code, it helps to know what separates a real backup routine from a pg_dump someone ran once and forgot. A few things have to be true:

• It runs on a schedule, without anyone remembering to trigger it.
• It's compressed, because a raw SQL dump of a real database is large and mostly text.
• Each file has a unique, date-stamped name, so a new backup never overwrites yesterday's.
• It fails loudly. A backup job that silently stops working is worse than no backup, because you'll trust it right up until you need it.
• Old copies get cleaned up on a lifecycle policy, so storage doesn't grow forever.

The backup script

The core of it is three commands chained together: dump the database, compress the output, and upload it to object storage.

#!/bin/bash
set -euo pipefail

# Dump, compress, and stream straight to a date-stamped object
pg_dump "$DATABASE_URL" | gzip | \
  aws s3 cp - "s3://my-backups/postgres/$(date +%F).sql.gz"

Reading it left to right: pg_dump reads the whole database from the connection string in $DATABASE_URL and writes plain SQL to standard output. gzip compresses that stream on the fly. The aws s3 cp - command takes the compressed stream and uploads it as a single object, named with the current date so each run lands in its own file. The set -euo pipefail line at the top matters more than it looks. Without it, if pg_dump fails halfway, the script can still upload a truncated file and exit as if nothing went wrong.

One detail trips people up: if your bucket is S3-compatible storage rather than AWS itself (Runsite's is), the CLI needs an `--endpoint-url` flag so it talks to the right host instead of guessing an AWS region. The exact value is in your storage settings. Same command, one extra flag.

Scheduling it as a cron job

A script you have to run by hand isn't automation. The job needs something to trigger it every day, on time, whether or not your laptop is on. That's what managed cron jobs are for: you give it a schedule and a container, and it runs the job for you.

Since each run executes a Docker container, the image you point it at needs pg_dump and the aws CLI available inside it. The official Postgres image with the AWS CLI added covers it, or a small purpose-built image if you'd rather keep it lean. Whatever you pick, match the Postgres client version to your database so the dump format lines up.

A daily backup at 2am is a standard cron expression:

# At 02:00 every day
0 2 * * *

A few settings turn this from "runs the script" into "runs the script safely":

• Timezone. Pin the schedule to a timezone so 2am means 2am where you expect, not wherever the server happens to think it is.
• A timeout. A backup that hangs shouldn't run forever. Give it a generous ceiling and let the platform kill it if it blows past.
• Concurrency set to Forbid. If last night's backup is somehow still running when tonight's is due, you don't want two pg_dumps fighting over the same database. Forbid skips the new run instead of piling on. On Runsite that's the default.
• A failure alert. Wire up an email or webhook notification on a non-zero exit code. This is the part that makes the whole thing trustworthy: you find out the night it breaks, not the morning you need to restore.

Runsite's cron jobs keep a history of every run with its exit code and duration, so you can glance at the log and confirm last night's backup actually finished, not just that it started.

Where the copy lives

The destination is an S3-compatible bucket you own, kept separate from the provider's own snapshots. That separation is the whole point: a separate copy, somewhere else, doing a different job from the managed backup.

Set a lifecycle rule on the bucket to match your retention policy. Maybe daily backups expire after 90 days while the first-of-the-month copy is kept for a year. The bucket handles the cleanup, so the script never has to.

Worth a thought while you're here: encryption. Runsite's object storage encrypts every object at rest with AES-256, so the dump isn't sitting in the clear once it lands. If you want it encrypted before it ever leaves the job, pipe the stream through gpg in the script and hold the key yourself, which keeps the backup readable only to you even if the bucket is ever exposed.

If your database, cron job, and bucket all sit in the same region, the backup never leaves it. On Runsite, PostgreSQL, Cron Jobs, and object storage all run in the EU (Frankfurt), so the dump travels between services in one region instead of crossing the open internet between three different providers.

Restoring from it

A backup you've never restored isn't a backup, it's a hope. Test the round trip at least once so you know the file is good and you know the steps before you need them in a hurry.

Restoring reverses the script: pull the object down, decompress it, and pipe it into psql.

aws s3 cp "s3://my-backups/postgres/2026-06-22.sql.gz" - | \
  gunzip | psql "$TARGET_DATABASE_URL"

The usual snag is role ownership: restore into a database with different roles than the original and psql will complain about missing owners. Adding `--no-owner` to the dump command sidesteps it by leaving ownership to whoever runs the import. That one flag aside, the full process, including restoring into a fresh database and the odd large-object edge case, is worth reading once in the Runsite docs rather than improvising during an incident.

When a second copy is worth it

Not every project needs this. If you're running a side project or an early-stage app, the managed daily backups with 30-day retention and point-in-time recovery are genuinely enough, and adding a second pipeline is effort you could spend elsewhere.

Reach for your own copy when one of these sounds like you:

• You're in a regulated space and 30 days of retention won't satisfy an auditor.
• You'd sleep better knowing you could walk to another provider tomorrow with your data in hand.
• Losing access to your provider account would be an extinction event, not an inconvenience.
• You've already lived through one bad restore and have no intention of repeating it.

The good news is that the whole thing is one script and one scheduled job. You're not building a backup system from scratch; you're adding a second, portable copy on top of the one you already have. Schedule your first backup job and keep a copy that's yours to take anywhere.