pg_dump to S3: a scheduled PostgreSQL backup you can restore

The script below dumps one PostgreSQL database with pg_dump, reads the archive back to make sure it is complete, and uploads it to S3 or any S3-compatible bucket: Cloudflare R2, Backblaze B2, Wasabi, MinIO. It works the same against RDS, Neon, Railway, Supabase, or your own server. Run it daily and you have backups. Run the second script on this page weekly and you know they restore.

Download pg-dump-to-s3.sh

#!/usr/bin/env bash
# pg-dump-to-s3.sh: dump one PostgreSQL database, check the archive, upload it
# to S3 or any S3-compatible bucket (R2, B2, Wasabi, MinIO).
# From https://backupdrill.com/guides/pg-dump-to-s3 (MIT licensed).
#
# Needs pg_dump and pg_restore of the same major version as the server, and
# the AWS CLI v2. Configure with environment variables:
#   DATABASE_URL     postgresql://user@host:5432/dbname (password in ~/.pgpass)
#   S3_BUCKET        bucket name
#   S3_PREFIX        folder inside the bucket (default: postgres)
#   AGE_RECIPIENT    optional: age public key; the dump is encrypted before upload
#   AWS_ENDPOINT_URL for anything that is not AWS S3, e.g.
#                    https://<account-id>.r2.cloudflarestorage.com
set -euo pipefail

: "${DATABASE_URL:?DATABASE_URL is not set}"
: "${S3_BUCKET:?S3_BUCKET is not set}"
prefix="${S3_PREFIX:-postgres}"
stamp="$(date -u +%Y-%m-%dT%H-%M-%SZ)"

workdir="$(mktemp -d)"
trap 'rm -rf "$workdir"' EXIT
umask 077
dump="$workdir/$stamp.dump"

# 1. Dump. Custom format is compressed, and pg_restore can restore
#    single tables from it or run in parallel.
pg_dump --format=custom --file="$dump" --dbname="$DATABASE_URL"

# 2. Read the whole archive back before it leaves the machine: --list checks
#    the table of contents, --file=/dev/null decompresses every row. A truncated
#    or corrupt file fails here instead of on the day you need it.
pg_restore --list "$dump" > "$workdir/contents.txt"
pg_restore --file=/dev/null "$dump"
tables="$(grep -c ' TABLE DATA ' "$workdir/contents.txt" || true)"
echo "dump OK: $tables tables with data, $(wc -c < "$dump" | tr -d ' ') bytes"

# 3. Optionally encrypt. Only the holder of the matching private key can
#    read it, so a leaked bucket key does not leak the database.
upload="$dump"
if [ -n "${AGE_RECIPIENT:-}" ]; then
  age --recipient "$AGE_RECIPIENT" --output "$dump.age" "$dump"
  upload="$dump.age"
fi
name="$(basename "$upload")"

# 4. Upload the file, then a checksum next to it.
if command -v sha256sum > /dev/null; then
  sum="$(sha256sum "$upload" | cut -d ' ' -f 1)"
else
  sum="$(shasum -a 256 "$upload" | cut -d ' ' -f 1)"
fi
aws s3 cp "$upload" "s3://$S3_BUCKET/$prefix/$name" --only-show-errors
printf '%s  %s\n' "$sum" "$name" \
  | aws s3 cp - "s3://$S3_BUCKET/$prefix/$name.sha256" --only-show-errors
echo "uploaded s3://$S3_BUCKET/$prefix/$name"

It needs pg_dump and pg_restore of the same major version as your server (check with pg_dump --version and select version()). A newer pg_dump can dump an older server, but its archive then needs a newer pg_restore, and restoring that into the older server version runs into errors covered in the pg_restore guide. Matching versions avoids all of it. It also needs the AWS CLI v2, which also talks to non-AWS buckets through AWS_ENDPOINT_URL. We ran every command on this page against PostgreSQL 17 and a MinIO bucket before publishing it.

Why the script does not pipe pg_dump into aws s3 cp

The one-liner you see everywhere, pg_dump … | aws s3 cp - s3://…, has a failure mode worth knowing. If pg_dump dies partway (a dropped connection, a statement timeout, a full disk on the server), the pipe simply ends. The AWS CLI sees the end of its input and finishes the upload. We tested it: the pipeline exits with an error, and a truncated file is in the bucket under a normal timestamped name, looking like any other backup until the day you try to restore it.

