How to back up PostgreSQL, MySQL and MongoDB to S3 automatically

How the pipe works

aws s3 cp reads - as standard input. Past 8 MiB it switches to a multipart upload and sends 8 MiB parts while the dump is still running, so the server needs no free disk space for the backup. Install AWS CLI v2 for all users, so cron and the postgres user find it in /usr/local/bin:

curl -fsSL https://awscli.amazonaws.com/v2/install.sh | sudo bash -s -- --system

Run the backups as postgres for PostgreSQL, and as root with the option files from the mysqldump and mongodump guides for MySQL and MongoDB:

pg_dump --no-password mydb | gzip | aws s3 cp - s3://acme-db-backups/db-01/postgresql/mydb-2026-10-04.sql.gz --profile db-backup

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events mydb | gzip | aws s3 cp - s3://acme-db-backups/db-01/mysql/mydb-2026-10-04.sql.gz --profile db-backup

mongodump --config=/root/.mongodump.yaml --archive --gzip | aws s3 cp - s3://acme-db-backups/db-01/mongodb/mongodb-2026-10-04.archive.gz --profile db-backup

• --no-password makes pg_dump fail at once instead of waiting for a password nobody will type.
• mongodump --archive with no file name writes to standard output, and --gzip compresses it, so there is no gzip stage.

We ran the dump half on PostgreSQL 16.15: a 295 MB database with 2 million rows became a 103,473,624-byte gzipped stream (99 MB) in 21 seconds. Dump options and locking are in the pg_dump guide.

Give the server a key that can upload but not delete

Whoever gets root on the database server gets every key on it. A key that can delete lets them erase every backup, as attackers who encrypt your data try to. Give the server a key that can add and read backups, and leave deleting to a lifecycle rule it cannot change:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ListOwnPrefix",
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::acme-db-backups",
      "Condition": { "StringLike": { "s3:prefix": ["db-01/*"] } }
    },
    {
      "Sid": "UploadAndReadNoDelete",
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:GetObject", "s3:AbortMultipartUpload"],
      "Resource": "arn:aws:s3:::acme-db-backups/db-01/*"
    }
  ]
}

• s3:PutObject covers every step of a multipart upload. s3:AbortMultipartUpload lets the CLI clean up a failed one; it cannot touch finished objects.
• s3:GetObject lets the script check the stored size and lets you restore from the server. s3:ListBucket lets it list its own prefix.
• No s3:DeleteObject. s3:PutObject can still overwrite a key, so turn on versioning: an overwritten backup survives as an older version, and removing versions needs s3:DeleteObjectVersion, which this key lacks.

Create the IAM user, attach the policy and make its access key as in the S3 bucket guide. Wasabi uses AWS-style policies, and its guide shows one without delete rights. Cloudflare R2 and DigitalOcean Spaces have no key level that uploads without deleting; protect those with R2 bucket locks or a second copy, and see the B2 guide for Backblaze's key types. Then store the key for the user that runs the backups:

sudo -iu postgres aws configure --profile db-backup

Enter the key ID, the secret and the bucket's region (auto for R2; Backblaze says to leave it blank). More on this in protecting backups from ransomware.

Use R2, B2, Spaces or Wasabi

--endpoint-url sends the same command to another S3-compatible service, at the bucket's regional endpoint:

pg_dump --no-password mydb | gzip | aws s3 cp - s3://acme-db-backups/db-01/postgresql/mydb-2026-10-04.sql.gz --profile db-backup --endpoint-url https://s3.us-west-004.backblazeb2.com

To keep the script the same on every provider, add the endpoint to the profile in the backup user's ~/.aws/config instead; a flag on the command line still overrides it:

[profile db-backup]
endpoint_url = https://s3.us-west-004.backblazeb2.com

Current AWS CLI versions add a checksum to every upload by default, which some S3-compatible services reject. If uploads fail there, add request_checksum_calculation = WHEN_REQUIRED and response_checksum_validation = WHEN_REQUIRED to the profile, as in the Akamai guide.

A failed dump still uploads

If pg_dump dies halfway, gzip still closes its stream properly, the AWS CLI sees the end of its input, completes the upload and exits 0. A pipeline reports the exit status of its last command, so without help the run looks like a success. We killed pg_dump's connection while it was copying the 2-million-row table:

pg_dump: error: Dumping the contents of table "orders" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: FATAL:  terminating connection due to administrator command
pg_dump: detail: Command was: COPY public.orders (id, customer_id, total, note, created_at) TO stdout;

