Database dumps to S3 on a schedule: Postgres, MySQL, MongoDB

A nightly database dump to S3 is one cron line that pipes the dump tool into an upload: pg_dump -Fc | aws s3 cp - s3://db-dumps/app/$(date +%F).dump. That line works, and it also contains the most common way these jobs fail silently - when the dump dies halfway, the upload still succeeds and you store a truncated file every night until the day you need one. This post builds the job properly for PostgreSQL, MySQL and MongoDB: where to run it, how to keep passwords out of the process list, how to make sure only complete dumps are kept, encryption, retention without lifecycle rules, and the restore test that tells you whether any of it works.

Where the job runs#

A dump tool connects to the database like any client, so the job can run on any machine that can reach the database's host and port and has the right client tools installed. The options, in rough order of preference:

• A small Linux VDS or server you already run, with cron and the client packages. The usual answer.
• The application server, if it is a full machine where you can install postgresql-client and set a cron job.
• Your own PC or a home server, which has the advantage of being in a different place from both the database and the bucket.

Managed database lines often give you no shell on the database machine itself, which is fine: dumping over the network is the normal way. On RE:NODE, the PostgreSQL, MySQL and MongoDB lines are reached on the plan's host and port with generated credentials, and they come with panel backup slots of their own. Use those for quick rollbacks; the S3 dump is the copy that survives a deleted server and gives you a portable file you can load anywhere.

The client version matters. pg_dump must be the same major version as the server or newer - an older pg_dump refuses to dump a newer server. mysqldump from MySQL 8.4 dumps an 8.4 server cleanly; a MariaDB mysqldump against MySQL 8.4 mostly works but is not the tool to trust for a backup. mongodump comes from the MongoDB Database Tools package, versioned separately from the server; any recent release supports current servers.

Credentials without leaking them#

Passwords on the command line are visible to every user on the machine through ps and end up in shell history. Each tool has a file-based alternative.

[client]host = db.example.netport = 3306user = backuppassword = s3cret-generated-value

--defaults-extra-file must be the first option on the command line or mysqldump ignores it. Make every one of these files readable only by the user the job runs as (chmod 600).

Give the dump its own database user where you can. A backup user needs read access, not write: in PostgreSQL that is the pg_read_all_data role (PostgreSQL 14 and later); in MySQL, SELECT, SHOW VIEW, TRIGGER, EVENT and LOCK TABLES on the database plus PROCESS globally to avoid a warning about tablespaces. Then a leaked backup credential cannot change anything. PostgreSQL roles and permissions has the details for Postgres.

For the bucket side, set up an AWS CLI profile once with the endpoint, path-style and the checksum compatibility settings, as described in S3 storage with the AWS CLI:

[profile store]region = us-east-1endpoint_url = https://s3.example.comrequest_checksum_calculation = when_requiredresponse_checksum_validation = when_requireds3 =  addressing_style = path

PostgreSQL#

Use the custom format (-Fc). It is compressed, and pg_restore can restore a single table or schema from it, list its contents, and restore in parallel. A plain SQL dump can only be replayed whole.

$ pg_dump -h db.example.net -p 5432 -U backup -d app -Fc --no-owner \    | aws s3 cp - "s3://db-dumps/pg/app/$(date -u +%F_%H%M).dump" --profile store

pg_dump takes a consistent snapshot inside one transaction, so the dump reflects a single moment even while the application keeps writing. It does not lock writers out. --no-owner leaves ownership statements out, which makes the file restorable into a database whose user has a different name - useful when the restore target is a new server with generated credentials. Roles and other cluster-wide objects are not in a pg_dump file at all; if you need them, pg_dumpall --globals-only captures them, though on a managed line you usually recreate the one application user by hand. pg_dump and pg_restore goes through every flag.

MySQL#

$ mysqldump --defaults-extra-file=/etc/backup/my.cnf \    --single-transaction --routines --triggers --events --hex-blob \    --set-gtid-purged=OFF app \    | zstd -q -T0 \    | aws s3 cp - "s3://db-dumps/mysql/app/$(date -u +%F_%H%M).sql.zst" --profile store

What each flag is for:

• --single-transaction dumps InnoDB tables from one consistent snapshot without locking them. It does nothing for MyISAM tables, which are not consistent without a lock - one more reason to have none.
• --routines --triggers --events include stored procedures, triggers and scheduled events. Triggers are on by default; the other two are not, and a dump without them restores a database that is quietly missing logic.
• --hex-blob writes binary columns as hex, which survives any character-set mishandling on the way back in.
• --set-gtid-purged=OFF keeps GTID statements out of the dump so it imports into a server that is not part of the same replication setup.