Writing to a temporary file first costs disk space equal to one compressed dump, and buys two things: nothing is uploaded unless pg_dump succeeded, and pg_restore reads the whole archive back before it leaves the machine. --list alone is not enough for that: it only reads the table of contents at the start of the file, and in our test it passed a dump with its end cut off. --file=/dev/null decompresses every row and failed on the same file. If you have no local disk to spare, stream to a key ending in .partial and rename it only after the pipeline succeeds.

A successful run prints two lines:

$ bash pg-dump-to-s3.sh
dump OK: 4 tables with data, 17730 bytes
uploaded s3://acme-db-backups/postgres/2026-09-29T14-06-30Z.dump

Credentials: keep the password out of the connection string

A password inside DATABASE_URLends up on pg_dump’s command line, where any user on the machine can read it in the process list while the dump runs. Put it in a password file instead and leave it out of the URL:

# a dedicated system user runs the backups
sudo useradd --system --create-home --home-dir /var/lib/pgbackup pgbackup

# /etc/pg-backup.pgpass holds one line: host:port:database:user:password
# read -rs keeps the password off the screen and out of shell history
# IFS= keeps leading/trailing spaces; sed escapes \ and : as .pgpass requires
printf 'Password for backup_user: '; IFS= read -rs PGPASS_VALUE; echo
sudo install -m 600 -o pgbackup /dev/null /etc/pg-backup.pgpass
printf 'db.internal:5432:app:backup_user:%s\n' \
  "$(printf '%s' "$PGPASS_VALUE" | sed 's/[\\:]/\\&/g')" \
  | sudo tee /etc/pg-backup.pgpass > /dev/null
unset PGPASS_VALUE

libpq ignores the file if its permissions are wider than 0600. Point to it with PGPASSFILE, as the environment file below does. The database user needs to be able to read every table you back up; on PostgreSQL 14 and later, granting it the pg_read_all_data role is the simplest way to give a dedicated backup user exactly that. One exception: that role does not bypass row-level security, and pg_dump stops with an error on a table with RLS enabled unless the user owns it or has BYPASSRLS (ALTER ROLE backup_user BYPASSRLS, which needs a superuser or, on managed hosts, the platform’s admin role).

For the bucket, give the backup machine a key that can write to the backup prefix and nothing else. A key that can also delete means whoever takes over that server can take your backups with it. A write-only key still lets them overwrite an existing backup with a file of the same name, so also turn on bucket versioning (or S3 Object Lock, where your provider offers it): an overwritten backup then stays recoverable as an older version. Cloudflare R2 has neither; its bucket lock rules do the job differently, by refusing overwrites and deletes under a prefix for a retention period. Deletion of old backups is handled by the lifecycle rule further down, which needs no key at all.

Schedule it: systemd timer, cron, or GitHub Actions

All three read the same settings. On a server, keep them in one file that only the backup user can read:

# /etc/pg-backup.env (chmod 600, owned by pgbackup)
# quote the URL: cron sources this with sh, where an unquoted & (as in
# ?sslmode=verify-full&sslrootcert=...) would end the line
DATABASE_URL="postgresql://backup_user@db.internal:5432/app"
PGPASSFILE=/etc/pg-backup.pgpass
S3_BUCKET=acme-db-backups
S3_PREFIX=postgres/app
AWS_ACCESS_KEY_ID=AKIA...
AWS_SECRET_ACCESS_KEY=...
AWS_DEFAULT_REGION=us-east-1
# R2, B2, Wasabi: uncomment and set the provider's S3 endpoint
# AWS_ENDPOINT_URL=https://ACCOUNT_ID.r2.cloudflarestorage.com

systemd timer (recommended on Linux servers)

# /etc/systemd/system/pg-backup.service
[Unit]
Description=pg_dump to S3
Wants=network-online.target
After=network-online.target

[Service]
Type=oneshot
User=pgbackup
EnvironmentFile=/etc/pg-backup.env
ExecStart=/usr/local/bin/pg-dump-to-s3.sh

# /etc/systemd/system/pg-backup.timer
[Unit]
Description=Daily pg_dump to S3

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

[Install]
WantedBy=timers.target

sudo install -m 0755 pg-dump-to-s3.sh /usr/local/bin/pg-dump-to-s3.sh
sudo systemctl daemon-reload
sudo systemctl enable --now pg-backup.timer
sudo systemctl start pg-backup.service   # one run now, to test
journalctl -u pg-backup.service -n 20

Persistent=true runs a backup that was missed while the machine was off as soon as it is back. RandomizedDelaySec keeps a fleet of servers from dumping at the same second. Output goes to the journal, and a failed run shows up in systemctl --failed.

cron

