Database Backup and Restore

Chapter 22 concludes the database installation series in Section V by installing web UIs for PostgreSQL, MySQL/MariaDB, and MongoDB. Chapter 23 brings up a topic that is most often overlooked until an actual incident occurs: database backup and restore. An accidentally targeted DROP TABLE query, a sudden disk failure, or a ransomware attack encrypting all production data only takes seconds to happen. Meanwhile, the recovery process can take hours if the Sysadmin has never prepared a tested backup strategy beforehand. We will understand the fundamental differences between logical and physical backups, practice backup and restore operations for all four databases that have been running since Chapter 18 through Chapter 21 (PostgreSQL, MySQL, MariaDB, and MongoDB), write a single bash script that automates the entire dump, compression, and retention processes at once, and then close the chapter by scheduling the script via a systemd timer so that backups run on their own without needing manual daily reminders.

23.1 Backup Strategies: Logical vs Physical

Every database backup tool operates through one of two major approaches: logical backup or physical backup. A logical backup reads data via standard queries to the running database and writes the output as a structured representation such as SQL commands or BSON documents, exactly like how mariadb-dump works as practiced in Section 20.4. Conversely, a physical backup directly copies the raw files where the database stores its data on disk (the data directory), regardless of the logical structure inside.

Aspect	Logical Backup	Physical Backup
Mechanism	Reads data via queries, rewrites as SQL/documents	Copies raw data files in the data directory
Tool examples	`pg_dump`, `mysqldump`, `mariadb-dump`, `mongodump`	`pg_basebackup`, `mariadb-backup`, filesystem/LVM snapshots
Portability	High, dump files can be moved across different versions and platforms	Low, generally requires identical versions and architectures
Restore speed on large datasets	Relatively slow, as each row is rewritten via queries	Much faster, as it only copies files back into place
Granularity	Can be scoped per database or per specific table/collection	Generally covers the entire instance/cluster at once

This chapter focuses on logical backups using pg_dump, mysqldump, mariadb-dump, and mongodump, because their portability is best suited for daily Sysadmin needs: moving data between servers, shifting between development and production environments, or simply storing copies that are easy to inspect using a standard text editor. Physical backups still have their place, especially for databases sized hundreds of gigabytes or larger where restore time becomes critical during an incident, using official tools like PostgreSQL's native pg_basebackup, mariadb-backup for MariaDB, or simple LVM snapshots as practiced in Section 7.2.3 at the filesystem level. An in-depth discussion of these three physical backup tools is outside the scope of this chapter. However, the LVM snapshot principles from Section 7.2.3 are sufficient to provide an overview of how physical backups operate without stopping active database services.

A note from the field that applies to both approaches: a backup whose restore process has never been tested is not a reliable backup. Many data loss incidents are only revealed when an emergency restore is urgently needed, discovering too late that the dump file is corrupt, credentials have expired, or the backup script has been silently failing for months without anyone noticing. We will dive deeper into this principle through the 3-2-1 strategy in Chapter 40.

23.2 PostgreSQL Backup and Restore with `pg_dump` and `pg_restore`

pg_dump and pg_restore are automatically installed during the PostgreSQL installation in Section 18.1.2 as part of the client package, so no additional installation steps are required here. Both connect to the database in an identical manner to psql, using the webapp_user role created in Section 18.3.1, rather than the postgres superuser role which should only be held by Sysadmins for administrative purposes.

23.2.1 Backup with `pg_dump` and Dump Format Options

pg_dump supports several output formats simultaneously via the -F flag. The plain format (-F p) generates a standard SQL text file that is easy to read and edit manually, the custom format (-F c) produces a compressed binary file that supports selective restores via pg_restore, while the directory format (-F d) splits the dump into multiple files within a folder to enable parallel dumping using the -j flag. The custom format is the most common choice for routine backups due to its smaller size and flexibility during restores.

Practical Steps

Prepare the directory to store backup files.
```
sudo mkdir -p /var/backups/database
```
Run pg_dump using the custom format against the webapp_db database.
```
pg_dump -h 127.0.0.1 -U webapp_user -d webapp_db -F c -f /var/backups/database/webapp_db.dump
```
This command prompts for the webapp_user password interactively, adhering to the scram-sha-256 rule for TCP connections discussed in Section 18.2.1. How to configure non-interactive password usage for automation scripts is covered separately in Section 23.6.1.

