Automatic Postgres backups you can actually restore

A cron job that runs pg_dump every night and writes a file somewhere is not a backup system. It’s a backup system the day someone actually needs to restore from it, and not a day before. Plenty of teams find out the hard way: the cron entry stopped firing three months ago because of a PATH change nobody noticed, or the nightly dump has been writing a 40-byte error message to a .dump file instead of a database for weeks, or the backups are sitting right next to the database they’re supposed to protect and both disappear in the same server failure. A file on disk that nobody has ever restored is a guess, not a backup.

By the end of this tutorial you’ll have a real system: a scripted pg_dump in the right format, a cron schedule that actually runs it, the dump shipped to storage that isn’t on the same server, a retention policy that prunes old copies without leaving you with zero backups, and, most importantly, a scheduled job that restores your latest backup into a throwaway database and checks it’s actually good. That last part is the difference between a backup and a hope.

What you’ll need

• A Postgres server you control: a VPS, a dedicated box, or any self-hosted server running Postgres with pg_dump, pg_restore, psql, and createdb/dropdb available.
• An off-server destination for the dumps: S3-compatible object storage (AWS S3, DigitalOcean Spaces, Backblaze B2, Cloudflare R2, or similar) and either the AWS CLI or rclone configured with credentials for it. The whole point of this exercise is that the backup doesn’t live on the same disk as the database, so skipping this step defeats the system.
• Shell access to schedule cron jobs on the Postgres server (or wherever you run scheduled tasks).
• Enough disk headroom on the server for a dump to land locally before it ships out, and the same headroom again for a restore test database during the verify step.

A domain name isn’t part of this. You’re backing up data, not serving traffic.

Step 1: Script the dump

Use pg_dump’s custom format (-Fc, equivalent to --format=custom). It’s compressed by default, and unlike a plain SQL dump, it’s what pg_restore expects, which means you get selective restores and a format pg_restore can parallelize later if you ever need to.

Add --no-owner and --no-acl so the dump doesn’t carry ALTER OWNER and GRANT/REVOKE statements tied to roles that may not exist (or may have different names) wherever you eventually restore it, including your verification database in Step 5.

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

DB_NAME="myapp_production"
DB_USER="myapp"
BACKUP_DIR="/var/backups/postgres"
TIMESTAMP="$(date +%Y%m%d%H%M%S)"
DUMP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"

mkdir -p "${BACKUP_DIR}"

pg_dump -Fc --no-owner --no-acl \
  -U "${DB_USER}" -d "${DB_NAME}" \
  -f "${DUMP_FILE}"

if [ ! -s "${DUMP_FILE}" ]; then
  echo "pg_dump produced an empty file, aborting" >&2
  exit 1
fi

echo "Backed up ${DB_NAME} to ${DUMP_FILE}"

set -euo pipefail matters here: without it, a failed pg_dump can still let the script carry on and report success. The [ ! -s "${DUMP_FILE}" ] check catches the specific case where pg_dump exits cleanly but the file it wrote is empty, which happens more often than you’d expect when the destination disk is full.

You should see a new file in /var/backups/postgres with a size that roughly tracks your database’s actual size (a custom-format dump is compressed, so don’t expect a 1:1 match, but a 500 MB database producing a 2 KB file is a sign something’s wrong).

Step 2: Schedule it with cron

Crontab fields go in this order: minute, hour, day of month, month, day of week. A nightly run at 2:00 AM looks like this:

0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

Two details that cause most “my backups silently stopped” incidents:

• cron runs with a minimal environment, not your interactive shell’s. If your script calls pg_dump, aws, or rclone by name and relies on your shell’s PATH to find them, cron’s stripped-down PATH may not include the directory they live in. Use absolute paths inside the script (/usr/bin/pg_dump, /usr/local/bin/aws) or set PATH explicitly at the top of the script.
• Redirect both stdout and stderr to a log file (the >> ... 2>&1 above). Without it, cron emails failures to a mailbox nobody reads, or silently drops them depending on your system’s mail setup, and you only find out when a restore fails.

You should see a new line appended to /var/log/pg_backup.log every night, and a new .dump file in /var/backups/postgres with a fresh timestamp. If a day goes by with no new file and no new log line, cron never ran the job at all, which is a different failure than the job running and failing.

Step 3: Ship the dump off the server

A dump sitting on the same disk as the database it backs up doesn’t protect you from the failure that matters most: losing the server. Push it somewhere else immediately after it’s written.

With the AWS CLI, against S3 or any S3-compatible endpoint:

