Bash scripts to automate Postgres backups to S3

Most backup scripts on the internet are one line: pg_dump | aws s3 cp. It works until the night the dump fails halfway, and S3 quietly stores a truncated file that looks like every other backup. You find out months later, during the restore you needed.

This is the version I run in production. One Bash script dumps the database, proves the dump is readable, uploads it to S3 with a key that sorts by date, and reports success or failure. A systemd timer runs it nightly, an IAM policy lets the server write backups but never delete them, lifecycle rules keep 35 daily and 13 monthly copies, and a second script restores the latest backup into a scratch database so you know it works.

Part of my guide to AWS infrastructure for small SaaS teams.

TL;DR#

• Dump with pg_dump --format=custom to a local file, verify it with pg_restore --list, then upload. Don't stream straight to S3: a failed dump becomes a truncated "backup".
• Run it from a systemd timer with Persistent=true, under flock, with a healthcheck ping on success and failure.
• Give the server s3:PutObject only, on one prefix, and turn on versioning or Object Lock, so nobody with the server's credentials can delete your backups.
• Lifecycle rules keep 35 daily and 13 monthly copies and clean up failed uploads.
• Test the restore on a schedule, and time it: that's your real recovery time.

Before you start#

• A pg_dump at least as new as the server. pg_dump refuses to dump a newer server. Install the matching client from the PostgreSQL apt repository (postgresql-client-17 for a PostgreSQL 17 server, for example).
• A read-only database role for backups. On PostgreSQL 14 and later, one grant covers it:

CREATE ROLE backup LOGIN PASSWORD 'a-long-random-password';
GRANT pg_read_all_data TO backup;

• An S3 bucket, ideally in a separate AWS account, so that a compromised production account can't delete its own backups. Turn on versioning.
• The AWS CLI v2 on the server, and an instance role, not access keys.

The backup script#

Save as /usr/local/bin/pg-backup.sh, owned by root and mode 755:

#!/usr/bin/env bash
# Backs up one PostgreSQL database to S3: dump, verify, upload, clean up.
# Configured by environment variables; see /etc/pg-backup.env.
set -Eeuo pipefail

: "${DATABASE_URL:?DATABASE_URL is required}"
: "${S3_BUCKET:?S3_BUCKET is required}"
S3_PREFIX="${S3_PREFIX:-postgres/app}"
WORK_DIR="${WORK_DIR:-/var/lib/pg-backup}"
HEALTHCHECK_URL="${HEALTHCHECK_URL:-}"

STAMP="$(date -u +%Y-%m-%dT%H%M%SZ)"
FILE="$WORK_DIR/app-$STAMP.dump"

log() { printf '%s %s\n' "$(date -u +%FT%TZ)" "$*"; }
# Reports to a monitor like healthchecks.io: /start, /fail, or "" for success. Never fails the backup.
ping() { if [ -n "$HEALTHCHECK_URL" ]; then curl -fsS -m 10 --retry 3 "$HEALTHCHECK_URL$1" > /dev/null || true; fi; }

trap 'rm -f "$FILE"' EXIT
trap 'log "Backup FAILED at line $LINENO"; ping /fail' ERR

mkdir -p "$WORK_DIR"

# One backup at a time, even if a slow run overlaps the next.
exec 9> "$WORK_DIR/.lock"
flock -n 9 || { log "Another backup is still running"; exit 1; }

ping /start

log "Dumping to $FILE"
pg_dump --format=custom --no-password --file="$FILE" "$DATABASE_URL"

# A dump pg_restore can't read isn't a backup.
pg_restore --list "$FILE" > /dev/null
log "Dump verified: $(stat -c %s "$FILE") bytes"

KEY="$S3_PREFIX/daily/$(date -u +%Y/%m/%d)/app-$STAMP.dump"
aws s3 cp "$FILE" "s3://$S3_BUCKET/$KEY" --storage-class STANDARD_IA --only-show-errors
log "Uploaded s3://$S3_BUCKET/$KEY"

# The first backup of each month is kept longer, under monthly/.
if [ "$(date -u +%d)" = "01" ]; then
  aws s3 cp "$FILE" "s3://$S3_BUCKET/$S3_PREFIX/monthly/app-$STAMP.dump" --storage-class STANDARD_IA --only-show-errors
  log "Kept as this month's backup"
fi

ping ""
log "Backup complete"

What each part is for:

• set -Eeuo pipefail stops at the first failed command, an unset variable or a failure anywhere in a pipe, and -E makes the ERR trap fire inside functions too.
• The custom format (--format=custom) is compressed, restores a single table if you need one, and restores in parallel with pg_restore --jobs. On PostgreSQL 16 or later, --compress=zstd makes smaller dumps faster than the default gzip.
• pg_restore --list reads the dump's table of contents, which fails on a truncated or corrupt file. It takes seconds and catches the failure the one-liner can't.
• The key sorts by date, so the newest backup is always last in a listing, and the prefix makes lifecycle rules simple.
• STANDARD_IA storage costs about half of S3 Standard for files you rarely read. It bills at least 30 days and 128 KB per object, which suits nightly backups kept for a month.
• flock stops a slow backup and the next one from running at the same time.
• The traps delete the local file however the script ends, and report a failure to your monitor before exiting.