Verification and Troubleshooting

Check the size of the generated dump file and ensure it is not zero bytes.
```
ls -lh /var/backups/database/webapp_db.dump
```
Executing file /var/backups/database/webapp_db.dump on a healthy file typically returns the label PostgreSQL custom database dump. This is a quick way to ensure the file is a valid PostgreSQL dump and not an empty or corrupted file resulting from an interrupted process.
The message pg_dump: error: connection to server ... failed usually means PostgreSQL is not running or the host address was mistyped, whereas FATAL: password authentication failed indicates an incorrect webapp_user password, identical to the connection troubleshooting in Section 18.3.1.

23.2.2 Restore with `pg_restore` and psql

The restore method varies depending on the dump format used during backup. Files in plain format are executed directly using psql as a series of standard SQL commands, whereas files in custom or directory format must use pg_restore because their contents are binary archives rather than pure SQL text.

Practical Steps

Create a new database as the restore target, simulating a recovery scenario to a server different from the backup source.
```
sudo -u postgres psql -c "CREATE DATABASE webapp_db_restore OWNER webapp_user;"
```
Restore the custom format dump file into the new database.
```
pg_restore -h 127.0.0.1 -U webapp_user -d webapp_db_restore --no-owner /var/backups/database/webapp_db.dump
```
The --no-owner flag ignores object ownership commands saved inside the dump, which is useful when roles on the target server do not match those on the source server.
To view the contents of the dump without actually executing it, use the --list option as a table of contents for the dump.
```
pg_restore --list /var/backups/database/webapp_db.dump
```

Verification and Troubleshooting

Confirm that the data is fully intact by comparing the contents of the notes table from Section 18.3.2 in the restored database.
```
psql -h 127.0.0.1 -U webapp_user -d webapp_db_restore -c "SELECT * FROM notes;"
```
The message pg_restore: error: could not execute query: ERROR: relation "notes" already exists appears if the target database already contains the table. Add the --clean --if-exists flags so pg_restore drops existing objects before recreating them. Note, however, that these flags are destructive to existing data in the target database.

Restoring from a plain format file uses psql, not pg_restore.

psql -h 127.0.0.1 -U webapp_user -d webapp_db_restore -f /var/backups/database/webapp_db.sql

23.3 MySQL Backup and Restore with `mysqldump`

mysqldump has been available since installing the mysql-server package in Section 19.2.1 as part of mysql-client, which was included as a dependency. The scenario here continues with the webapp_db database and webapp_user account created in Section 19.3.2.

23.3.1 Backup with mysqldump

Practical Steps

Run mysqldump with the --single-transaction option so that the InnoDB table dump is captured from a single consistent transaction snapshot without locking tables or pausing write access from active applications.
```
mysqldump -u webapp_user -p --single-transaction webapp_db > /var/backups/database/webapp_db.sql
```
The --single-transaction option automatically disables table locking during the dump process. However, consistency is only fully guaranteed for tables using the InnoDB storage engine. Legacy MyISAM tables still require brief locks during dumps, making the migration path to InnoDB (which has been the MySQL default since version 5.5) essential for Sysadmins inheriting legacy databases.
Compress the dump output directly through a pipe to gzip to save disk space, a pattern that will be reused in the automation script in Section 23.6.2.
```
mysqldump -u webapp_user -p --single-transaction webapp_db | gzip > /var/backups/database/webapp_db.sql.gz
```

Verification and Troubleshooting

A dump that completes successfully always ends with the comment line -- Dump completed on followed by a timestamp on the last line of the file. A quick way to verify that a dump was not cut short (e.g., due to a dropped network connection or full disk) is to check for the presence of this line.
```
tail -n 3 /var/backups/database/webapp_db.sql
```
The message mysqldump: Got error: 1045: Access denied indicates an incorrect password, identical to the MySQL login troubleshooting in Section 19.3.2.

23.3.2 Restoring from a Dump File

Practical Steps

Create the target restore database first, as mysqldump does not include a CREATE DATABASE command by default unless executed with the additional --databases flag.
```
mysql -u webapp_user -p -e "CREATE DATABASE webapp_db_restore;"
```