• Without set -o pipefail the pipeline exited 0. With it, it exited 1, so cron and your alerts see the failure.
• Either way the stream that came out of the pipe was a complete gzip file of 6 to 7 MB instead of 103 MB, and gzip -t passed it.
• Restoring that file with psql -1 -v ON_ERROR_STOP=1 also exited 0. It loaded 112,317 of 2,000,000 orders and none of the primary keys or indexes, which pg_dump writes after the data.

So pipefail reports the failure but cannot stop the upload: by the time the shell knows, the object exists. The script below writes a small .done object next to each dump only after the dump, upload and size check all succeed. A dump without one is from a failed run.

The backup script

This runs the pipe as postgres, checks what arrived and writes the marker:

#!/usr/bin/env bash
# Dump one PostgreSQL database, gzip it and stream it to S3 with no local copy.
set -euo pipefail

DB="mydb"
BUCKET="acme-db-backups"
PREFIX="db-01/postgresql"
export AWS_PROFILE="db-backup"

KEY="$PREFIX/$DB-$(date -u +%Y-%m-%dT%H%M%SZ).sql.gz"
TMP=$(mktemp -d)
trap 'rm -rf "$TMP"' EXIT

# Count the bytes as they stream past, to compare with what the bucket stores.
mkfifo "$TMP/tap"
wc -c < "$TMP/tap" > "$TMP/bytes" &
COUNTER=$!

pg_dump --no-password "$DB" \
  | gzip \
  | tee "$TMP/tap" \
  | aws s3 cp - "s3://$BUCKET/$KEY" --only-show-errors

wait "$COUNTER"
SENT=$(cat "$TMP/bytes")
STORED=$(aws s3api head-object --bucket "$BUCKET" --key "$KEY" \
  --query ContentLength --output text)

if [ "$SENT" != "$STORED" ]; then
  echo "size mismatch for $KEY: sent $SENT bytes, bucket has $STORED" >&2
  exit 1
fi

# Written last: a dump with no .done file next to it is from a failed run.
printf '%s\n' "$SENT" | aws s3 cp - "s3://$BUCKET/$KEY.done" --only-show-errors
echo "$(date -u +%FT%TZ) uploaded s3://$BUCKET/$KEY ($SENT bytes)"

• set -euo pipefail stops at the first failed command, unset variable or failed stage of a pipe. The UTC timestamp in the key means runs never overwrite each other.
• The named pipe (mkfifo) lets wc -c count the bytes on their way to the CLI without a copy on disk. head-object returns the stored ContentLength, and the two must match.

We ran the script with a local stand-in for the AWS CLI. A normal run stored 103,473,624 bytes and the marker. With pg_dump killed as above, it exited 1 and left a 5.4 MB .sql.gz with no .done beside it. Make it executable and give it a log file:

sudo chmod 755 /usr/local/bin/pg-s3-backup

sudo install -o postgres -g postgres -m 640 /dev/null /var/log/pg-s3-backup.log

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

30 2 * * * postgres flock -n /run/lock/pg-s3-backup.lock /usr/local/bin/pg-s3-backup >> /var/log/pg-s3-backup.log 2>&1

The PATH line lets cron find /usr/local/bin/aws, and flock -n skips a run while the last one is still uploading. Wire the exit status to an alert as in backup failure alerts and the cron guide.

For MySQL or MariaDB, run the script as root and replace the pg_dump line with this one:

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf --single-transaction --routines --events --triggers "$DB" \

For MongoDB, run it as root, end the key in .archive.gz, and replace both the pg_dump and gzip lines with this one, since mongodump compresses its own archive:

mongodump --config=/root/.mongodump.yaml --archive --gzip \

Dumps over 50 GB need --expected-size

With no size to go on, the CLI uses 8 MiB parts, and S3 allows 10,000 parts per upload, so a stream fails at about 84 GB. AWS says to pass --expected-size, in bytes, for streams over 50 GB. It only sets the part size, so overestimate. Size on disk is a safe ceiling for a gzipped dump (ours: 295 MB on disk, 99 MB dumped). Add this before the pipe:

EXPECTED=$(psql -XAtc "SELECT pg_database_size('$DB')")

Then add --expected-size "$EXPECTED" to the aws s3 cp line. One AWS re:Post user hit the part limit anyway by taking the size from du, which reports kilobytes; du -b gives bytes. Or raise the part size for every upload: aws configure set s3.multipart_chunksize 64MB --profile db-backup lifts the ceiling to about 670 GB, at the cost of more memory.

Retention: a lifecycle rule, not the script

