Automating Database Backups

A backup you have to remember to run is a backup you will eventually forget. The whole point of this page is to make backups boring: a small script does the dump, a scheduler runs it every night, old copies get cleaned up automatically, and a fresh copy lands somewhere safe far away from your server. This page ties together everything from the rest of this section into one production-grade routine you set up once and trust forever. We will target Ubuntu 22.04/24.04 LTS and PostgreSQL as the main example, with notes for MySQL and MongoDB.

The 3-2-1 rule (start here)

Before writing a single line of script, understand the goal. The industry standard is the 3-2-1 rule:

• 3 copies of your data (the live database plus two backups).
• 2 different storage types (e.g. the server’s disk and cloud object storage).
• 1 copy off-site (a different physical location, so a fire, theft, or a deleted-by-accident server does not destroy everything).

A backup that lives only on the same server as the database is not a backup. If that machine dies, both copies die together. Off-site is not optional.

The other rule people forget: a backup is only real if you have restored from it. An untested backup is a hope, not a backup. We cover testing restores at the end.

Step 1: a per-database backup script

A backup script is just a shell script (a text file of commands) that creates a compressed dump and saves it with a date in the filename. “Compressed” means we shrink the file with gzip so it uses less disk and less bandwidth when shipped off-site.

Create a folder and a dedicated user-owned location for backups:

sudo mkdir -p /var/backups/postgres
sudo chown $USER:$USER /var/backups/postgres

Now the script. Save it as /usr/local/bin/pg-backup.sh:

#!/usr/bin/env bash
# Backup a PostgreSQL database, compress it, and rotate old copies.
set -euo pipefail

DB_NAME="appdb"
BACKUP_DIR="/var/backups/postgres"
RETENTION_DAYS=14
TIMESTAMP="$(date +%Y-%m-%d_%H%M%S)"
OUTFILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"

# Dump the database and compress it in one pipeline.
pg_dump --format=plain --no-owner "${DB_NAME}" | gzip -9 > "${OUTFILE}"

# Verify the file is non-empty, otherwise fail loudly.
if [ ! -s "${OUTFILE}" ]; then
  echo "ERROR: backup file is empty: ${OUTFILE}" >&2
  exit 1
fi

# Rotation: delete dumps older than RETENTION_DAYS.
find "${BACKUP_DIR}" -name "${DB_NAME}_*.sql.gz" -mtime "+${RETENTION_DAYS}" -delete

echo "Backup OK: ${OUTFILE} ($(du -h "${OUTFILE}" | cut -f1))"

A few words on the flags. set -euo pipefail makes the script stop immediately if any command fails (without it, a failed dump could silently produce a half-written file). gzip -9 is maximum compression. find ... -mtime +14 -delete is the rotation step: it removes any dump older than 14 days so the disk never fills up.

Make it executable and test it by hand:

sudo chmod +x /usr/local/bin/pg-backup.sh
sudo -u postgres /usr/local/bin/pg-backup.sh

Output:

Backup OK: /var/backups/postgres/appdb_2026-06-15_021500.sql.gz (4.2M)

We run it as the postgres system user so pg_dump can connect without a password (Ubuntu’s default “peer” authentication). For MySQL use mysqldump --single-transaction appdb | gzip -9 > ...; for MongoDB use mongodump --db appdb --archive --gzip > .... Everything else in the script stays the same.

Step 2: schedule it — cron vs systemd timer

You need something to run the script automatically. Two tools ship with Ubuntu: cron (the classic Unix scheduler) and systemd timers (the modern systemd-native scheduler).

Feature	cron	systemd timer
Setup effort	Lowest (one line)	Two small files
Logging	You redirect output yourself	Automatic, in journalctl
Missed runs (server was off)	Skipped	Can catch up with Persistent=true
Built-in failure alerts	No	Pairs with OnFailure= units
When to use	Simple single-server jobs	Anything you want observability for

Option A: cron (quick)

Edit the postgres user’s crontab:

sudo crontab -u postgres -e

Add this line to run every night at 2:15 AM and log to a file:

15 2 * * * /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

The five fields are minute, hour, day-of-month, month, day-of-week. 2>&1 means “send errors to the same log file as normal output”.

Option B: systemd timer (recommended for production)

Create the service unit at /etc/systemd/system/pg-backup.service:

[Unit]
Description=PostgreSQL nightly backup

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg-backup.sh

Create the timer at /etc/systemd/system/pg-backup.timer:

[Unit]
Description=Run pg-backup nightly

[Timer]
OnCalendar=*-*-* 02:15:00
Persistent=true

[Install]
WantedBy=timers.target

Persistent=true means if the server was switched off at 2:15, the backup runs as soon as it boots again. Enable and start it:

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

Output:

NEXT                        LEFT       LAST  PASSED  UNIT             ACTIVATES
Tue 2026-06-16 02:15:00 UTC 18h left   -     -       pg-backup.timer  pg-backup.service

View logs anytime with journalctl -u pg-backup.service.

Step 3: ship it off-server (the off-site copy)

A local dump satisfies “2 copies”, but not “1 off-site”. The two common ways to get the file off the box are rsync (copy to another server over SSH) and the cloud, usually Amazon S3 (object storage, a service that holds files in buckets).

Off-site with rsync to a backup server

rsync -avz --delete \
  /var/backups/postgres/ \
  [email protected]:/srv/db-backups/

-avz means archive mode, verbose, compressed transfer. Add this line to the end of your backup script so every dump is mirrored immediately.

Off-site with Amazon S3

Install the AWS CLI and configure credentials once (sudo apt install awscli, then aws configure). Then append to the script:

aws s3 cp "${OUTFILE}" "s3://my-company-db-backups/postgres/" --storage-class STANDARD_IA

STANDARD_IA (Infrequent Access) is a cheaper S3 tier that suits backups you rarely read. Turn on S3 lifecycle rules and versioning in the AWS console so the cloud handles its own long-term rotation and protects against accidental overwrites.

Encrypt before it leaves the server if the data is sensitive. Pipe through gpg --symmetric --cipher-algo AES256 (or use S3 server-side encryption). A plaintext dump sitting in a bucket is a breach waiting to happen.

Step 4: test the restore

Schedule a calendar reminder to do this monthly. Spin up a throwaway database and load the latest dump into it:

sudo -u postgres createdb restore_test
gunzip -c /var/backups/postgres/appdb_2026-06-15_021500.sql.gz \
  | sudo -u postgres psql restore_test
sudo -u postgres psql -d restore_test -c "SELECT count(*) FROM users;"

Output:

 count
-------
  4821
(1 row)

If the row counts look right, your backup is real. Drop the test database with sudo -u postgres dropdb restore_test when done.

Best Practices

• Follow 3-2-1: at least one copy on different media and one off-site, always.
• Never trust an untested backup — restore to a scratch database on a recurring schedule.
• Compress every dump (gzip -9) and encrypt before any sensitive data leaves the server.
• Use set -euo pipefail and an empty-file check so a broken backup fails loudly instead of silently.
• Prefer systemd timers when you want automatic logs, missed-run catch-up, and failure alerts.
• Automate rotation (find -mtime) locally and S3 lifecycle rules remotely so disks never fill.
• Send a success/failure notification (email or a webhook) so a silently broken job cannot hide for weeks.

Related Topics

• PostgreSQL backup and restore
• MySQL backup and restore
• MongoDB backup and restore
• Running a database in Docker
• Databases for DevOps