Restore the SQL file to the newly created database.

mysql -u webapp_user -p webapp_db_restore < /var/backups/database/webapp_db.sql

If the dump source is a compressed file, decompress it via a pipe without needing to save an intermediate raw file.
```
gunzip -c /var/backups/database/webapp_db.sql.gz | mysql -u webapp_user -p webapp_db_restore
```

Verification and Troubleshooting

Confirm that the table structure and content have been restored.
```
mysql -u webapp_user -p webapp_db_restore -e "SHOW TABLES;"
```
The message ERROR 1049 (42000): Unknown database means the database creation step was skipped or the target database name was mistyped.

23.4 MariaDB Backup and Restore with `mariadb-dump`

Section 20.4 covered mariadb-dump in detail, including the sandbox mode directive automatically inserted since MariaDB 11.0 as a mitigation for the MDEV-21778 security vulnerability. This section complements that practice with a backup and restore flow consistent with the PostgreSQL and MySQL patterns above, using the webapp_user account instead of root to align with the non-interactive script automation needs in Section 23.6.

23.4.1 Backup and Restore with mariadb-dump

Practical Steps

Back up the webapp_db database using the webapp_user account, which has held ALL PRIVILEGES since Section 20.3.1, sufficient to execute mariadb-dump without needing extra privileges.
```
mariadb-dump -u webapp_user -p --single-transaction webapp_db > /var/backups/database/webapp_db_mariadb.sql
```
Restore to a new database, assuming the mariadb client used supports the sandbox mode directive according to the version list in Section 20.4.1, so the first line of the dump file does not need to be truncated.
```
sudo mariadb -e "CREATE DATABASE webapp_db_restore;"
mariadb -u webapp_user -p webapp_db_restore < /var/backups/database/webapp_db_mariadb.sql
```

Verification and Troubleshooting

If restoring to an older MariaDB version or to MySQL, reapply the tail -n +2 workaround covered in Section 20.4.2 to skip the sandbox mode directive line.
Error messages and other verification steps are identical to Section 23.3.2, as MariaDB inherits its error message formats from the same MySQL codebase.

23.5 MongoDB Backup and Restore with `mongodump` and `mongorestore`

mongodump and mongorestore have been installed since the mongodb-org setup in Section 21.2.2 as part of the mongodb-org-tools package. The scenario continues with the webapp_catalog database and webapp_user account created in Section 21.4.2, complete with authentication enabled in Section 21.4.1.

23.5.1 Backup with `mongodump`

Practical Steps

Run mongodump with the --archive flag to consolidate all collections into a single stream file, accompanied by the --gzip flag for direct inline compression.
```
mongodump --uri="mongodb://webapp_user:[email protected]:27017/webapp_catalog?authSource=webapp_catalog" --archive=/var/backups/database/webapp_catalog.archive --gzip
```
The authSource=webapp_catalog parameter inside this connection string is equivalent to the --authenticationDatabase webapp_catalog flag used in Section 21.4.2, as webapp_user was created in the webapp_catalog database rather than admin.

Verification and Troubleshooting

Check the size of the output backup archive file.

ls -lh /var/backups/database/webapp_catalog.archive

The message Authentication failed usually indicates a mismatch in the username, password, or authSource value, identical to the MongoDB login troubleshooting in Section 21.4.2.

23.5.2 Restore with `mongorestore`

Practical Steps

Restore the archive to the database using mongorestore with the exact same --archive and --gzip flags used during backup.
```
mongorestore --uri="mongodb://webapp_user:[email protected]:27017/webapp_catalog?authSource=webapp_catalog" --archive=/var/backups/database/webapp_catalog.archive --gzip
```
Without additional flags, mongorestore only inserts new documents into existing collections. Documents matching an existing _id value will fail to insert and return duplicate key errors rather than overwriting existing data.

Verification and Troubleshooting

Confirm that the document count in the products collection from Section 21.3.2 matches its pre-backup state.

mongosh -u webapp_user -p --authenticationDatabase webapp_catalog webapp_catalog --eval "db.products.countDocuments()"