Recent versions of the AWS CLI also send a checksum with every upload, and S3 rejects an upload that arrives corrupted.

Configuration#

Keep the settings, including the database password, in /etc/pg-backup.env, owned by root with mode 600:

DATABASE_URL=postgresql://backup:a-long-random-password@app-db.abc123xyz.us-east-1.rds.amazonaws.com:5432/app?sslmode=require
S3_BUCKET=acme-db-backups
S3_PREFIX=postgres/app
WORK_DIR=/var/lib/pg-backup
HEALTHCHECK_URL=https://hc-ping.com/your-check-uuid

WORK_DIR needs free space for one dump. If the database runs in Docker on the same host instead, run pg_dump inside its container: docker exec db pg_dump -U app --format=custom app > "$FILE".

Run it nightly with a systemd timer#

A systemd timer beats cron here: output goes to the journal, missed runs (the server was off at 2 a.m.) run at the next boot, and the service runs with its own limits.

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

[Unit]
Description=Back up PostgreSQL to S3
Wants=network-online.target
After=network-online.target

[Service]
Type=oneshot
EnvironmentFile=/etc/pg-backup.env
ExecStart=/usr/local/bin/pg-backup.sh
DynamicUser=yes
StateDirectory=pg-backup
Nice=10
IOSchedulingClass=idle

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

[Unit]
Description=Nightly PostgreSQL backup

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

[Install]
WantedBy=timers.target

sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer

sudo systemctl start pg-backup.service   # run one now
journalctl -u pg-backup.service -n 50     # read what it did
systemctl list-timers pg-backup.timer     # when it runs next

DynamicUser=yes runs the backup as a throwaway unprivileged user with a read-only view of the system, and StateDirectory gives it /var/lib/pg-backup, the one place it can write (which is why the lock file lives there too). Nice and IOSchedulingClass=idle keep the dump from slowing the app if it shares the server.

Get told when it fails, or doesn't run. The script pings a monitor like healthchecks.io on start, success and failure. The monitor also alerts when no ping arrives at all, which catches the failure no script can report: the timer never firing.

Write-only access to the bucket#

The server needs to add backups, never read or delete them. If an attacker gets onto it, they shouldn't be able to wipe your history:

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

That's not enough on its own, because uploading to an existing key overwrites it. Turn on versioning, so an overwritten or deleted backup keeps its previous version, or go further with S3 Object Lock and a default retention of 35 days, so no one, not even an administrator in compliance mode, can delete a backup before then.

If the bucket lives in another account, as it should, allow this role in the bucket policy too, and make the bucket owner the owner of uploaded objects (the default for new buckets).

Keep 35 days and 13 months#

Lifecycle rules do the retention, so the script never needs delete permission:

{
  "Rules": [
    {
      "ID": "daily-35-days",
      "Status": "Enabled",
      "Filter": { "Prefix": "postgres/app/daily/" },
      "Expiration": { "Days": 35 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 7 }
    },
    {
      "ID": "monthly-13-months",
      "Status": "Enabled",
      "Filter": { "Prefix": "postgres/app/monthly/" },
      "Transitions": [{ "Days": 30, "StorageClass": "GLACIER_IR" }],
      "Expiration": { "Days": 400 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 7 }
    },
    {
      "ID": "abort-incomplete-uploads",
      "Status": "Enabled",
      "Filter": {},
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 2 }
    }
  ]
}

aws s3api put-bucket-lifecycle-configuration \
  --bucket acme-db-backups \
  --lifecycle-configuration file://lifecycle.json

Monthly backups move to Glacier Instant Retrieval after 30 days, cheaper still and readable in milliseconds. The last rule clears out the parts of uploads that failed halfway, which are invisible in the console but billed like any other storage.

Test the restore#

A backup you've never restored is a hope. This script, run weekly from a machine with read access to the bucket (not the database server), restores the latest backup into a throwaway PostgreSQL container and checks it:

#!/usr/bin/env bash
# Restores the latest backup into a scratch PostgreSQL container and checks the data is recent.
set -Eeuo pipefail

: "${S3_BUCKET:?S3_BUCKET is required}"
S3_PREFIX="${S3_PREFIX:-postgres/app}"
PG_IMAGE="${PG_IMAGE:-postgres:17}"   # the same major version as production
WORK="$(mktemp -d)"
trap 'rm -rf "$WORK"; docker rm -f restore-test > /dev/null 2>&1 || true' EXIT

LATEST=$(aws s3api list-objects-v2 --bucket "$S3_BUCKET" --prefix "$S3_PREFIX/daily/" \
  --query 'sort_by(Contents, &LastModified)[-1].Key' --output text)
