Database backups (pg_dump / mysqldump)

A database's files copied by a plain file-level backup are only a consistent picture if you're lucky — the DBMS could well be writing data at the exact moment of copying, and the result ends up corrupted. A logical dump via pg_dump/pg_dumpall (PostgreSQL) or mysqldump (MySQL/MariaDB) works differently — it takes a consistent snapshot using the DBMS's own mechanisms, and the output is a plain SQL file you can compress, move around, and restore on any server running the same or a newer version of the DBMS. This article covers taking such a dump for PostgreSQL and MySQL/MariaDB, compressing it, restoring it, putting it on autopilot with cron, and moving a copy off the server.

What you'll need

• An mHost VPS running Ubuntu 22.04/24.04, Debian 11/12, or AlmaLinux/Rocky Linux 8/9.
• SSH access as root or a user with sudo.
• PostgreSQL and/or MySQL/MariaDB already installed and running. If you don't have one set up yet, see PostgreSQL: install and configure or MySQL/MariaDB: install and configure — this article covers backups only.
• Free disk space for the dumps, ideally on a partition separate from the DBMS's own data directory. If space runs out partway through a dump, the file ends up truncated and useless for restoring — keep an eye on df -h (and see Disk full if you do run out).

Part 1. PostgreSQL: pg_dump and pg_dumpall

pg_dump dumps one database; roles, tablespaces, and other cluster-level settings aren't included. For a dump of the entire cluster, roles included, you need pg_dumpall.

Step 1. Dump a single database (pg_dump)

Create a directory for the dumps and take the first one, piping it straight through gzip:

mkdir -p /var/backups/postgresql
sudo -u postgres pg_dump mydb | gzip > /var/backups/postgresql/mydb_$(date +%F).sql.gz

sudo -u postgres runs pg_dump as the postgres system user — on a standard PostgreSQL install on any of the distros listed above, connecting as that user over the local socket uses peer authentication, so no password is needed. Without a -F/--format flag, pg_dump writes a plain SQL script to stdout, which goes straight into gzip and lands on disk already compressed.

Check that the file isn't empty and unpacks cleanly:

gzip -t /var/backups/postgresql/mydb_$(date +%F).sql.gz
ls -lh /var/backups/postgresql/

gzip -t checks the archive's integrity without unpacking it to disk.

If you have several databases, list them and dump each into its own file — this same list will come in handy for the automation script in Part 3, step 1:

pg_databases=$(sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datistemplate = false;")
for db in $pg_databases; do
  sudo -u postgres pg_dump "$db" | gzip > "/var/backups/postgresql/${db}_$(date +%F).sql.gz"
done

Step 2. Dump the entire cluster — roles and all databases (pg_dumpall)

pg_dumpall is useful when you need not one database but a full copy of the entire DBMS server — for example, before migrating to a new VPS or reinstalling the OS. Unlike pg_dump, it includes roles (users), the privileges granted to them, and tablespaces — things pg_dump deliberately leaves out.

Dump the whole cluster into a single file:

sudo -u postgres pg_dumpall | gzip > /var/backups/postgresql/cluster_$(date +%F).sql.gz

Or just the roles and tablespaces, without the databases' data (handy alongside the per-database dumps from step 1, if you want to restore databases individually but not lose track of user privileges):

sudo -u postgres pg_dumpall --globals-only | gzip > /var/backups/postgresql/globals_$(date +%F).sql.gz

Step 3. Restore PostgreSQL

Create an empty database with the right owner and restore the dump into it:

sudo -u postgres createdb -O myuser mydb_restored
gunzip -c /var/backups/postgresql/mydb_2026-07-10.sql.gz | sudo -u postgres psql mydb_restored

Without the -C/--create flag, pg_dump doesn't put a CREATE DATABASE statement into the dump — it expects you to already be connected to an existing (even if empty) database, so createdb needs to run as its own first step.

A whole-cluster dump (pg_dumpall) restores through psql directly, without createdb — the script already contains every CREATE ROLE/CREATE DATABASE it needs:

gunzip -c /var/backups/postgresql/cluster_2026-07-10.sql.gz | sudo -u postgres psql postgres

If you took the dump in -F c format, restore it with pg_restore, not psql:

sudo -u postgres createdb -O myuser mydb_restored
sudo -u postgres pg_restore -d mydb_restored mydb.dump

Part 2. MySQL and MariaDB: mysqldump

mysqldump works the same way in MySQL and MariaDB, including current MariaDB versions (10.11+/11.x), where the main command became mariadb-dump — mysqldump is kept there as a working alias for compatibility, and every command and flag below works unchanged.

Unlike PostgreSQL, where connecting locally as postgres uses passwordless peer authentication, for MySQL/MariaDB it's more reliable to set up a dedicated user just for backups from the start — that way the automation doesn't depend on exactly how root's login happens to be configured on a given server.

Step 1. A dedicated account for backups

Connect as the DBMS administrator — for example, sudo mysql (if root logs in through the socket without a password) or mysql -u root -p — and create a read-only user:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'CHANGE_ME_STRONG_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS, LOCK TABLES ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

SELECT covers the data itself — and per MySQL's own documentation, it's also enough for mysqldump to read the bodies of stored procedures and functions for the --routines flag, even if backup isn't the account that created them. SHOW VIEW is needed for views, TRIGGER for triggers, EVENT for scheduler events. PROCESS is required as long as --no-tablespaces isn't set (it isn't, in the commands below), and LOCK TABLES is required if --single-transaction isn't used; the examples below do use it, so LOCK TABLES isn't strictly necessary — but it's there in case you ever need to dump non-InnoDB tables without that flag.

Save the credentials to a dedicated client option file — this lets you run mysqldump without a password on the command line (where it would show up in ps output and shell history) and without an interactive password prompt from cron:

mkdir -p /etc/mysql
cat > /etc/mysql/backup.cnf <<'EOF'
[client]
user=backup
password=CHANGE_ME_STRONG_PASSWORD
EOF
chmod 600 /etc/mysql/backup.cnf

Check the connection:

mysql --defaults-file=/etc/mysql/backup.cnf -e "SELECT 1;"

Step 2. Dump a database (mysqldump)

A single database, compressed on the fly:

mkdir -p /var/backups/mysql
mysqldump --defaults-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --triggers --events \
  mydb | gzip > /var/backups/mysql/mydb_$(date +%F).sql.gz

• --single-transaction takes a consistent snapshot of InnoDB tables in a single transaction, without blocking writes from other clients. For non-InnoDB tables (the legacy MyISAM), this flag doesn't guarantee consistency for the duration of the dump.
• --routines, --triggers, --events include stored procedures and functions, triggers, and scheduler events in the dump; without these flags mysqldump skips them.

All databases at once, each into its own file (this same list will come in handy for the automation script in Part 3, step 1):

mysql_databases=$(mysql --defaults-file=/etc/mysql/backup.cnf -N -e "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|mysql|sys)$")
for db in $mysql_databases; do
  mysqldump --defaults-file=/etc/mysql/backup.cnf \
    --single-transaction --routines --triggers --events \
    "$db" | gzip > "/var/backups/mysql/${db}_$(date +%F).sql.gz"
done

grep -Ev filters out MySQL/MariaDB's own system databases — there's no need to back those up with a logical dump.

Step 3. Restore MySQL/MariaDB

A single-database dump (taken without --databases/--all-databases) doesn't contain a CREATE DATABASE statement — create the database yourself first and restore straight into it:

mysql --defaults-file=/etc/mysql/backup.cnf -e "CREATE DATABASE mydb_restored CHARACTER SET utf8mb4;"
gunzip -c /var/backups/mysql/mydb_2026-07-10.sql.gz | mysql --defaults-file=/etc/mysql/backup.cnf mydb_restored

Part 3. Automating and storing backups

Step 1. A nightly backup with cron

Combine the commands from the steps above into a single script with logging and protection against overlapping runs. Use whichever block (PostgreSQL, MySQL/MariaDB, or both) matches your server:

sudo nano /usr/local/bin/db-backup.sh

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

BACKUP_DIR_PG="/var/backups/postgresql"
BACKUP_DIR_MYSQL="/var/backups/mysql"
MYSQL_DEFAULTS="/etc/mysql/backup.cnf"
KEEP_DAYS=7
DATE=$(date +%F)
LOG_FILE="/var/log/db-backup.log"
LOCK_FILE="/var/lock/db-backup.lock"

exec 9>"$LOCK_FILE"
if ! flock -n 9; then
  echo "$(date '+%F %T') Previous run still in progress, exiting" >> "$LOG_FILE"
  exit 1
fi

echo "$(date '+%F %T') Backup started" >> "$LOG_FILE"

mkdir -p "$BACKUP_DIR_PG" "$BACKUP_DIR_MYSQL"

# PostgreSQL: one file per database, skip this block if PostgreSQL isn't installed
if command -v pg_dump >/dev/null; then
  pg_databases=$(sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datistemplate = false;")
  for db in $pg_databases; do
    sudo -u postgres pg_dump "$db" | gzip > "$BACKUP_DIR_PG/${db}_${DATE}.sql.gz"
  done
  sudo -u postgres pg_dumpall --globals-only | gzip > "$BACKUP_DIR_PG/globals_${DATE}.sql.gz"
  find "$BACKUP_DIR_PG" -name "*.sql.gz" -mtime "+${KEEP_DAYS}" -delete
fi

# MySQL/MariaDB: one file per database, skip this block if it isn't installed
if command -v mysqldump >/dev/null; then
  mysql_databases=$(mysql --defaults-file="$MYSQL_DEFAULTS" -N -e "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|mysql|sys)$")
  for db in $mysql_databases; do
    mysqldump --defaults-file="$MYSQL_DEFAULTS" --single-transaction --routines --triggers --events "$db" \
      | gzip > "$BACKUP_DIR_MYSQL/${db}_${DATE}.sql.gz"
  done
  find "$BACKUP_DIR_MYSQL" -name "*.sql.gz" -mtime "+${KEEP_DAYS}" -delete
fi

echo "$(date '+%F %T') Backup finished OK" >> "$LOG_FILE"

Make the script executable and run it by hand before you trust it to cron:

sudo chmod 700 /usr/local/bin/db-backup.sh
sudo /usr/local/bin/db-backup.sh
tail -n 20 /var/log/db-backup.log
ls -lh /var/backups/postgresql /var/backups/mysql

set -euo pipefail matters specifically for this script: without pipefail, a failure in pg_dump/mysqldump inside the ... | gzip pipe would go unnoticed, because a pipeline's exit status in bash defaults to that of the last command (gzip), and gzip will "succeed" even after receiving truncated input. find ... -mtime "+${KEEP_DAYS}" -delete clears out local dumps older than KEEP_DAYS days — otherwise the disk eventually fills up (see Disk full); longer-term retention is handled by the off-server copy from step 2.

Add a job to root's crontab — for example, every night at 02:00:

sudo crontab -e

0 2 * * * /usr/local/bin/db-backup.sh

The script logs to /var/log/db-backup.log on its own, so redirecting output in crontab too isn't necessary. For a detailed look at cron syntax, timezones, and common mistakes, see Scheduled backups with cron.

Step 2. Copy the backups off the server

The dumps in /var/backups/postgresql and /var/backups/mysql protect you from corrupted data and human error, but not from the server or its disk failing outright — if the backup sits on the same disk as the live database, losing the VPS loses both at once. Move these directories to another server the same way you would any other files:

• rsync — a simple mirror of the dump directory to another server over SSH, no versioning. See Backups with rsync.
• restic — versioned, encrypted, deduplicated copies that keep a longer snapshot history. See Incremental backups with restic.

Either way, the source for rsync/restic is the same /var/backups/{postgresql,mysql} directory — this article prepares the compressed files, and the tools themselves carry them off the server.

Step 3. Always test your restores

A dump that's never been restored isn't a backup — it's an assumption that the backup works. It breaks in ways you won't notice without checking: pg_dump/mysqldump could have died halfway through from running out of space, cron could have failed to fire because of a typo in the schedule, permissions on the dump file could quietly block reading it, and the off-server sync could stop silently on its first network error.

• Once every 1–2 months — or after any significant schema change — actually restore the latest dump into a separate database (step 3 of Part 1 or Part 2), ideally on another server rather than production.
• Check more than just the restore command's exit code: row counts on key tables, the presence of the most recent records by timestamp, and whether the application actually works against the restored copy.
• Check not just the local dump in /var/backups, but also its off-server copy from step 2 — that's the one that survives if the VPS itself is lost outright.
• Keep the db-backup.log log within reach and check it even when nothing looks wrong — a backup that's quietly been failing for days with no alerts is easy to miss.

What's next

For the details of the automation itself — cron syntax, timezones, log rotation — see Scheduled backups with cron. For moving dumps off the server, see Backups with rsync and Incremental backups with restic.