Note that mysqlpump, the parallel alternative some older guides recommend, was removed in MySQL 8.4. mysqldump remains, and for anything above a few gigabytes MySQL Shell's dump utilities are faster. mysqldump backup and restore covers restoring and large dumps.

MongoDB#

$ mongodump --config=/etc/backup/mongo.yaml --archive --gzip \    | aws s3 cp - "s3://db-dumps/mongo/app/$(date -u +%F_%H%M).archive.gz" --profile store

uri: mongodb://backup@db.example.net:27017/app?authSource=adminpassword: s3cret-generated-value

--archive with no file name writes a single archive stream to stdout, and --gzip compresses it. The restore reads the same stream: aws s3 cp s3://... - | mongorestore --archive --gzip --drop. On a standalone server mongodump is not a point-in-time snapshot - documents written during a long dump may or may not be included. --oplog fixes that but only works against a replica set. For most small applications a dump at the quietest hour is consistent enough; if yours is not, pause writes or dump from a replica. mongodump and mongorestore has the rest.

Valkey and SQL Server

Two other engines need a different approach. Valkey keeps its data in memory and saves it to disk itself, so the "dump" is a copy of its snapshot. valkey-cli (or redis-cli, which speaks the same protocol) can fetch one over the network: valkey-cli -h db.example.net -p 6379 --user default --pass "$PW" --rdb /tmp/dump.rdb asks the server for a fresh RDB file and writes it locally, ready to upload. Most people do not back up a cache at all - if it can be rebuilt from the real database, losing it costs a few slow minutes - but a Valkey that holds sessions or queues is worth a nightly copy. --pass on the command line shows up in ps; redis-cli reads the password from the REDISCLI_AUTH environment variable instead, and valkey-cli --help lists the variable your version honours.

SQL Server works the other way round: BACKUP DATABASE writes a .bak file on the server's own disk, and Express has no SQL Server Agent to schedule it. The usual pattern is a scheduled sqlcmd call from another machine to run the backup, then fetching the file. SQL Server backup and restore covers it, and the upload step at the end is the same aws s3 cp as everywhere else.

The pipe that uploads half a dump#

Here is the trap from the introduction in detail. In a pipeline A | B, the shell reports the exit status of B. If pg_dump loses its connection after 300 MB of a 1 GB dump, it exits with an error, the pipe closes, aws s3 cp - sees a normal end of input, uploads 300 MB and exits 0. The job "succeeded". Every night it may succeed like that.

set -o pipefail makes the script notice - the pipeline now fails if any stage failed - but the truncated object is already in the bucket, and nothing in its name says so. Two reliable patterns:

Upload to a temporary key, promote on success.

TMP="s3://db-dumps/_incoming/app-$$.dump"FINAL="s3://db-dumps/pg/app/$(date -u +%F_%H%M).dump"if pg_dump ... -Fc | aws s3 cp - "$TMP" --profile store; then  aws s3 mv "$TMP" "$FINAL" --profile storeelse  aws s3 rm "$TMP" --profile store  exit 1fi

With pipefail set, the if sees the failure of either stage. A failed run leaves nothing under the final prefix, and anything stuck in _incoming/ is a visible sign of a problem. The mv is a server-side copy followed by a delete, so it does not upload the data twice.

Dump to local disk first. Write to a file, check the exit code, verify the file (pg_restore --list dump.file > /dev/null reads the whole table of contents and fails on a truncated custom-format file), then upload. This needs free local disk the size of the compressed dump, and in exchange gives you the strongest check.

For streams over about 50 GB, the AWS CLI also needs --expected-size with an estimate of the size in bytes, so that it chooses part sizes large enough to stay under the 10,000-part multipart limit. Below that the defaults are fine.

Encryption#

A database dump is the most sensitive file you own: every user, every hash, every address. If the bucket's keys ever leak, the dumps leak with them. Encrypt before upload so that the storage only ever holds ciphertext.

age is the simplest tool for this. Generate a key pair once on a machine that is not the backup machine, keep the private key offline, and put only the public key in the job:

$ age-keygen -o backup-key.txt          # on your own machine; keep this file safe$ pg_dump ... -Fc | age -r age1qz...publickey... \    | aws s3 cp - "s3://db-dumps/pg/app/$(date -u +%F).dump.age" --profile store

The backup machine can encrypt but cannot decrypt, so an attacker who takes over the backup machine cannot read old dumps from the bucket. To restore: aws s3 cp s3://.../file.dump.age - | age -d -i backup-key.txt | pg_restore .... gpg --symmetric works too but needs a passphrase on the backup machine, which loses that property. Whatever you choose, store the decryption key somewhere you will still have it after the server is gone - a password manager, printed in a drawer - or the encrypted dumps are just noise.

Retention without lifecycle rules#

Name keys so they sort by date - pg/app/2026-10-08_0400.dump - and delete by name. Do not lean on --min-age style filters in sync tools; they use modification times that do not always mean "when this backup was taken".

A grandfather-father-son scheme keeps a week of dailies, a month of weeklies and a year of monthlies with three prefixes. On Sundays, copy that night's dump into weekly/; on the 1st, into monthly/. Server-side copies cost no upload:

#!/usr/bin/env bashset -euo pipefailP="--profile store"B="s3://db-dumps/pg/app"TODAY=$(date -u +%F)LATEST=$(aws s3 ls "$B/" $P | awk '{print $4}' | grep "^$TODAY" | sort | tail -n1)[ -n "$LATEST" ] || exit 1if [ "$(date -u +%u)" = 7 ]; then aws s3 cp "$B/$LATEST" "$B/weekly/$LATEST" $P; fiif [ "$(date -u +%d)" = 01 ]; then aws s3 cp "$B/$LATEST" "$B/monthly/$LATEST" $P; fiprune() {  # prune <prefix> <days>  local cutoff; cutoff=$(date -u -d "$2 days ago" +%F)  { aws s3 ls "$1/" $P || true; } | awk '{print $4}' | while read -r k; do    if [ -n "$k" ] && [[ "${k:0:10}" < "$cutoff" ]]; then aws s3 rm "$1/$k" $P; fi  done}prune "$B" 7prune "$B/weekly" 35prune "$B/monthly" 366

aws s3 ls on a prefix lists objects in its fourth column and folders as PRE lines, which the empty-check skips. It also exits with an error when a prefix has nothing in it yet - the first week, before any weekly copy exists - which is why that call is wrapped in || true; without it, pipefail would stop the script on its first run. Run the dump at 04:00 and this at 04:30 from cron, and add a dead-man's-switch ping at the end of each so you hear about the night it does not run. Cron expressions explained covers the schedule syntax.

Size the bucket from the policy. Seven dailies, five weeklies and twelve monthlies is 24 dumps; a 400 MB compressed dump makes that roughly 10 GB, and growth of the database grows it in proportion.

Testing the restore#

A backup nobody has restored is a hypothesis. Once a month, or after any change to the job:

1. Download the latest dump and decrypt it.
2. Restore into a scratch database - a local Docker container is ideal: docker run --rm -e POSTGRES_PASSWORD=x -p 5433:5432 postgres:17.
3. Run a query that only works on real data: row counts on the main tables, the newest order's timestamp.
4. Note how long it took. That is your real recovery time, and it is usually longer than people guess.

Database backups and restores and testing a restore before you need it turn this into a routine.

FAQ#

How often should I dump the database?

Daily covers most small applications. The interval is the most data you can lose, so a shop taking orders all day may want dumps every few hours plus the panel backups between them. Beyond that, continuous archiving (WAL for Postgres, binlogs for MySQL) is the next step, and a different job.

Should I compress dumps before uploading?

Yes, unless the format already is. PostgreSQL's custom format is compressed; mongodump --gzip is too. A plain mysqldump is text and shrinks five to ten times with zstd or gzip.

Can I dump while the application is running?

Yes. pg_dump and mysqldump --single-transaction read a consistent snapshot without blocking writes. Run them at a quiet hour anyway; a dump reads every row and competes with real queries for disk and CPU.

Is one copy in S3 enough?

It is a good second copy, not the only one. RE:NODE's storage, for example, keeps one copy on NVMe in one location without replication. Keep the panel backups too, and download a monthly dump to somewhere else entirely.

How do I know the job is still running?

Have it ping a monitoring service at the end of every successful run, and alert when the ping is missing. Also check the size of the newest dump now and then: a sudden drop to a few kilobytes means something is wrong even if every step "succeeded".