The --drop flag forces mongorestore to drop each target collection matching the archive content before writing new data. Use this for full restoration scenarios that intentionally overwrite old data. Handle this flag with extreme caution as it is destructive and irreversible; never run it against production databases without verifying that the source archive is valid and complete.

23.6 Bash Script for Multi-Database Backup Automation

Running four manual dump commands every day is impractical for production needs. This section constructs a single bash script that executes backups for all four databases at once, compresses them, and removes older backup files according to a retention policy (the maximum age a backup file is kept before automatic deletion to prevent disk space exhaustion). This pattern follows automated system administrative tasks detailed further in Chapter 35. Note one crucial limitation: MySQL and MariaDB cannot run concurrently on the same server, as warned in Section 20.1.1. Therefore, in practice, this script enables either the MySQL or MariaDB block according to which engine is actually active on the server, rather than running both simultaneously on a single host.

23.6.1 Securely Preparing Non-Interactive Credentials

Scripts running automatically via a systemd timer in Section 23.7 cannot enter passwords interactively, requiring credentials to be stored in files read directly by each tool. Each database engine uses its own convention, and storing plain text passwords inside scripts is a bad habit to avoid because script contents are easily readable by anyone with file read access.

Practical Steps

Create a dedicated system user to execute the backup process, following the principle of least privilege applied in Section 5.2.2, Section 15.6.2, and Section 22.3.1.
```
sudo useradd --system --no-create-home --shell /usr/sbin/nologin backupdb
```

Create a dedicated directory to hold credential files, restricted to access by the backupdb user only.

sudo mkdir -p /etc/backup-db
sudo chown backupdb:backupdb /etc/backup-db
sudo chmod 700 /etc/backup-db

Create a .pgpass file for PostgreSQL following the official hostname:port:database:username:password format documented by the PostgreSQL project.
```
sudo -u backupdb nano /etc/backup-db/pgpass
```
```
127.0.0.1:5432:webapp_db:webapp_user:S4ngatRahasiaPG!2026
```
This file must have permissions set to 600, as PostgreSQL will ignore its contents entirely if permissions are more permissive.
```
sudo chmod 600 /etc/backup-db/pgpass
```
Create an option file for MySQL or MariaDB (choose one depending on which engine is active on the server) using the [client] format supported directly by the mysql/mariadb clients via the --defaults-extra-file flag.
```
sudo -u backupdb nano /etc/backup-db/db-credentials.cnf
```
```
[client]
user=webapp_user
password=S4ngatRahasia!2026
```
```
sudo chmod 600 /etc/backup-db/db-credentials.cnf
```
An important note from the field: the --defaults-extra-file flag must be placed as the very first argument in the mysqldump/mariadb-dump command, before any other flags. Placing it elsewhere causes the client to ignore it without throwing clear error messages, a issue that often leaves new Sysadmins wondering why credential files are not being read.

Create an environment variable file for MongoDB, as mongodump/mongorestore do not provide an official option file mechanism like the three relational databases above.

sudo -u backupdb nano /etc/backup-db/mongo.env

MONGO_USER=webapp_user
MONGO_PASSWORD=PasswordWebappCatalog!2026
MONGO_AUTH_DB=webapp_catalog

sudo chmod 600 /etc/backup-db/mongo.env

Verification and Troubleshooting

Test the pgpass file directly without a script to ensure pg_dump no longer prompts for a password interactively.

sudo -u backupdb env PGPASSFILE=/etc/backup-db/pgpass pg_dump -h 127.0.0.1 -U webapp_user -d webapp_db -F c -f /tmp/test.dump

If PostgreSQL still displays a password prompt, re-check the pgpass file permissions with ls -la /etc/backup-db/pgpass; it must be set strictly to -rw-------.
Replace all placeholder passwords above with strong production passwords before implementing on actual servers, and never commit these four credential files into version control systems like Git.
An honest note on the risks of the environment variable approach for MongoDB: unlike pgpass and option files which avoid exposing passwords in command-line arguments, connection strings containing passwords during mongodump/mongorestore execution remain briefly visible via ps aux or /proc/<pid>/cmdline to other local users while the process runs, as Mongo shell tools lack official option file support. Restrict local shell access to trusted Sysadmins as a mitigation, and do not assume this risk profile is identical to the other three credential handling methods.