echo "Restoring s3://$S3_BUCKET/$LATEST"
aws s3 cp "s3://$S3_BUCKET/$LATEST" "$WORK/latest.dump" --only-show-errors

docker run -d --name restore-test -e POSTGRES_PASSWORD=restore -p 127.0.0.1:55432:5432 "$PG_IMAGE" > /dev/null
# Over TCP, so we don't catch the temporary server the image runs while it initialises.
until pg_isready -q -h 127.0.0.1 -p 55432; do sleep 1; done
docker exec restore-test createdb -U postgres app

URL="postgresql://postgres:restore@127.0.0.1:55432/app"
START=$(date +%s)
pg_restore --no-owner --no-privileges --exit-on-error --jobs 4 --dbname "$URL" "$WORK/latest.dump"
echo "Restored in $(( $(date +%s) - START ))s"

# Checks that only pass on real, recent data. Adjust them to your schema.
psql "$URL" -v ON_ERROR_STOP=1 -At -c "SELECT count(*) FROM users" | awk '$1 > 0 { ok = 1 } END { exit !ok }'
psql "$URL" -v ON_ERROR_STOP=1 -At -c "SELECT max(created_at) > now() - interval '2 days' FROM orders" | grep -qx t

echo "Restore test passed"

• --no-owner --no-privileges restores everything as the scratch database's user, so the roles from production don't need to exist.
• --exit-on-error fails on the first error instead of printing warnings and carrying on.
• The checks are what make it a test: a restore that "succeeds" into empty tables, or from a backup that stopped updating last month, fails here.
• The time it prints is your real recovery time for a full restore. If it's longer than the business can be down, you need a faster path, like RDS point-in-time recovery, before you need it.

Wire it to the same monitor, or run it in a scheduled GitHub Actions workflow with read-only credentials.

Why not stream the dump straight to S3?#

pg_dump … | aws s3 cp - s3://… saves disk space, and it's tempting. The problem is what happens on failure: when pg_dump dies halfway, the pipe closes, aws s3 cp sees a normal end of input and completes the upload. pipefail makes your script exit with an error, but the truncated object is already in S3, named like a good backup.

If the database is too big to dump to local disk, stream to a temporary key, verify, and only then copy it to its final name, with a lifecycle rule that deletes anything left under the temporary prefix. For streams over 50 GB, aws s3 cp also needs --expected-size so it can size the upload's parts.

pg_dump, RDS snapshots, or both?#

Use both: RDS point-in-time recovery for "undo the last hour", and logical dumps in another account for "the account is compromised", "we're moving providers" or "restore one table from last Tuesday". Past a few hundred GB, pg_dump gets slow, and physical backup tools like pgBackRest or WAL-G suit self-managed PostgreSQL better.

The checklist#

• pg_dump matches or is newer than the server's major version
• A read-only backup role with pg_read_all_data
• Dumps are verified with pg_restore --list before upload
• A systemd timer with Persistent=true, under flock
• A monitor alerts on failure and on missing runs
• The server can only PutObject to its own prefix
• The bucket has versioning or Object Lock, ideally in a separate account
• Lifecycle rules for daily and monthly retention and incomplete uploads
• A weekly restore test checks real, recent data and records the time
• RDS point-in-time recovery is on as well

FAQ#

How do I back up PostgreSQL to S3 automatically?#

Write a Bash script that runs pg_dump --format=custom to a local file, verifies it with pg_restore --list, and uploads it with aws s3 cp to a date-based key. Run it nightly from a systemd timer or cron, give the server's IAM role only s3:PutObject on the backup prefix, enable bucket versioning, and use S3 lifecycle rules to expire old backups.

Should I use pg_dump or RDS snapshots?#

Both. RDS automated backups give point-in-time recovery to any second within the retention period, but restore only as a new RDS instance in the same account and Region. pg_dump backups are portable: they restore into any PostgreSQL server, can restore a single table, and survive the deletion of the instance or the account if stored elsewhere.

How do I restore a pg_dump backup?#

For a custom-format dump, create an empty database and run pg_restore --no-owner --no-privileges --exit-on-error --jobs 4 --dbname <connection string> backup.dump. Plain SQL dumps are restored with psql -f backup.sql instead. Use a pg_restore at least as new as the pg_dump that made the file.

Which pg_dump format should I use?#

The custom format (--format=custom) for most backups: it's compressed, verifiable with pg_restore --list, can restore single tables and restores in parallel. The directory format also dumps in parallel with --jobs, which helps large databases. Plain SQL is readable but can't restore selectively or in parallel.

How long should I keep database backups?#

A common policy for small SaaS products is 30–35 daily backups plus 12–13 monthly ones, adjusted to any legal or contractual requirements. S3 lifecycle rules can expire each set automatically and move older ones to cheaper storage classes like Glacier Instant Retrieval.

Related reading#

• AWS infrastructure for small SaaS teams: what you actually need
• Running database migrations safely in CI/CD
• S3 and CloudFront: signed URLs and caching done right
• Ubuntu server hardening checklist for a fresh EC2