How to Back Up MariaDB and PostgreSQL on a VPS

A daily VPS backup protects you against losing the server, not against losing data. It copies the whole disk while the database is running, and it usually keeps only the most recent copy, so it cannot bring back a table someone emptied last Tuesday. The fix is a second, separate backup: a nightly script that exports each database to its own compressed file, keeps 14 days, checks every file, and takes about ten minutes to set up. The script, the schedule and the restore commands are all below.

This guide covers MariaDB, MySQL and PostgreSQL on Ubuntu or Debian, with a short section on MongoDB. It assumes you can use sudo. If you have not yet confirmed that your databases are closed to the internet, run the 15-minute VPS audit first.

What a whole-server backup can and cannot do#

There is a second reason. A disk copy taken while the database is writing is like pulling the power cord: the database normally recovers when it starts, but the copy was never checked. A dump is a consistent export that you can open, read and test.

Step 1: Create the backup script#

Save this as /usr/local/sbin/db-backup.sh. It dumps every MariaDB or MySQL database and every PostgreSQL database it finds, one file each, and skips whichever server is not installed.

#!/usr/bin/env bash
# Nightly database dumps: one compressed file per database, kept for KEEP_DAYS.
set -euo pipefail
umask 077

DIR=/var/backups/db
KEEP_DAYS=14
STAMP=$(date +%F)
mkdir -p "$DIR"

# MariaDB / MySQL (root logs in through the local socket, no password in this file)
if command -v mariadb-dump >/dev/null 2>&1 || command -v mysqldump >/dev/null 2>&1; then
  DUMP=$(command -v mariadb-dump || command -v mysqldump)
  CLIENT=$(command -v mariadb || command -v mysql)
  for db in $("$CLIENT" -N -e 'SHOW DATABASES' | grep -Ev '^(information_schema|performance_schema|sys)$'); do
    "$DUMP" --single-transaction --quick --routines --events --triggers "$db" | gzip > "$DIR/mysql-$db-$STAMP.sql.gz"
  done
fi

