A database backup strategy has to answer four questions: Is each backup consistent? Does it leave the server? How long is it kept? Has anyone tested a restore? This guide builds a simple, robust routine for MySQL/MariaDB, PostgreSQL and MongoDB on a single VPS.

The plan

  1. Dump each database consistently every night (or more often).
  2. Compress and keep a few days locally for quick restores.
  3. Copy off-site — encrypted, versioned, ideally immutable.
  4. Prune old backups automatically.
  5. Monitor that backups ran.
  6. Test a restore regularly.

This follows the 3-2-1 rule: three copies, two media, one off-site.

1. Consistent dumps

MySQL / MariaDB — --single-transaction gives a consistent InnoDB snapshot without locking:

mysqldump --single-transaction --routines --triggers --events "$db" | zstd -q > "$DEST/mysql-$db-$STAMP.sql.zst"

Credentials in /root/.my.cnf (mode 600). See mysqldump.

PostgreSQL — custom format for selective, parallel restores:

sudo -u postgres pg_dump -Fc "$db" > "$DEST/pg-$db-$STAMP.dump"
sudo -u postgres pg_dumpall --globals-only > "$DEST/pg-globals-$STAMP.sql"

See pg_dump.

MongoDB:

mongodump --uri="$MONGO_URI" --gzip --archive="$DEST/mongo-$STAMP.archive.gz"

See MongoDB backup.

For large databases or point-in-time recovery, use physical backups: pgBackRest, Percona XtraBackup or mariadb-backup.

2. A backup script

/usr/local/bin/db-backup.sh:

#!/bin/bash
set -euo pipefail
DEST=/var/backups/db
STAMP=$(date +%F_%H%M)
KEEP_DAYS=7
mkdir -p "$DEST"

# MySQL/MariaDB: all user databases
for db in $(mysql -N -e "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|mysql|sys)$'); do
  mysqldump --single-transaction --routines --triggers --events "$db" | zstd -q > "$DEST/mysql-$db-$STAMP.sql.zst"
done

# PostgreSQL: all databases
sudo -u postgres pg_dumpall --globals-only > "$DEST/pg-globals-$STAMP.sql"
for db in $(sudo -u postgres psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"); do
  sudo -u postgres pg_dump -Fc "$db" > "$DEST/pg-$db-$STAMP.dump"
done

# Local retention
find "$DEST" -type f -mtime +"$KEEP_DAYS" -delete

set -o pipefail ensures a failed dump fails the script rather than producing an empty “successful” file. Remove the sections for databases you don’t run.

sudo chmod 700 /usr/local/bin/db-backup.sh
sudo chmod 700 /var/backups/db

3. Off-site, encrypted copies with restic

restic encrypts, deduplicates and keeps versioned snapshots — repeated nightly dumps of a mostly unchanged database take little extra space.

sudo apt install -y restic
export RESTIC_REPOSITORY="s3:https://s3.example.com/backups/web1"
export RESTIC_PASSWORD_FILE=/root/.restic-pass
export AWS_ACCESS_KEY_ID=… AWS_SECRET_ACCESS_KEY=…
restic init

Append to the backup script:

restic backup /var/backups/db --tag db
restic forget --tag db --keep-daily 14 --keep-weekly 8 --keep-monthly 12 --prune

Targets: S3-compatible object storage, another VPS over SFTP, or a Storage VPS. Use credentials that can’t delete old snapshots if possible (object lock / append-only), so ransomware on the server can’t wipe your backups. See restic backups and BorgBackup.

Keep the restic password outside the server too (password manager) — without it the backups can’t be restored.

4. Schedule

echo '15 3 * * * root /usr/local/bin/db-backup.sh >> /var/log/db-backup.log 2>&1' | sudo tee /etc/cron.d/db-backup

Or a systemd timer for journald logging. See crontab guide.

5. Monitor

A silent failure is the most common backup disaster. Ping a heartbeat monitor at the end of a successful run:

curl -fsS -m 10 https://hc-ping.com/YOUR-UUID > /dev/null

Healthchecks.io or Uptime Kuma push monitors alert you when the ping doesn’t arrive. Also watch backup sizes — a sudden drop means something is wrong.

6. Test restores monthly

restic restore latest --tag db --target /tmp/restore-test
zstd -dc /tmp/restore-test/var/backups/db/mysql-shop-*.sql.zst | mysql shop_restore_test
mysql -e "SELECT COUNT(*) FROM shop_restore_test.orders"

Write down the restore steps — you’ll be stressed when you need them for real.

Checklist

  • Consistent dumps (single-transaction / pg_dump / –oplog)
  • pipefail and error logging
  • Local copies for 7 days
  • Encrypted off-site copies with versioning
  • Retention policy
  • Heartbeat monitoring
  • Monthly restore test, documented

Related: off-site VPS backups and backing up to cloud storage.

  • Backups
  • Databases
  • Automation