# /etc/cron.d/pg-backup: daily at 03:15 server time, as user pgbackup
PATH=/usr/local/bin:/usr/bin:/bin
15 3 * * * pgbackup set -a; . /etc/pg-backup.env; set +a; /usr/local/bin/pg-dump-to-s3.sh >> /var/log/pg-backup/backup.log 2>&1

Two cron details break backup scripts. Cron starts jobs with a minimal PATH that usually does not include /usr/local/bin, where the AWS CLI installs, so set it at the top of the file. And cron does not read your environment file, so the line loads it with set -a. Create /var/log/pg-backup owned by the backup user first.

GitHub Actions

Useful when there is no server to run it on, or the database is a managed service. Commit the script to scripts/pg-dump-to-s3.sh, add the values as repository secrets, and use this workflow:

# .github/workflows/pg-backup.yml
name: pg-backup

on:
  schedule:
    - cron: "15 3 * * *" # daily, 03:15 UTC; runs can start late under load
  workflow_dispatch: {}

permissions:
  contents: read

concurrency:
  group: pg-backup
  cancel-in-progress: false

jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      # Use the client that matches your server's major version (17 here).
      # Install it from the PostgreSQL apt repo and put it first on PATH;
      # age is only needed if you set AGE_RECIPIENT.
      - name: Install postgresql-client-17 (PGDG)
        run: |
          sudo install -d /usr/share/postgresql-common/pgdg
          curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc \
            | sudo gpg --dearmor -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
          echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg] \
          https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
            | sudo tee /etc/apt/sources.list.d/pgdg.list
          sudo apt-get update
          sudo apt-get install -y postgresql-client-17 age
          echo "/usr/lib/postgresql/17/bin" >> "$GITHUB_PATH"

      - name: Dump and upload
        run: bash scripts/pg-dump-to-s3.sh
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
          S3_BUCKET: ${{ secrets.S3_BUCKET }}
          S3_PREFIX: postgres/app
          AWS_ACCESS_KEY_ID: ${{ secrets.AWS_ACCESS_KEY_ID }}
          AWS_SECRET_ACCESS_KEY: ${{ secrets.AWS_SECRET_ACCESS_KEY }}
          AWS_DEFAULT_REGION: us-east-1
          AGE_RECIPIENT: ${{ secrets.AGE_RECIPIENT }}
          # R2, B2, Wasabi only. Delete this line for AWS S3.
          AWS_ENDPOINT_URL: ${{ secrets.AWS_ENDPOINT_URL }}

The runner image may come with an older PostgreSQL client, which is why the workflow installs a matching version and puts it first on PATH. On a runner the password can stay in DATABASE_URL: the machine is yours alone and discarded after the job. Scheduled runs can start late when GitHub is busy, and GitHub disables schedules in public repositories after 60 days without activity, so check the Actions tab now and then. The Supabase GitHub Actions guide covers the egress side of running backups from GitHub in more detail.

Encrypt the dump before upload

S3 server-side encryption protects the disks at Amazon. It does nothing against the more likely leak: someone with a read key for the bucket sees your database in plain text. Encrypting on the backup machine with a public key closes that gap, because the machine that writes backups cannot read them:

# once, on your own machine: create a key pair
age-keygen --output pg-backup-key.txt
age-keygen -y pg-backup-key.txt   # prints the public key, age1...

# on the backup server, add the public key to /etc/pg-backup.env
AGE_RECIPIENT=age1...

With AGE_RECIPIENT set, the script uploads …dump.age instead of the plain dump. Keep the private key somewhere that is not the backup server and not the bucket, such as a password manager. Losing it means losing every backup made with it, so test a restore with it now. The check script below takes it through AGE_IDENTITY.

Retention: delete old dumps with a lifecycle rule

Let the bucket delete old backups, not the script. On S3, a rule that expires everything under the prefix after 35 days:

{
  "Rules": [
    {
      "ID": "expire-old-dumps",
      "Status": "Enabled",
      "Filter": { "Prefix": "postgres/app/" },
      "Expiration": { "Days": 35 }
    }
  ]
}

Applying it replaces the bucket’s whole lifecycle configuration, not just this rule, so any expiry or archive rules already on the bucket would silently disappear. Look first:

# prints the current rules, or NoSuchLifecycleConfiguration if there are none
aws s3api get-bucket-lifecycle-configuration --bucket acme-db-backups

# no existing rules, or you merged them into lifecycle.json's Rules array:
aws s3api put-bucket-lifecycle-configuration \
  --bucket acme-db-backups --lifecycle-configuration file://lifecycle.json

