How to back up PostgreSQL to S3 with pg_dump
You can build a nightly PostgreSQL backup to S3 from three standard tools:
pg_dump, age for encryption and the AWS CLI. The script below stops
on the first error and encrypts the dump before it leaves the server. The bucket refuses to
delete a backup for a set number of days, then deletes it after 30. Any S3-compatible
storage with Object Lock and lifecycle rules works.
What you need
pg_dumpat the server's major version or newer. An olderpg_dumprefuses to dump a newer server. Check withpg_dump --versionandSELECT version();.- A role that can read every table, for example one granted
pg_read_all_data(PostgreSQL 14 and later). age, a small file encryption tool packaged for most Linux distributions and Homebrew.- The AWS CLI and a bucket with Object Lock turned on. The Object Lock guide covers creating one.
1. Make an encryption key
Generate the key on your laptop or in your secrets manager, not on the database server. The server only needs the public half, so a compromised server cannot read old backups.
Keep backup-key.txt in at least two places that do not depend on the server or
the bucket, such as your password manager and an offline copy. It is the only key that opens
these backups.
2. The backup script
Why each part is there:
set -o pipefailmakes the script fail whenpg_dumpfails. Without it, a pipeline's exit status is the last command's, so a dump that dies half way still uploads a truncated file and exits 0, and the log says the backup worked.--format=customis compressed, restores selectively, and can be read back withpg_restorewithout a database.- Streaming means no unencrypted copy ever touches the server's disk.
For a stream larger than 50 GB, give
aws s3 cpan--expected-sizein bytes so it picks large enough upload parts. - A timestamped key never overwrites an earlier backup, which a locked bucket would refuse anyway.
Here is what pipefail changes. The role below cannot read one of the
database's two tables, and the upload is swapped for a local file so it runs anywhere:
./no-pipefail.sh; echo "exit $?"
pg_dump: error: query failed: ERROR: permission denied for table payouts
pg_dump: detail: Query was: LOCK TABLE public.orders, public.payouts IN ACCESS SHARE MODE
uploaded app.dump.age
exit 0
ls -l app.dump.age
-rw-r--r-- 1 postgres postgres 200 Oct 31 14:50 app.dump.age
./with-pipefail.sh; echo "exit $?"
pg_dump: error: query failed: ERROR: permission denied for table payouts
pg_dump: detail: Query was: LOCK TABLE public.orders, public.payouts IN ACCESS SHARE MODE
exit 1
set -eu, then with set -euo pipefail, writing to app.dump.age instead of aws s3 cp. Run in a postgres:17 container with pg_dump 17.11 and age 1.2.1. The 200-byte file has no rows in it.3. Give the server a key that cannot delete
The access key on the database server should be able to write backups and nothing else. If it can delete, anyone who takes over the server can remove your backups before they touch the database.
With a default retention rule on the bucket, every object this key writes is locked
without the key needing s3:PutObjectRetention. Keep a separate read-only key
for restores.
4. Schedule it, and notice when it stops
Cron sends no alert when a job fails, and none when it stops running because the server was rebuilt without it. Add a check that expects a backup every day and alerts when one does not arrive: a heartbeat URL the script calls on success, or a daily job that lists the bucket and checks the newest object's date and size.
5. Delete backups after 30 days
An Object Lock bucket keeps every version of every object, so the rule needs two parts.
Expiration hides a backup after 30 days by putting a delete marker on top of
it. NoncurrentVersionExpiration deletes the hidden version one day later.
Without the second part, the backups disappear from aws s3 ls and stay on
your bill.
- S3 never deletes a locked version. With the 14-day lock in the diagram above, each backup goes on about day 31. If you lock for longer than you keep, a backup stays, and you pay for it, until its lock ends.
- Dates round up. S3 rounds each rule's date to the next midnight UTC and removes objects in the background, so a backup can outlive its date by a day or so.
- Unfinished uploads cost money too.
AbortIncompleteMultipartUploadremoves the parts of an upload that never completed, which no listing shows you. - Set the rule with your own credentials. The backup key from step 3 should not be able to change it.
6. Restore
pg_restore reads a custom-format archive from standard input, but a parallel
restore with -j needs a file it can seek in. For a large database, decrypt to a
file first and restore that with -j 4 or more.
7. Test the restore on a schedule
Everything above produces a file. None of it proves the file restores. Run step 6 into a
scratch database regularly and compare row counts with the source.
How to test that a backup restores has
the queries, including how to count from the same snapshot pg_dump read.
How SafeGrd does it
SafeGrd's daemon does every step here but step 5. It dumps the schema with pg_dump and the
rows over binary COPY from one snapshot, compresses with zstd, encrypts with
age on your server and writes to your bucket under Object Lock, with a host key that cannot
delete. It alerts you when a backup fails or does not run. On every plan it restores the
newest backup on a schedule and checks every table's row count against the manifest it
wrote at backup time. See PostgreSQL for setup, or
pricing.
Deleting old backups from your own bucket is off by default, because it needs a key that
can delete. Set storage.expire_after_lock: true and the daemon deletes each
backup a day after its lock ends, never a surface's newest. Or keep the lifecycle rule from
step 5.