How to back up PostgreSQL to S3 with pg_dump

This guide builds a nightly PostgreSQL backup to an S3 bucket with standard tools: pg_dump, age for encryption and the AWS CLI. The script stops on the first error, the file is encrypted before it leaves the server, and the bucket refuses to delete it for a set number of days. It works with any S3-compatible storage that supports Object Lock.

What you need

• pg_dump at the server's major version or newer. An older pg_dump refuses to dump a newer server. Check with pg_dump --version and SELECT 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.

Store backup-key.txt in at least two places that do not depend on the server or the bucket. Without it the backups cannot be decrypted by anyone.

2. The backup script

Why each part is there:

• set -o pipefail makes the script fail when pg_dump fails. 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=custom is compressed, restores selectively, and can be checked with pg_restore --list without a database.
• Streaming means no unencrypted copy ever touches the server's disk. For a stream larger than 50 GB, give aws s3 cp an --expected-size in bytes so it picks large enough upload parts.
• A timestamped key never overwrites an earlier backup, which a locked bucket would refuse anyway.

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. 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.

6. Test the restore on a schedule

Everything above produces a file. None of it proves the file restores. Run step 5 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 all six steps. 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 when a backup fails or does not run, and on paid plans it restores the newest backup on a schedule and checks every table's row count against the manifest it wrote at backup time. See Databases for setup, or pricing.