The checksum files sit under the same prefix, so they expire with their dumps. If the bucket has versioning turned on, expiring an object only hides it behind a delete marker and the old version is still stored and billed: add a NoncurrentVersionExpiration rule as well, for example { "NoncurrentDays": 7 }. On R2, set the same rule under the bucket’s Object Lifecycle Rules. On B2, lifecycle rules first hide a file and then delete it. For more history at little cost, keep dailies for a month and copy the first dump of each month to a second prefix with a longer rule.

The same script on RDS, Neon, Railway, and Supabase

Only the connection string changes. What to use on each platform:

AWS RDS and Aurora PostgreSQL. Connect with TLS verified against the RDS certificate bundle: ?sslmode=verify-full&sslrootcert=/etc/ssl/rds-global-bundle.pem, with the bundle downloaded from truststore.pki.rds.amazonaws.com. RDS’s own “export snapshot to S3” feature is not a substitute: it writes Apache Parquet files for analytics tools, which no Postgres can restore. RDS snapshots themselves restore only into RDS. A pg_dump file in your own bucket is the copy you can restore anywhere, including outside AWS.

Neon. Use the direct connection string, the host without -pooler. Neon’s own pg_dump guide says pooled connections are not supported for dumps and restores and will cause errors.

Railway.Railway databases are private by default. To dump from outside Railway (a GitHub Actions runner, another server), turn on Public Access under the database’s Networking settings, which creates a TCP proxy and a DATABASE_PUBLIC_URL variable to use here. Traffic through that proxy counts as egress on your Railway bill. Running the script as a cron service inside the same Railway project avoids both.

Supabase.Use the Session pooler string on port 5432, and dump your own schemas, not the platform’s. Storage files are not in any database dump. The Supabase to S3 guide covers both.

Check that the backup restores

A backup job that has run green for a year proves that pg_dump exited 0 every night. It does not prove the files restore. This second script downloads the newest dump, verifies its checksum, restores it into a throwaway Postgres container of your server’s major version, and fails if any table in the archive did not come back. Run it weekly on any machine with Docker, using a separate key that can read and list the bucket, so the backup server’s write-only key stays write-only.

Download pg-restore-check.sh

#!/usr/bin/env bash
# pg-restore-check.sh: restore the newest dump from the bucket into a throwaway
# Postgres container and check that every table in the archive came back.
# From https://backupdrill.com/guides/pg-dump-to-s3 (MIT licensed).
#
# Needs Docker and the AWS CLI v2. Same variables as pg-dump-to-s3.sh, plus:
#   PG_MAJOR       major version of the pg_dump that made the dumps, which
#                  should be the same as your server's (default: 17)
#   AGE_IDENTITY   path to the age private key, if the dumps are encrypted
#   ROLES          space-separated roles your policies or grants name, e.g.
#                  "app_reader app_writer"; created (NOLOGIN) before restoring
set -euo pipefail

: "${S3_BUCKET:?S3_BUCKET is not set}"
prefix="${S3_PREFIX:-postgres}"
major="${PG_MAJOR:-17}"
container="pg-restore-check-$$"

workdir="$(mktemp -d)"
cleanup() {
  # --volumes: the postgres image keeps its data in an anonymous volume, which
  # would otherwise outlive the container with a plain-text copy of the database
  docker rm --force --volumes "$container" > /dev/null 2>&1 || true
  rm -rf "$workdir"
}
trap cleanup EXIT
umask 077

# 1. Newest dump in the prefix. The timestamped names sort by date.
latest="$(aws s3 ls "s3://$S3_BUCKET/$prefix/" \
  | awk '{print $4}' | grep -E '\.dump(\.age)?$' | sort | tail -n 1 || true)"
[ -n "$latest" ] || { echo "no dumps under s3://$S3_BUCKET/$prefix/"; exit 1; }
aws s3 cp "s3://$S3_BUCKET/$prefix/$latest" "$workdir/$latest" --only-show-errors
aws s3 cp "s3://$S3_BUCKET/$prefix/$latest.sha256" "$workdir/$latest.sha256" --only-show-errors

# 2. Checksum, so a damaged download is not mistaken for a bad backup.
if command -v sha256sum > /dev/null; then
  (cd "$workdir" && sha256sum --check --quiet "$latest.sha256")
else
  (cd "$workdir" && shasum -a 256 --check --quiet "$latest.sha256")
fi

dump="$workdir/latest.dump"
if [ "${latest%.age}" != "$latest" ]; then
  : "${AGE_IDENTITY:?the dump is encrypted: set AGE_IDENTITY}"
  age --decrypt --identity "$AGE_IDENTITY" --output "$dump" "$workdir/$latest"