The server's key cannot delete, so the bucket removes old dumps itself. Apply this once from an admin machine, with admin credentials:

{
  "Rules": [
    {
      "ID": "expire-db-01-dumps",
      "Filter": { "Prefix": "db-01/" },
      "Status": "Enabled",
      "Expiration": { "Days": 30 },
      "NoncurrentVersionExpiration": { "NoncurrentDays": 7 },
      "AbortIncompleteMultipartUpload": { "DaysAfterInitiation": 2 }
    }
  ]
}

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

• Expiration removes dumps and their .done markers 30 days after upload: about 30 nightly backups.
• NoncurrentVersionExpiration keeps an expired or overwritten dump as an old version for 7 more days, in a versioned bucket.
• AbortIncompleteMultipartUpload clears the parts of uploads that died mid-stream, after a reboot or a killed script. You pay for those parts until they go.

Encrypt the dump before it leaves the server

Server-side encryption does not protect the dump from anyone holding a key to the bucket. To encrypt with a public key the server cannot decrypt with, add age to the pipe; the recipients file holds your age1… key:

pg_dump --no-password mydb | gzip | age -R /etc/backup/age-recipients.txt | aws s3 cp - s3://acme-db-backups/db-01/postgresql/mydb-2026-10-04.sql.gz.age --profile db-backup

The private key stays off the server, so restores run where it is: aws s3 cp s3://… - | age -d -i key.txt | gunzip | psql ….

Check a backup and restore it

List the finished dumps, newest last; each .done marker names one:

aws s3 ls s3://acme-db-backups/db-01/postgresql/ --profile db-backup | awk '/\.done$/ {print $4}' | sort | tail -n 3

head-object shows a dump's size and upload time without downloading it:

aws s3api head-object --bucket acme-db-backups --key db-01/postgresql/mydb-2026-10-04T023001Z.sql.gz --profile db-backup

To restore PostgreSQL, create an empty database and stream the dump into it, as the postgres user. Nothing touches the disk on the way:

createdb mydb_restored

aws s3 cp s3://acme-db-backups/db-01/postgresql/mydb-2026-10-04T023001Z.sql.gz - --profile db-backup | gunzip | psql -X -v ON_ERROR_STOP=1 -d mydb_restored

From a local copy of our dump, gunzip | psql took 25 seconds and brought back all 2,000,000 rows. Since a truncated dump also restores without an error, check the ending too. pg_dump 16.10 and later put a \unrestrict line after the completion comment, so read the last few lines. This prints 1 for a complete dump; our truncated one gave 0:

aws s3 cp s3://acme-db-backups/db-01/postgresql/mydb-2026-10-04T023001Z.sql.gz - --profile db-backup | gunzip | tail -n 5 | grep -c "PostgreSQL database dump complete"

A complete mysqldump file ends with -- Dump completed on; restore it with gunzip | mysql -u root -p mydb_restored. mongorestore reads an archive from standard input when --archive has no file name: aws s3 cp s3://….archive.gz - | mongorestore --archive --gzip. Compare row counts afterwards, as in testing a restore; the PostgreSQL restore guide explains psql's errors.

rclone instead of the AWS CLI

rclone streams the same way with rclone rcat remote:acme-db-backups/db-01/postgresql/mydb.sql.gz, which copies standard input to one object. Two limits from its docs: a streamed upload cannot be retried as a whole, and with the default 5 MiB S3 chunk size a stream tops out at 48 GiB unless you raise --s3-chunk-size.

Common errors

Frequently asked questions

Can I back up a database to S3 without saving the dump to disk first?

Yes. aws s3 cp - s3://bucket/key uploads standard input as a multipart upload while the dump runs, so pg_dump, mysqldump or mongodump --archive can pipe straight into it.

Does aws s3 cp work with Cloudflare R2, Backblaze B2, DigitalOcean Spaces and Wasabi?

Yes. Add --endpoint-url with the provider's regional endpoint, or set endpoint_url in the AWS CLI profile.

Why does my S3 backup script need set -o pipefail?

Without it, a pipeline reports only the last command's exit status. A failed pg_dump then looks like a successful upload. Even with it, the truncated object is already in the bucket, so mark good dumps separately.

How do I delete old database backups from S3 automatically?

Use a lifecycle rule with an Expiration in days on the backup prefix, set with admin credentials. The server's own key then never needs delete rights.

When do I need --expected-size?

For streams over 50 GB, per AWS's documentation. Give the size in bytes; an overestimate only makes the parts bigger.