aws s3 cp "${DUMP_FILE}" "s3://my-backups-bucket/postgres/" --only-show-errors

For a non-AWS S3-compatible provider, add --endpoint-url:

aws s3 cp "${DUMP_FILE}" "s3://my-backups-bucket/postgres/" \
  --endpoint-url "https://nyc3.digitaloceanspaces.com" \
  --only-show-errors

Or with rclone, after setting up a remote once with rclone config:

rclone copy "${DUMP_FILE}" "myremote:my-backups-bucket/postgres/"

Append whichever command to the end of your backup script, and check its exit code the same way you checked pg_dump’s:

if ! aws s3 cp "${DUMP_FILE}" "s3://my-backups-bucket/postgres/" --only-show-errors; then
  echo "upload to S3 failed for ${DUMP_FILE}" >&2
  exit 1
fi

You should see the object show up in your bucket (aws s3 ls s3://my-backups-bucket/postgres/ or the equivalent in your provider’s dashboard) within moments of the cron job finishing, with a size matching the local file.

Step 4: Prune old backups

Without a retention policy, your backup directory and your bucket grow forever. With too aggressive a policy, you can prune your way down to zero backups right when you need one. The safer rule is two-part: keep at least a minimum number of recent backups no matter their age, and separately delete anything older than N days that isn’t among those kept.

Locally:

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

BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=30
KEEP_MIN=7

mapfile -t ALL_DUMPS < <(ls -1t "${BACKUP_DIR}"/*.dump 2>/dev/null)

for i in "${!ALL_DUMPS[@]}"; do
  if [ "$i" -lt "${KEEP_MIN}" ]; then
    continue
  fi
  file="${ALL_DUMPS[$i]}"
  if [ -n "$(find "${file}" -mtime "+${KEEP_DAYS}")" ]; then
    rm -f "${file}"
    echo "pruned ${file}"
  fi
done

ls -1t lists dumps newest first, so the loop skips the most recent KEEP_MIN files unconditionally, then only deletes older ones that also fail the age check. That means a quiet week where nothing new gets created never leaves you with fewer than KEEP_MIN backups.

For the remote copy, most S3-compatible providers support a bucket lifecycle rule that expires objects after N days, which is simpler than scripting remote pruning yourself; set it to a value at least as long as KEEP_DAYS so you’re never pruning the only off-server copy you have.

Run this as a second cron entry, separate from the backup script, so a pruning bug can’t accidentally run inside the same script that’s trying to create a fresh backup:

30 2 * * * /usr/local/bin/pg_prune.sh >> /var/log/pg_backup.log 2>&1

You should see old .dump files disappear from /var/backups/postgres over time while at least KEEP_MIN recent ones always remain, and a “pruned …” line in the log whenever one is removed.

Step 5: Restore and verify, on a schedule

This is the step almost everyone skips, and it’s the one that actually matters. A dump file existing and having a nonzero size tells you pg_dump ran. It tells you nothing about whether the data inside it is intact, whether the format is valid, or whether a restore would actually work when you need it to. The only way to know a backup is good is to restore it and check what came back.

Automate it against a throwaway database on the same server (or a separate one, if you’d rather not add restore load to production):

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

BACKUP_DIR="/var/backups/postgres"
LATEST_DUMP="$(ls -1t "${BACKUP_DIR}"/*.dump | head -n1)"
VERIFY_DB="restore_check_$(date +%Y%m%d)"
MIN_EXPECTED_ROWS=1

createdb "${VERIFY_DB}"

pg_restore --no-owner --no-acl -d "${VERIFY_DB}" "${LATEST_DUMP}"

ROW_COUNT="$(psql -d "${VERIFY_DB}" -tAc "SELECT count(*) FROM users;")"

dropdb "${VERIFY_DB}"

if [ "${ROW_COUNT}" -lt "${MIN_EXPECTED_ROWS}" ]; then
  echo "restore verification failed: ${ROW_COUNT} rows in users table" >&2
  exit 1
fi

echo "restore verified: ${LATEST_DUMP}, ${ROW_COUNT} rows in users table"

Swap users for whatever table you can realistically assert a minimum row count on; the point of the query isn’t that it’s exhaustive, it’s that it proves the restore produced a working database with real data in it, not just a database that exists. pg_restore itself will also fail loudly on a genuinely corrupted or truncated dump, so a non-zero exit code from the restore step alone is already a useful signal, separate from the row-count check.

Schedule it after your retention job, and less frequently than the backup itself is reasonable, weekly is a sensible starting point for most teams:

0 3 * * 0 /usr/local/bin/pg_restore_check.sh >> /var/log/pg_backup.log 2>&1

You should see a restore verified: ... line in the log every week, with a row count that looks right for your data. If that line stops appearing, or starts reporting a row count that’s suspiciously low, you’ve caught a bad backup while you still have older good ones to fall back on, instead of finding out during an actual outage.

The shortcut: do it with Railyard

Everything above is the honest, from-scratch version. Railyard runs the same loop for you on every Postgres database it manages, without the scripts.

When you provision a database on a Railyard-managed server, it schedules its own backups automatically on an interval you can adjust per database (a day is a sensible default for most apps). Each backup is verified by Railyard actually restoring it and checking it, not by checking that a file exists, so you can see at a glance whether your most recent backup is good instead of assuming it is. Restoring is one click from the database’s page, no script to remember and no throwaway database to clean up by hand afterward. Pruning is handled the same way you’d want it handled yourself: Railyard keeps a configurable number of your most recent successful backups and removes anything older than a configurable number of days that isn’t among those kept, so you’re never a single bad prune away from nothing.

For Postgres specifically, you can also turn on point-in-time recovery per server from the server’s page, which complements scheduled dumps: instead of only being able to restore to the moment of your last backup, you can recover to any moment the recovery window covers. More on all of this, including how databases are provisioned and connected, is in Databases and backups.

If you’re starting a new app rather than retrofitting backups onto an existing one, the quickstart and deploying from a repository cover getting the app and its database running on a server in the first place.

Troubleshooting

The cron job used to run and now it doesn’t, with no error anywhere. This is almost always the PATH problem from Step 2: a script that worked fine when you tested it interactively fails silently under cron’s minimal environment, because pg_dump, aws, or rclone aren’t where cron expects to find them. Use absolute paths for every binary the script calls, or set PATH explicitly at the top of the script, and confirm the job is actually firing by checking the log file’s timestamp, not just that no error showed up.

The dump file exists but is empty or truncated. The most common cause is the destination disk filling up mid-write, especially if the backup directory shares a disk with the database’s own data directory or a growing log file. pg_dump can exit with a nonzero status in this case, which is exactly why the script checks the exit code and the file size separately rather than trusting either alone.

Nobody ever tested a restore, and the backups have been silently broken for months. This is the scenario Step 5 exists to prevent. A corrupted or incomplete dump can sit in your bucket looking exactly like a healthy one for as long as nobody tries to use it. Scheduling the restore-and-verify job, not just documenting it as something to do manually someday, is what actually closes this gap.

Credentials for the off-server destination expired or got rotated. An S3 access key or rclone remote’s credentials can expire or get revoked independently of anything on the Postgres server, and the upload step will start failing while the local dump keeps succeeding. Checking the upload command’s exit code (Step 3) catches this immediately instead of weeks later when you go looking for an off-server copy that was never actually written.

FAQ

How often should backups run? It depends on how much data you can afford to lose, your recovery point objective. Daily is a reasonable default for most applications. If your data changes fast enough that losing up to a day of it would be a real problem, either run pg_dump more often or add WAL archiving for point-in-time recovery between dumps.

Is pg_dump enough, or do I need WAL archiving and point-in-time recovery? pg_dump gives you a clean, consistent snapshot at the exact moment it ran. Continuous WAL archiving (archive_mode, archive_command, and periodic base backups with pg_basebackup) lets you replay changes forward to any specific moment using recovery_target_time, so you can recover to just before a bad migration or an accidental delete instead of only to last night’s dump. For most teams, scheduled pg_dump backups cover the common case (server loss, corruption) and point-in-time recovery is worth adding once losing even a few hours of data between dumps would genuinely hurt.

How do I know a backup is actually good? Only a successful restore proves it. A file existing, having a reasonable size, and pg_dump exiting with status 0 are all necessary but not sufficient; the only real test is restoring it somewhere and checking that the data that comes back is what you expect, which is exactly what Step 5 automates.

Where should the off-server copy actually live? Anywhere that isn’t the same disk, ideally not even the same provider or region as the database server, so a single provider outage or account issue can’t take out both your database and its backups at once.

Do I need to encrypt the dump files? If your object storage destination supports server-side encryption at rest, turning it on costs nothing and is worth doing. If you want encryption independent of the storage provider, you can pipe the dump through gpg before it ships, though that adds a key you now also have to back up safely, which is its own kind of risk worth thinking through before adding it.

A backup you’ve never restored is a belief, not a fact. The five steps here turn that belief into something you actually verify on a schedule, so the first time you restore from a backup isn’t also the first time you find out whether it works.