else
  mv "$workdir/$latest" "$dump"
fi

# 3. A throwaway server of the same major version, with no network at all:
#    it holds a copy of your data, and docker exec is all the checks need.
#    It only listens on TCP once initialisation has finished, so wait for a
#    TCP connection, not pg_isready.
docker run --detach --name "$container" --network none \
  --env POSTGRES_PASSWORD=throwaway "postgres:$major" > /dev/null
for attempt in $(seq 1 120); do
  if docker exec "$container" psql -h 127.0.0.1 -U postgres -c 'select 1' > /dev/null 2>&1; then
    break
  fi
  if [ "$attempt" -eq 120 ]; then
    echo "FAIL: the throwaway Postgres did not start within 2 minutes:"
    docker logs --tail 20 "$container"
    exit 1
  fi
  sleep 1
done
docker cp "$dump" "$container:/tmp/latest.dump" > /dev/null

# --no-owner and --no-privileges drop ownership and grants, but a row-level
# security policy that names a role still needs that role to exist.
for role in ${ROLES:-}; do
  docker exec "$container" psql -h 127.0.0.1 -U postgres -q \
    -c "create role \"$role\" nologin" > /dev/null
done

# 4. Restore. --exit-on-error turns the first failure into a failed check
#    instead of a warning count at the end; --no-tablespaces because the
#    throwaway server has none of the source's custom tablespaces.
docker exec "$container" pg_restore -h 127.0.0.1 -U postgres --dbname=postgres \
  --no-owner --no-privileges --no-tablespaces --exit-on-error /tmp/latest.dump

# 5. Every table that has data in the archive must exist after the restore.
#    A TOC line ends in "TABLE DATA <schema> <table> <owner>"; the table name
#    is everything between schema and owner, so table names with spaces
#    survive. Schema or owner names with spaces are not handled.
docker exec "$container" pg_restore --list /tmp/latest.dump \
  | awk '/ TABLE DATA / {
      sub(/^.* TABLE DATA /, "")
      schema = $1; owner = $NF
      print schema "." substr($0, length(schema) + 2, length($0) - length(schema) - length(owner) - 2)
    }' | sort > "$workdir/expected.txt"
docker exec "$container" psql -h 127.0.0.1 -U postgres -At -c "
  select table_schema || '.' || table_name
  from information_schema.tables
  where table_type = 'BASE TABLE'
    and table_schema not in ('pg_catalog', 'information_schema')" \
  | sort > "$workdir/restored.txt"
missing="$(comm -23 "$workdir/expected.txt" "$workdir/restored.txt")"
if [ -n "$missing" ]; then
  echo "FAIL: tables in the dump but not in the restored database:"
  echo "$missing"
  exit 1
fi

# 6. Row counts, so an empty table stands out.
docker exec "$container" psql -h 127.0.0.1 -U postgres -At -F ' ' -c "
  select table_schema || '.' || table_name,
         (xpath('/row/n/text()', query_to_xml(format('select count(*) as n from %I.%I',
           table_schema, table_name), false, true, '')))[1]::text
  from information_schema.tables
  where table_type = 'BASE TABLE'
    and table_schema not in ('pg_catalog', 'information_schema')
  order by 1"
echo "PASS: $latest restored, $(wc -l < "$workdir/expected.txt" | tr -d ' ') tables present"

A passing run lists every table with its exact row count:

$ bash pg-restore-check.sh
billing.invoices 250
public.customers 300
public.empty_audit 0
public.orders 1000
PASS: 2026-09-29T14-06-30Z.dump restored, 4 tables present

We broke it on purpose before trusting it. A dump truncated in the bucket fails the checksum. A truncated dump with a matching checksum, which is what the piping failure above produces, fails in pg_restore with could not read from input file: end of file. A zero next to a table that should have rows is your cue to look closer. If your row-level security policies name roles, list them in ROLES so the throwaway server has them; otherwise the policies fail to restore. The pg_restore guide explains the flags the script uses and the errors you may meet.

What a year of daily dumps costs on S3, R2, and B2

Say the compressed dump is 2 GB and you keep 30 dailies plus 12 monthlies: 42 copies, about 84 GB stored at any time.

Storage is the small line. The one that grows is reading backups back out: a weekly restore check downloads a full dump each time, which is free on R2, free within B2’s allowance, and billed as internet egress on S3 once you pass the free 100 GB a month. Upload requests cost fractions of a cent at one dump a day.

FAQ