# PostgreSQL (roles and permissions first, then each database in custom format)
if command -v pg_dump >/dev/null 2>&1; then
  runuser -u postgres -- pg_dumpall --globals-only | gzip > "$DIR/pg-globals-$STAMP.sql.gz"
  for db in $(runuser -u postgres -- psql -At -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"); do
    runuser -u postgres -- pg_dump -Fc "$db" > "$DIR/pg-$db-$STAMP.dump"
  done
fi

# Every compressed file must be readable, or the script fails loudly
for f in "$DIR"/*-"$STAMP".sql.gz; do [ -e "$f" ] && gzip -t "$f"; done

# Retention
find "$DIR" -type f \( -name '*.sql.gz' -o -name '*.dump' \) -mtime +"$KEEP_DAYS" -delete

echo "db-backup OK: $(find "$DIR" -type f -name "*-$STAMP.*" | wc -l) files, $(du -sh "$DIR" | cut -f1) total"

Make it runnable by root only, and run it once by hand:

sudo chmod 700 /usr/local/sbin/db-backup.sh
sudo /usr/local/sbin/db-backup.sh
sudo ls -lh /var/backups/db

You should see one file per database and a final db-backup OK line. Three details in the script matter:

• --single-transaction gives a consistent snapshot of InnoDB tables without locking the site while the dump runs. WordPress, WooCommerce and Laravel use InnoDB by default.
• set -o pipefail makes the script stop if the dump fails. Without it, a failed dump piped into gzip leaves a tiny, valid-looking file and reports success.
• umask 077 makes the files readable by root only. Database dumps contain password hashes, customer details and API keys.

Step 2: Run it every night#

Create /etc/cron.d/db-backup:

30 2 * * * root /usr/local/sbin/db-backup.sh 2>&1 | logger -t db-backup

Pick a quiet hour that does not collide with other nightly jobs. The output goes to the system log, so you can see the last week of results with:

journalctl -t db-backup --since "7 days ago"

Step 3: Check the disk can hold 14 nights#

After the first run, multiply the folder size by the number of days you keep:

sudo du -sh /var/backups/db
df -h /var

Text compresses well, so a 2 GB database is often a 200 to 400 MB file. If 14 copies do not fit comfortably, lower KEEP_DAYS or move the older files off the server, which you should do anyway.

Step 4: Keep a copy somewhere else#

Dumps stored on the same server disappear with the server. Copy the folder to object storage or another machine after each run. With rclone configured for an S3-compatible bucket, one line at the end of the script is enough:

rclone copy /var/backups/db remote:my-bucket/db-backups --max-age 2d

Use credentials that can write to that bucket and nothing else, and turn on versioning or a retention lock on the bucket if the provider offers it. Then an attacker who gets into the server cannot quietly delete your history.

Step 5: Restore one database (do this once now)#

A backup you have never restored is a hope, not a backup. Restore last night’s file into a scratch database, look at it, and delete it.

MariaDB or MySQL:

sudo mariadb -e 'CREATE DATABASE restore_test'
gunzip < /var/backups/db/mysql-shop-2026-10-10.sql.gz | sudo mariadb restore_test
sudo mariadb -e 'SELECT COUNT(*) FROM restore_test.wp_posts'
sudo mariadb -e 'DROP DATABASE restore_test'

PostgreSQL:

sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test /var/backups/db/pg-n8n-2026-10-10.dump
sudo -u postgres psql -d restore_test -c '\dt'
sudo -u postgres dropdb restore_test

To restore for real, stop the application first, restore into the real database name (drop and recreate it, or use pg_restore --clean), then start the application again. Note how long the test took: that is roughly how long your site would be down.

MongoDB in one paragraph#

For MongoDB, add this to the script. It writes one compressed archive of every database and is restored with mongorestore --gzip --archive=FILE:

mongodump --gzip --archive="$DIR/mongo-$STAMP.archive.gz"

Add -o -name '*.archive.gz' to the find line so old archives are removed too. If authentication is enabled, pass a connection string from a root-only file with --config rather than typing the password on the command line.

Common mistakes to avoid#

• Putting the database password in the script. On Ubuntu and Debian, root connects to MariaDB through the local socket and the postgres user connects to PostgreSQL the same way. No password is needed.
• Dumping all databases into one file. Restoring a single site then means editing a multi-gigabyte text file. One file per database avoids that.
• Keeping dumps only on the server. They are lost in exactly the event you fear most.
• Never testing a restore. Empty or truncated files look fine in a folder listing.
• Forgetting uploaded files. The database holds posts and orders, not images. The whole-server backup or a separate file copy still has to cover wp-content/uploads and similar folders.

How this fits with a SiteHarbour VPS#

Every SiteHarbour VPS is backed up automatically each day, keeping a copy of the previous 24 hours, with snapshots and longer retention available on request. That covers the server. The nightly dumps in this guide cover your data, and our engineers can help you add them when your stack is installed. Use both: they answer different questions.

Frequently asked questions#

Does the dump slow down or lock my website?#

With --single-transaction on InnoDB tables, and with pg_dump on PostgreSQL, the site keeps reading and writing during the dump. You may see slightly higher disk and CPU use for a few minutes, which is why it runs at night.

How many days should I keep?#

Long enough to notice a problem. Fourteen days suits most small businesses, because data mistakes are often found a week or more after they happen. Keep monthly copies off the server if you need a longer history.

Can I use this instead of the provider’s daily backup?#

No. Dumps do not contain your application files, configuration, uploaded images or the operating system. Keep both.

What if I have MyISAM tables?#

--single-transaction does not make MyISAM tables consistent. Convert them to InnoDB if you can, which also makes the database safer after a crash. Check with SELECT table_schema, table_name FROM information_schema.tables WHERE engine = 'MyISAM'.

How do I know last night’s backup ran?#

Run journalctl -t db-backup --since yesterday and look for the db-backup OK line, or let a monitoring check alert you when that line does not arrive.

The short version: the daily server backup rescues the machine; nightly per-database dumps rescue the data. Write the files with a script that fails loudly, keep two weeks, copy them off the server, and restore one today so you know it works.