23.6.2 Writing the Backup Script: Dump, Compression, and Retention

Practical Steps

Create a new script file.

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

Populate it with the following script. The MySQL and MariaDB blocks are written side-by-side; enable only one by removing the hash symbol # from the relevant lines based on the database engine actually running on the server.

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/database"
RETENTION_DAYS=7
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
CRED_DIR="/etc/backup-db"

mkdir -p "$BACKUP_DIR"

echo "[$(date '+%F %T')] Starting database backup"

# PostgreSQL
PGPASSFILE="$CRED_DIR/pgpass" pg_dump -h 127.0.0.1 -U webapp_user -d webapp_db \
  -F c -f "$BACKUP_DIR/postgresql-webapp_db-$TIMESTAMP.dump"
echo "[$(date '+%F %T')] PostgreSQL backup completed"

# MySQL (enable this block if the server runs MySQL)
# mysqldump --defaults-extra-file="$CRED_DIR/db-credentials.cnf" --single-transaction webapp_db \
#   | gzip > "$BACKUP_DIR/mysql-webapp_db-$TIMESTAMP.sql.gz"

# MariaDB (enable this block if the server runs MariaDB)
# mariadb-dump --defaults-extra-file="$CRED_DIR/db-credentials.cnf" --single-transaction webapp_db \
#   | gzip > "$BACKUP_DIR/mariadb-webapp_db-$TIMESTAMP.sql.gz"

# MongoDB
mongodump --uri="mongodb://${MONGO_USER}:${MONGO_PASSWORD}@127.0.0.1:27017/${MONGO_AUTH_DB}?authSource=${MONGO_AUTH_DB}" \
  --archive="$BACKUP_DIR/mongodb-webapp_catalog-$TIMESTAMP.archive" --gzip
echo "[$(date '+%F %T')] MongoDB backup completed"

# Retention: remove backup files older than RETENTION_DAYS
find "$BACKUP_DIR" -type f -mtime +"$RETENTION_DAYS" -delete
echo "[$(date '+%F %T')] Retention applied, files older than $RETENTION_DAYS days deleted"

echo "[$(date '+%F %T')] Database backup process finished"

The set -euo pipefail directive at the top stops execution immediately if any command fails, including mid-pipe failures like mysqldump | gzip, preventing the script from continuing as if successful when a backup step silently fails. Shell script error handling is covered further in Chapter 35.

Make the script executable and transfer ownership of the script and the backup directory from Section 23.2.1 to the backupdb user, as the process will run under this user account rather than root.
```
sudo chmod +x /usr/local/bin/backup-database.sh
sudo chown backupdb:backupdb /usr/local/bin/backup-database.sh
sudo chown backupdb:backupdb /var/backups/database
```

Test run the script manually as the backupdb user, pre-loading MongoDB environment variables from mongo.env.

sudo -u backupdb bash -c 'set -a; source /etc/backup-db/mongo.env; set +a; /usr/local/bin/backup-database.sh'

Verification and Troubleshooting

Ensure all new dump files appear in the backup directory with today's timestamp.
```
ls -lh /var/backups/database
```
Check the script exit code explicitly to ensure no steps failed silently.
```
echo $?
```
A Permission denied error when the script attempts to read files in /etc/backup-db usually indicates credential file ownership or permissions do not match the backupdb user executing the script; review the steps in Section 23.6.1.
The message MONGO_USER: unbound variable appears if the script is executed directly without loading mongo.env first, because set -euo pipefail causes bash to terminate immediately upon encountering an undefined variable. This fail-fast behavior is intentional and safer than allowing mongodump to run with empty credentials.

23.7 Scheduling Automated Backups with Systemd Timers

The manually verified script from Section 23.6.2 can be scheduled using a systemd service and timer unit pair, following the exact same pattern from Section 5.3.1.

23.7.1 Creating Service and Timer Units

Practical Steps

