PostgreSQL Backups to S3: The 3-2-1 Rule, Least-Privilege IAM and Tested Restores

Everyone has backups until they need to restore one. That's when the problems show up: the file is empty, the cron job stopped running three months ago, the backup lived on the same disk that failed or, worse, the attacker who got into the server deleted the backups too.

In this post I build a PostgreSQL-to-S3 backup system designed to avoid exactly those scenarios.

The 3-2-1 rule

It's the foundation of any backup strategy:

• 3 copies of your data (the original and two backups).
• 2 different types of storage.
• 1 copy off-site.

For a database on a VPS, that means: the production database, a recent local backup for quick restores and a copy in S3 (or another provider) off the server. These days it's common to add one more point: an immutable copy that nobody can delete for a set period, not even you.

What kind of backup do you need

This post focuses on pg_dump, which covers the vast majority of cases. If your business can't afford to lose the transactions made since the last backup, the next step is point-in-time recovery with pgBackRest or WAL-G.

Step 1: the bucket

Create a dedicated bucket in the region closest to your server, with:

• Block public access turned on (the default).
• Server-side encryption (SSE-S3 is enabled by default).
• Versioning turned on, so overwriting or deleting an object doesn't remove the previous version.

aws s3api create-bucket --bucket my-company-pg-backups \
  --region sa-east-1 \
  --create-bucket-configuration LocationConstraint=sa-east-1

aws s3api put-bucket-versioning --bucket my-company-pg-backups \
  --versioning-configuration Status=Enabled

Step 2: an IAM user that can only upload

This is the most overlooked part. If the server uses a key with admin permissions or s3:*, anyone who compromises the server can delete all your backups. The server only needs to write.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "UploadBackupsOnly",
      "Effect": "Allow",
      "Action": ["s3:PutObject"],
      "Resource": "arn:aws:s3:::my-company-pg-backups/postgres/*"
    }
  ]
}

No s3:DeleteObject, no s3:GetObject, no listing. Restores use a different user or role with read permissions, whose credentials don't live on the production server:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ReadBackups",
      "Effect": "Allow",
      "Action": ["s3:GetObject"],
      "Resource": "arn:aws:s3:::my-company-pg-backups/postgres/*"
    },
    {
      "Sid": "ListBackups",
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::my-company-pg-backups",
      "Condition": { "StringLike": { "s3:prefix": ["postgres/*"] } }
    }
  ]
}

Set up the write-only user's credentials on the server under a dedicated profile:

aws configure --profile backups
chmod 600 ~/.aws/credentials

Step 3: the backup script

It runs pg_dump in custom format (compressed and selectively restorable) and streams it to S3 without keeping the full file on disk. It also backs up roles and keeps a short-lived local copy for quick restores.

File /opt/scripts/backup-pg.sh:

#!/usr/bin/env bash
set -euo pipefail

DB="${DB_NAME:?DB_NAME is not set}"
BUCKET="s3://my-company-pg-backups/postgres/$(hostname)"
LOCAL_DIR="/var/backups/postgres"
LOCAL_DAYS=3
DATE_PATH="$(date -u +%Y/%m/%d)"
STAMP="$(date -u +%Y%m%dT%H%M%SZ)"
export AWS_PROFILE=backups

mkdir -p "$LOCAL_DIR"

pg_dump -Fc --no-owner -d "$DB" \
  | tee "$LOCAL_DIR/${DB}_${STAMP}.dump" \
  | aws s3 cp - "$BUCKET/$DATE_PATH/${DB}_${STAMP}.dump" \
      --storage-class STANDARD_IA \
      --expected-size 10737418240   # adjust if your backup exceeds 50 GB

pg_dumpall --globals-only \
  | aws s3 cp - "$BUCKET/$DATE_PATH/globals_${STAMP}.sql"

pg_restore --list "$LOCAL_DIR/${DB}_${STAMP}.dump" > /dev/null

find "$LOCAL_DIR" -name "*.dump" -mtime +"$LOCAL_DAYS" -delete

echo "$(date -Is) OK ${DB}_${STAMP}.dump"

Key details:

• set -o pipefail (included in set -euo pipefail) makes the script fail if any part of the pipeline fails. Without it, a pg_dump that dies halfway uploads a truncated file and the script still exits "successfully".
• pg_restore --list confirms the file is a valid, readable dump.
• Dates are in UTC so daylight saving time changes don't catch you off guard.

Step 4: schedule it and find out when it fails

A systemd timer gives you logs and retries for free:

File /etc/systemd/system/backup-pg.service:

[Unit]
Description=PostgreSQL backup to S3
OnFailure=notify-failure@%n.service

[Service]
Type=oneshot
User=postgres
Environment=DB_NAME=app
ExecStart=/opt/scripts/backup-pg.sh

File /etc/systemd/system/backup-pg.timer:

[Unit]
Description=Daily PostgreSQL backup

[Timer]
OnCalendar=*-*-* 06:15:00 UTC
Persistent=true
RandomizedDelaySec=10m

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable --now backup-pg.timer
systemctl list-timers | grep backup-pg

Persistent=true runs the missed backup if the server was off at the scheduled time. The notify-failure@ unit can be a simple curl to ntfy, Slack or an email; what matters is that a failed backup makes noise.

And to cover the case where the script doesn't even run, use a heartbeat monitor (Healthchecks, Uptime Kuma in push mode): the script pings it when it finishes and, if no ping arrives within 25 hours, it alerts you.

Step 5: retention with lifecycle rules

Don't delete backups from the server: let S3 do it with lifecycle rules.

{
  "Rules": [
    {
      "ID": "pg-backups-retention",
      "Status": "Enabled",
      "Filter": { "Prefix": "postgres/" },
      "Transitions": [{ "Days": 30, "StorageClass": "GLACIER_IR" }],
      "Expiration": { "Days": 365 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 30 }
    }
  ]
}

aws s3api put-bucket-lifecycle-configuration \
  --bucket my-company-pg-backups \
  --lifecycle-configuration file://lifecycle.json

With this, backups move to cheaper storage after 30 days and are deleted after a year. Adjust the periods to your obligations: some data (accounting, tax or payroll records) must be kept for several years. Also check the minimum billable storage duration of each storage class before moving files to cold tiers.

Step 6: immutability with Object Lock

To protect yourself from an attacker who obtains credentials with broader permissions (or from your own mistakes), S3 Object Lock prevents objects from being deleted or overwritten for a set period. You enable it when creating the bucket (or later, with versioning turned on):

aws s3api put-object-lock-configuration \
  --bucket my-company-pg-backups \
  --object-lock-configuration '{
    "ObjectLockEnabled": "Enabled",
    "Rule": { "DefaultRetention": { "Mode": "GOVERNANCE", "Days": 30 } }
  }'

• GOVERNANCE: only users with a special permission can bypass the lock.
• COMPLIANCE: nobody can delete the object before the period ends, not even the root account. Use it carefully: it binds you too.

Step 7: test the restore (for real)

A backup you've never restored is a hypothesis. This script runs on another machine with the read-only credentials. It downloads the latest backup, restores it into a throwaway PostgreSQL instance and runs a sanity-check query:

File /opt/scripts/test-restore.sh:

#!/usr/bin/env bash
set -euo pipefail
export AWS_PROFILE=restore

PREFIX="s3://my-company-pg-backups/postgres/prod-server"
LATEST=$(aws s3 ls "$PREFIX/" --recursive | grep '\.dump$' | sort | tail -1 | awk '{print $4}')
echo "Testing: $LATEST"

aws s3 cp "s3://my-company-pg-backups/$LATEST" /tmp/test.dump

docker run -d --rm --name pg-test -e POSTGRES_PASSWORD=test postgres:16-alpine
until docker exec pg-test pg_isready -U postgres > /dev/null 2>&1; do sleep 1; done

docker exec pg-test createdb -U postgres restored
docker cp /tmp/test.dump pg-test:/tmp/test.dump
docker exec pg-test pg_restore -U postgres -d restored --no-owner /tmp/test.dump

ROWS=$(docker exec pg-test psql -U postgres -d restored -tAc \
  "SELECT count(*) FROM users")
docker stop pg-test > /dev/null
rm -f /tmp/test.dump

if [ "$ROWS" -lt 1 ]; then
  echo "ERROR: restore contains no data"; exit 1
fi
echo "OK: restore with $ROWS users"

Schedule it once a week. Besides confirming the backup works, it tells you how long a restore takes, a number you'll want to know before an emergency.

What if I don't use AWS?

Everything above works with S3-compatible services such as Cloudflare R2, Backblaze B2, Wasabi or a self-hosted MinIO, by adding the endpoint:

aws s3 cp file.dump s3://bucket/path/ --endpoint-url https://<account>.r2.cloudflarestorage.com

Check what each one supports: not all of them offer the same lifecycle rules, storage classes or Object Lock. And if your data is sensitive, you can encrypt it before uploading with age or gpg, so not even the provider can read it (keeping the decryption key somewhere safe and separate).

Final checklist

• Private, encrypted bucket with versioning
• Server IAM user with only s3:PutObject
• Read credentials kept off the production server
• Script with set -euo pipefail and dump verification
• Roles backed up with pg_dumpall --globals-only
• Timer + failure alert + heartbeat monitor
• Lifecycle rules that match your legal retention periods
• Object Lock for an immutable copy
• Automated weekly restore test

Conclusion

A good backup system isn't measured by how many copies it keeps, but by how certain you are that you can restore. With least privilege, automatic retention, immutability and regular testing, a backup stops being a file you hope works and becomes one you know works.