Guides / Back up PostgreSQL to S3

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.

The backup pipeline: on the database server, pg_dump pipes into age, which encrypts with the public key, then into aws s3 cp with a key that cannot delete, into a bucket under Object Lock. The private key is kept off the server and is only used to restore. database server pg_dump --format=custom age -r age1... public key only, cannot decrypt aws s3 cp - key can write, cannot delete acme-db-backups Object Lock, compliance, 14 days read to restore backup-key.txt private key, kept off the server
Nothing on the server can read an old backup or delete one. The private key stays somewhere else.

What you need

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.

age-keygen -o backup-key.txt
# prints: Public key: age1...

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

#!/usr/bin/env bash
set -euo pipefail
 
DB_URL="postgres://backup@db.internal:5432/app"
RECIPIENT="age1..." # the public key from step 1
BUCKET="acme-db-backups"
KEY="app/$(date -u +%Y/%m/%d/app-%Y%m%dT%H%M%SZ).dump.age"
 
pg_dump --format=custom --no-owner "$DB_URL" \
  | age -r "$RECIPIENT" \
  | aws s3 cp - "s3://$BUCKET/$KEY"
 
echo "uploaded s3://$BUCKET/$KEY"

Why each part is there:

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
The script above, first with 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.

{"Version": "2012-10-17", "Statement": [
  {"Effect": "Allow", "Action": ["s3:PutObject", "s3:AbortMultipartUpload"],
   "Resource": "arn:aws:s3:::acme-db-backups/*"}
]}

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

# /etc/cron.d/pg-backup: 02:15 UTC every night
15 2 * * * backup /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

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

aws s3api put-bucket-lifecycle-configuration --bucket acme-db-backups \
  --lifecycle-configuration '{"Rules": [{"ID": "expire-backups", "Status": "Enabled",
    "Filter": {"Prefix": "app/"},
    "Expiration": {"Days": 30},
    "NoncurrentVersionExpiration": {"NoncurrentDays": 1},
    "AbortIncompleteMultipartUpload": {"DaysAfterInitiation": 7}}]}'

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.

6. Restore

createdb app_restored
aws s3 cp "s3://acme-db-backups/app/2026/09/29/app-20260929T021500Z.dump.age" - \
  | age -d -i backup-key.txt \
  | pg_restore --exit-on-error --no-owner -d app_restored

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.