Create a service unit with Type=oneshot, as this job runs once per schedule and exits, rather than running continuously.
```
sudo nano /etc/systemd/system/backup-database.service
```
```
[Unit]
Description=Automated Database Backup (PostgreSQL, MySQL/MariaDB, MongoDB)
After=network.target

[Service]
Type=oneshot
User=backupdb
Group=backupdb
EnvironmentFile=/etc/backup-db/mongo.env
ExecStart=/usr/local/bin/backup-database.sh
```
The EnvironmentFile directive reads KEY=VALUE pairs from /etc/backup-db/mongo.env and injects them as process environment variables, replacing the manual source step used during testing in Section 23.6.2.
Create a timer unit with the matching base name, enabling systemd to automatically associate both units without additional directives, scheduled every night at 01:00 AM.
```
sudo nano /etc/systemd/system/backup-database.timer
```
```
[Unit]
Description=Automated Database Backup Schedule

[Timer]
OnCalendar=*-*-* 01:00:00
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target
```
As explained in Section 5.3.1, Persistent=true ensures missable backup jobs trigger upon server boot if the system was powered off during the scheduled time, while RandomizedDelaySec prevents I/O spikes by spreading execution times when deployed across multiple servers simultaneously.
Reload systemd, then enable and start the timer unit, not the service unit directly.
```
sudo systemctl daemon-reload
sudo systemctl enable --now backup-database.timer
```

Verification and Troubleshooting

View the next scheduled execution along with all active timers on the server.
```
systemctl list-timers
```
To test the job without waiting for 01:00 AM, trigger the service manually.
```
sudo systemctl start backup-database.service
```
Check execution logs, including all echo output generated by the script in Section 23.6.2.
```
journalctl -u backup-database.service -n 50
```
If the service fails with a Permission denied status on the EnvironmentFile, ensure /etc/backup-db/mongo.env remains readable by the backupdb user configured via the User= directive, not just root.
Automated backups running via timers must still undergo periodic restore testing, rather than relying solely on the presence of dump files. Routine restore verification separates dependable backup strategies from those that merely appear secure, a topic discussed further via the 3-2-1 principle in Chapter 40, alongside copying backups off-site to cloud storage as outlined in Chapter 41.

At this point, all four databases running since Section V have clear backup and restore paths: pg_dump/pg_restore for PostgreSQL, mysqldump for MySQL, mariadb-dump for MariaDB, and mongodump/mongorestore for MongoDB, all automated via a single bash script running nightly via a systemd timer. Section V is officially concluded. Chapter 24 opens Section VI with cross-platform file sharing via Samba, starting with the underlying SMB/CIFS protocol concepts.

Database Backup and Restore

23.1 Backup Strategies: Logical vs Physical

23.2 PostgreSQL Backup and Restore with pg_dump and pg_restore

23.2.1 Backup with pg_dump and Dump Format Options

Practical Steps

Verification and Troubleshooting

23.2.2 Restore with pg_restore and psql

Practical Steps

Verification and Troubleshooting

23.3 MySQL Backup and Restore with mysqldump

23.3.1 Backup with mysqldump

Practical Steps

Verification and Troubleshooting

23.3.2 Restoring from a Dump File

Practical Steps

Verification and Troubleshooting

23.4 MariaDB Backup and Restore with mariadb-dump

23.4.1 Backup and Restore with mariadb-dump

Practical Steps

Verification and Troubleshooting

23.5 MongoDB Backup and Restore with mongodump and mongorestore

23.5.1 Backup with mongodump

Practical Steps

Verification and Troubleshooting

23.5.2 Restore with mongorestore

Practical Steps

Verification and Troubleshooting

23.6 Bash Script for Multi-Database Backup Automation

23.6.1 Securely Preparing Non-Interactive Credentials

Practical Steps

Verification and Troubleshooting

23.6.2 Writing the Backup Script: Dump, Compression, and Retention

Practical Steps

Verification and Troubleshooting

23.7 Scheduling Automated Backups with Systemd Timers

23.7.1 Creating Service and Timer Units

Practical Steps

Verification and Troubleshooting

Share

23.2 PostgreSQL Backup and Restore with `pg_dump` and `pg_restore`

23.2.1 Backup with `pg_dump` and Dump Format Options

23.2.2 Restore with `pg_restore` and psql

23.3 MySQL Backup and Restore with `mysqldump`

23.4 MariaDB Backup and Restore with `mariadb-dump`

23.5 MongoDB Backup and Restore with `mongodump` and `mongorestore`

23.5.1 Backup with `mongodump`

23.5.2 Restore with `mongorestore`